Slow performance in Oracle where clause

Question

I have the following Oracle table:

create table my_table( start int, end int ); insert into my_table values(1, 3); insert into my_table values(5, 7); insert into my_table values(11, 200); insert into my_table values(311, 5000); insert into my_table values(60004, 60024); insert into my_table values(123213, 12312312); 

This table has 1M rows and saves a range of numbers ("start", "end"), all numbers are unique, it does not have a duplicate range, and any digits can be in only one range in this table, I have the following query that passes the variable my_number to identify the "start" of the range.

  execute immediate 'select start from my_table where :1 between start and end' using my_number 

I built a combination index for two fields. the question is when my_number is low, the query performance is good, but when my_number is increasing, the query time is continuously increasing. if my_number is much larger, it takes a lot of time to complete. Does anyone have a way to improve this query? the method may include redesigning my_table. Thank you

Answer 1

If you change the circuit to this:

 create table my_table( start int, range_size int ); insert into my_table values(1, 2); insert into my_table values(5, 2); insert into my_table values(11, 189); insert into my_table values(311, 4689); insert into my_table values(60004, 20); insert into my_table values(123213, 12300001); 

Then you can index only the start column.

 execute immediate 'select start from (select start, range_size from my_table where start < :1 order by start asc limit 1) tmp where :1 < start+range_size' using my_number 

This may have some increase in performance.

Answer 2

Do you have it?

 create table my_table( start int, end int constraint PK_comp primary key (start, end) ) ; 

Answer 3

I think you should do 2 indexes, one at the start column and one at the end of the column. Then choose not with an option in between, but where is more than starting, then decreasing, and then finish. You will then use an index for each where clause.

I hope this helps in performance.

Answer 4

This is an example of trying to make Oracle act like competitors, and without access to Oracle, I just guess. Maybe self-study can do this? With indices for each column separately,

 SELECT t1.start FROM my_table t1 JOIN my_table t2 ON t1.start=t2.start AND t2."end"=t1."end" AND t1.start <= :1 AND t2.end >= :1 

This is a silly look, but a simple solution is Joe Frambach. It tricks Postgres, which I have, just to find the index.

By the way, Postgres is very unhappy with end as the column name. I hope your real table does not use the reserved word there.

Answer 5

Create an index for each column and use this query:

 select start_num from my_table where start_num = ( --Last start <= number select start_num from ( select start_num from my_table where :1 >= start_num order by start_num desc ) where rownum = 1 ) and end_num = ( --First end >= number select end_num from ( select end_num from my_table where :1 <= end_num order by end_num ) where rownum = 1 ); 

Ugh. Probably the best way to write this. Or you can wrap this in a function.

Problem

Test data (with names of non-reserved column names):

 drop table my_table; create table my_table( start_num int, end_num int ); insert into my_table select level*2,level*2+1 from dual connect by level <= 1000000; commit; create index my_table_index on my_table(start_num, end_num); begin dbms_stats.gather_table_stats(user, 'MY_TABLE', no_invalidate => false); end; / 

Low numbers almost instantly - 0.015 seconds

 select start_num from my_table where 2 between start_num and end_num; 

Larger numbers slower - 0.125 seconds

 select start_num from my_table where 1000000 between start_num and end_num; 

There is one point between a range scan and a full table scan.

 explain plan for select start_num from my_table where 402741 between start_num and end_num; select * from table(dbms_xplan.display); Plan hash value: 3804444429 ------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 160K| 1570K| 622 (2)| 00:00:08 | |* 1 | TABLE ACCESS FULL| MY_TABLE | 160K| 1570K| 622 (2)| 00:00:08 | ------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("START_NUM"<=402742 AND "END_NUM">=402742) explain plan for select start_num from my_table where 402742 between start_num and end_num; select * from table(dbms_xplan.display); Plan hash value: 3804444429 ------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 160K| 1570K| 622 (2)| 00:00:08 | |* 1 | TABLE ACCESS FULL| MY_TABLE | 160K| 1570K| 622 (2)| 00:00:08 | ------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("START_NUM"<=402742 AND "END_NUM">=402742) 

But the problem is not that Oracle is not using an index. Using the index in a naive way does not help. In fact, this is even slower, at 0.172 seconds:

 select /*+ index(my_table my_table_index) */ start_num from my_table where 1000000 between start_num and end_num; 

Decision

Create new indexes:

 drop index my_table_index; create index my_table_index1 on my_table(start_num); create index my_table_index2 on my_table(end_num); begin dbms_stats.gather_table_stats(user, 'MY_TABLE', no_invalidate => false); end; / 

The results again instantly, for any number:

 select start_num from my_table where start_num = ( --Last start <= number select start_num from ( select start_num from my_table where 1000000 >= start_num order by start_num desc ) where rownum = 1 ) and end_num = ( --First end >= number select end_num from ( select end_num from my_table where 1000000 <= end_num order by end_num ) where rownum = 1 ); 

The plan looks great - this is probably the best performance you can get.

 Plan hash value: 522166032 -------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 10 | 10 (0)| 00:00:01 | |* 1 | TABLE ACCESS BY INDEX ROWID | MY_TABLE | 1 | 10 | 4 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | MY_TABLE_INDEX2 | 1 | | 3 (0)| 00:00:01 | |* 3 | COUNT STOPKEY | | | | | | | 4 | VIEW | | 3 | 39 | 3 (0)| 00:00:01 | |* 5 | INDEX RANGE SCAN | MY_TABLE_INDEX2 | 3 | 18 | 3 (0)| 00:00:01 | |* 6 | COUNT STOPKEY | | | | | | | 7 | VIEW | | 2 | 26 | 3 (0)| 00:00:01 | |* 8 | INDEX RANGE SCAN DESCENDING| MY_TABLE_INDEX1 | 500K| 2929K| 3 (0)| 00:00:01 | -------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("START_NUM"= (SELECT "START_NUM" FROM (SELECT "START_NUM" "START_NUM" FROM "MY_TABLE" "MY_TABLE" WHERE "START_NUM"<=1000000 ORDER BY "START_NUM" DESC) "from$_subquery$_002" WHERE ROWNUM=1)) 2 - access("END_NUM"= (SELECT "END_NUM" FROM (SELECT "END_NUM" "END_NUM" FROM "MY_TABLE" "MY_TABLE" WHERE "END_NUM">=1000000 ORDER BY "END_NUM") "from$_subquery$_004" WHERE ROWNUM=1)) 3 - filter(ROWNUM=1) 5 - access("END_NUM">=1000000) 6 - filter(ROWNUM=1) 8 - access("START_NUM"<=1000000)