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

表中有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的改进吧。

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏杨建荣的学习笔记

生产环境sql语句调优实战第四篇(r2笔记41天)

生产中有一条sql语句消耗了大量的cpu资源,执行时间在18秒左右, Session:PRODBUSER (1560:61133)SQL ID:1hg2wcua...

2105
来自专栏杨建荣的学习笔记

通过执行计划中的CONCATENATION分析sql问题(r4笔记第16天)

昨天开发的一个同事找到我,说写了一条sql语句,但是执行了半个小时还没有执行完,想让我帮忙看看是怎么回事。 他大体上给我讲了下逻辑,表bl1_rc_rates是...

2994
来自专栏乐沙弥的世界

Oracle 监控索引的使用率

    Oracle提供了索引监控特性来判断索引是否被使用。在Oracle 10g中,收集统计信息会使得索引被监控,在Oracle 11g中该现象不复存在。尽管...

1253
来自专栏杨建荣的学习笔记

关于统计信息过期的性能落差(r5笔记第36天)

今天客户反馈某一个应用部署补丁的时候,执行了一个脚本一个多小时还没有执行完。 语句是下面这样的形式。 insert into em1_rater_00068_0...

4056
来自专栏黑泽君的专栏

day42_Oracle学习笔记_01

虚拟机上的orcl数据库,所在位置:C:\app\Training\oradata\orcl

722
来自专栏数据和云

SQL之美 - 分页查询的排序问题

编辑手记:前面我们分享过分页查询的基础知识,其目的就是控制输出结果集大小,将结果尽快的返回。主要有两种方式,一种是嵌套的查询方式,一种是通过范围控制分页的最大值...

3466
来自专栏数据库新发现

使用dbms_rectifier_diff解决高级复制中的数据冲突问题

« Oracle基于时间点的恢复 | Blog首页 | 关于Oracle的冲突解决机制的研究 »

563
来自专栏杨建荣的学习笔记

通过使用hint unnest调优sql语句(r4笔记第38天)

生产环境中有一条sql语句通过sql_monitor看到执行的时间实在是太惊人了,竟然达到了13个小时,而且还没有执行完。 SessionAPPC (20015...

3018
来自专栏乐沙弥的世界

当心外部连接中的ON子句

       在SQL tuning中,不良写法导致SQL执行效率比比皆是。最近的SQL tuning中一个外部连接写法不当导致过SQL执行时间超过15分钟左右...

1264
来自专栏数据和云

突破常识:SQL增加DISTINCT后查询效率反而提高

杨廷琨,网名 yangtingkun 云和恩墨技术总监,Oracle ACE Director,ACOUG 核心专家 只要增加了DISTINCT关键字,Orac...

3186

扫码关注云+社区