为每个列创建索引并使用以下查询:
select start_num
from my_table
where
start_num =
(
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 =
(
select end_num
from
(
select end_num
from my_table
where :1 <= end_num
order by end_num
)
where rownum = 1
);
呃,可能有更好的写法。或者你可能想将其包装在一个函数中。
问题
测试数据(使用非保留字列名):
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;
/
小数字几乎是瞬间完成的 - 0.015 秒
select start_num from my_table where 2 between start_num and end_num;
数字越大,速度越慢 - 0.125秒
select start_num from my_table where 1000000 between start_num and end_num;
在范围扫描和全表扫描之间只有一个关键点。
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)
但问题不在于Oracle不使用索引。以天真的方式使用索引并不能帮助解决问题。事实上,这样做甚至更慢,需要0.172秒:
select start_num
from my_table
where 1000000 between start_num and end_num;
解决方案
创建新的索引:
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;
/
现在,对于任何数字,结果都是即时的:
select start_num
from my_table
where
start_num =
(
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 =
(
select end_num
from
(
select end_num
from my_table
where 1000000 <= end_num
order by end_num
)
where rownum = 1
);
计划看起来很棒 - 这可能是您可以获得的最佳性能。
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)