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. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

closed as unclear what you're asking by Max Vernon, RolandoMySQLDBA, Paul White, Michael - sqlbot, dezso Nov 12 '13 at 22:14

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question.If this question can be reworded to fit the rules in the help center, please edit the 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

Browse other questions tagged or ask your own question.