Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 2 down vote favorite

We've a table with two columns beginrange and endrange, which contain hex values. To this table no overlapping ranges should be inputted. We had implemented the check by having a composite functional

index(to_number(beginrange ,'xxx..'),to_number(endrange,'xxx...')

and used the same in the where query

where not ( userStartrange > to_number(endrange,'xxx...') or userEndrange < to_number(startrange,'xxx...') )

The explain plan showed use of index only, but however on the live env which contains around 5 mil records, the explain plan showed full table access.

In an act of desperation, two redundant columns were added which contain decimal values of the hex columns. But still the query response time is still unsatisfactory.

Is there anyway to write a much efficient overlap check?

share|improve this question
You didn't have this to_number() function in your original question. Please explain what it does and also what are the datatypes of the columns. What is a "hex value"? – ypercube Apr 12 '12 at 14:10
@ypercube: a hex value would a hexadecimal representation of a decimal number; 0x10 would be 16, 0xFF would be 255. – Adam Musch Apr 12 '12 at 14:15
Oh, it's currency (or numbers) disguised as text. No wonder you have full scans. – ypercube Apr 12 '12 at 14:17
What's the avg_row_len of the table (look in ALL_TABLES) - I want to setup a test. Where do you want to do the check? In your app before you do an insert, or do you want the DB to throw an error (constraint/trigger)? – Phil Apr 12 '12 at 14:20
@Phil: Would a spatial index help? – ypercube Apr 12 '12 at 14:21
show 8 more comments

Know someone who can answer? Share a link to this question via email, Google+, Twitter, or Facebook.

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.