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
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?