如何使此查询更快?
问题描述:
我在Oracle数据库上运行该查询:如何使此查询更快?
SELECT COUNT(1) FROM db.table WHERE columnA = 'VALUE' AND ROWNUM < 2
上有columnA没有索引,表有很多很多千行(可能是数以百万计)的。大约有20个值应该被返回,所以这不是一个大的返回值。但是,由于它会触发全表扫描,因此需要耗费大量的时间。我怎样才能让它走得更快?
注意:我不是DBA,所以我对数据库的访问有限,无法实现重组,或者添加索引或摆脱旧数据。
答
如果你正在寻找一个排的存在,它的出现次数不是数字,那么这将是更合适:
SELECT 1
FROM DB.TABLE
WHERE ColumnA = 'VALUE'
AND ROWNUM = 1
这将尽可能快地停止查询一次行的被发现;然而,如果你需要它变得更快,那就是索引的目的。
测试用例:
create table q8806566
(id number not null,
column_a number not null,
padding char(256), -- so all the rows aren't really short
constraint pk_q8806566 primary key (id)
using index tablespace users
)
tablespace users;
insert into q8806566 -- 4 million rows
(id, column_a, padding)
with generator as
(select --+ materialize
rownum as rn from dba_objects
where rownum <= 2000)
select rownum as id, mod(rownum, 20) as column_a,
v1.rn as padding
from generator v1
cross join generator v2;
commit;
exec dbms_stats.gather_table_stats (ownname => user, tabname => 'q8806566');
的column_A数据分布均匀,并且可以在所有值的前几个街区被发现,所以这个查询运行良好:
SELECT 1
FROM q8806566
WHERE Column_A = 1
AND ROWNUM = 1;
子。 1秒执行时间和低I/O - 大约4个I/O。然而,寻找这是不存在的值时,事情改变得惊人:执行时间
SELECT 1
FROM q8806566
WHERE Column_A = 20
AND ROWNUM = 1;
20-40秒,超过10万的I/O。
但是,如果我们添加索引:
create index q8806566_idx01 on q8806566 (column_a) tablespace users;
exec dbms_stats.gather_index_stats (ownname => user, indname => 'q8806566_idx01');
我们从两个查询得到子0.1秒响应时间和个位数的I/O。
我们是否应该假设您无法在'columnA'上创建索引以提高查询效率?而且,就我所知,你只想优化后一个查询,而不是以前的查询,对吗? – 2012-01-10 16:14:25
正确,编辑清晰。 – Malfist 2012-01-10 16:29:54