SELECT * FROM (SELECT T1.*, rownum as linenum FROM (
SELECT /*+ index(a ind_old)*/
a.category FROM auction_auctions a WHERE a.category =' 170101 ' AND a.closed='0' AND ends > sysdate AND (a.approve_status>=0) ORDER BY a.ends) T1 WHERE rownum < 18681) WHERE linenum >= 18641
SQL> select segment_name,bytes,blocks from user_segments where segment_name ='AUCTION_AUCTIONS';
SEGMENT_NAME BYTES BLOCKS
AUCTION_AUCTIONS 1059061760 129280
表上原本的索引
create index ind_old on auction_auctions(closed,approve_status,category,ends) tablespace tbsindex compress 2;
SQL> select segment_name,bytes,blocks from user_segments where segment_name = 'IND_OLD';
SEGMENT_NAME BYTES BLOCKS
IND_OLD 20971520 2560
表和索引都已剖析过,咱们来看一下sql履行的费用
SQL> set autotrace trace;
SQL> SELECT * FROM (SELECT T1.*, rownum as linenum FROM (SELECT a.* FROM auction_auctions a WHERE a.category like '18%' AND a.closed='0' AND ends > sysdate AND (a.approve_status>=0) ORDER BY a.ends) T1 WHERE rownum <18681) WHERE linenum >= 18641;
SELECT * FROM (SELECT T1.*, rownum as linenum FROM (SELECT a.* FROM auction_auctions a WHERE a.category like '18%' AND a.closed='0' AND ends > sysdate AND (a.approve_status>=0) ORDER BY a.ends) T1 WHERE rownum <18681) WHERE linenum >= 18641;
可以看出这个sql语句有很大优化余地,起首最外面的了局集SELECT a.* FROM auction_auctions a WHERE a.category like '18%' AND a.closed='0' AND ends > sysdate AND (a.approve_status>=0) ORDER BY a.ends,这里的话会走index range scan,然后table scan by rowid,如许的话假如合适前提的数据多的话相当耗资本,咱们可以改写成
SELECT a.rowid FROM auction_auctions a WHERE a.category like '18%' AND a.closed='0' AND ends > sysdate AND (a.approve_status>=0) ORDER BY a.ends
如许的话最外面的了局集只需求index fast full scan就能够完成了,再改写一下得出以下语句
select * from auction_auctions where rowid in (SELECT rid FROM (
SELECT T1.rowid rid, rownum as linenum FROM
(SELECT a.rowid FROM auction_auctions a WHERE a.category like '18%' AND a.closed='0' AND ends > sysdate AND
(a.approve_status>=0) ORDER BY a.ends) T1 WHERE rownum < 18681) WHERE linenum >= 18641)
上面咱们来测试一下这个索引的查询开支
select * from auction_auctions where rowid in (SELECT rid FROM (
SELECT T1.rowid rid, rownum as linenum FROM
(SELECT a.rowid FROM auction_auctions a WHERE a.category like '18%' AND a.closed='0' AND ends > sysdate AND
(a.approve_status>=0) ORDER BY a.closed,a.ends) T1 WHERE rownum < 18681) WHERE linenum >= 18641)