Database Tuning (MOSC)

MOSC Banner

Improving performance of a query that uses BETWEEN

edited Nov 14, 2012 10:20AM in Database Tuning (MOSC) 10 commentsAnswered
I have a query that uses the BETWEEN operator and is performing VERY slow.  Basically, I have a TABLE_A that contains an integer, I'm trying to find the ONE record in TABLE_B in which that integer falls between a BEGIN and END integer value.  Think of it similar to finding a date that falls between a begin date and an end date (although these are integers, not dates).  The two tables are very large, containing over 100 million records each.

My existing slow query looks like this:

SELECT A.*
FROM
      TABLE_A A,  

     TABLE_B B
WHERE
     A.INT_NO BETWEEN B.INT_BEGIN AND B.INT_END;

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center