原创

一个用 Oracle 函数索引进行优化的例子

版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://wxy0327.blog.csdn.net/article/details/53927085

表中有500万条记录,原来没有索引:

set timing on
set autotrace traceonly

SQL> select count(*), to_char(time,'hh24') from userloginlog
2 where trunc(time) = trunc(sysdate) - 1
3 group by to_char(time,'hh24')
4 order by to_char(time,'hh24');

24 rows selected.

Elapsed: 00:00:06.70

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=6179 Card=37215 Bytes=297720)

   1    0   SORT (GROUP BY) (Cost=6179 Card=37215 Bytes=297720)
   2    1     TABLE ACCESS (FULL) OF 'USERLOGINLOG' (TABLE) (Cost=6039 Card=37257 Bytes=298056)



Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
      25154  consistent gets
      24470  physical reads
          0  redo size
        763  bytes sent via SQL*Net to client
        514  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         24  rows processed

查询使用全表扫描,执行需6.7秒。

下面建立函数索引:

create index idx_time on userloginlog (to_char(time,'hh24')) tablespace indexes; 

create index idx_time2 on userloginlog (trunc(time)) tablespace indexes;

执行同样的查询:

SQL> select count(*), to_char(time,'hh24') from userloginlog
2 where trunc(time) = trunc(sysdate) - 1
3 group by to_char(time,'hh24')
4 order by to_char(time,'hh24');

24 rows selected.

Elapsed: 00:00:00.34

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=323 Card=37215 Bytes=297720)

   1    0   SORT (GROUP BY) (Cost=323 Card=37215 Bytes=297720)
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'USERLOGINLOG' (TABLE) (Cost=183 Card=37257 Bytes=298056)

   3    2       INDEX (RANGE SCAN) OF 'IDX_TIME2' (INDEX) (Cost=64 Card=16143) 


Statistics
----------------------------------------------------------
        197  recursive calls
          0  db block gets
        341  consistent gets
          1  physical reads
          0  redo size
        763  bytes sent via SQL*Net to client
        514  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
         24  rows processed

查询使用索引扫描,执行需0.34秒,快了20倍。

还有一点,建立索引后并没有执行 analyze table userloginlog compute statistics; 进行分析,索引就生效了,这是10g的改进吧。 

文章最后发布于: 2016-12-29 13:57:16
展开阅读全文
0 个人打赏
私信求帮助

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 大白 设计师: CSDN官方博客

分享到微信朋友圈

×

扫一扫,手机浏览