我有以下Oracle表:
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);
这个表有1M行并存储数字范围('start', 'end'),所有数字都是唯一的,它没有重复的范围,任何数字只能在这个表的一个范围内,我有以下查询,它传递一个变量my_number来标识范围的'开始'。
execute immediate
'select start from my_table where :1 between start and end' using my_number
我已经建立了两个字段的组合索引。问题是,当my_number较小时,查询性能良好,但当my_number增加时,查询时间不断增加。如果my_number大得多,则需要花费相当长的时间来完成。有人有办法改进这个问题吗?方法包括重新设计my_table。谢谢。
如果您将模式更改为:
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);
则只能在start
列上建立索引。
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
这可能会有一些性能提升
你做了吗?
create table my_table(
start int,
end int
constraint PK_comp primary key (start, end)
) ;
我认为你应该做两个索引,一个在开始列,一个在结束列。然后不要选择between选项,而是选择大于开始,小于结束的位置。然后,您将为每个where子句使用索引。
这是一个试图愚弄Oracle的案例,使其表现得像其竞争对手一样,而没有访问Oracle的权限,我只是猜测。也许自连接可以做到这一点?在每个列上分别设置索引,
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
这看起来很傻,但Joe Frambach的解决方案很简单。它欺骗Postgres,我有,只做索引搜索。
顺便说一句,Postgres对end
作为列名很不满意。
为每一列创建一个索引,并使用如下查询:
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
);
恶心。可能有更好的写法。或者你可以把它封装在一个函数中。
测试数据(非保留字列名):
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 /*+ index(my_table my_table_index) */ start_num
from my_table
where 1000000 between start_num and end_num;
<<p> 解决方案/strong> 创建新索引:
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 =
(
--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
);
这个计划看起来很棒——这可能是你能得到的最好的性能。
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)