一个用 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 条评论
登录 后参与评论

相关文章

来自专栏乐沙弥的世界

INDEX FULL SCAN vs INDEX FAST FULL SCAN

     INDEX FULL SCAN 与 INDEX FAST FULL SCAN两个长相差不多,乃是一母同胞,因此既有其共性,也有其个性。两者来说其共性是...

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

关于查询转换的一些简单分析(二) (r3笔记第68天)

关于查询转换的作用,在之前的一篇博文里面也大概说了视图合并和子查询嵌套的部分。http://blog.itpub.net/23718752/viewspace-...

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

关于查询转换的一些简单分析(三) (r3笔记第69天)

关于查询转换,已经讨论了视图合并和子查询解嵌套,还有谓词推进和物化视图查询重写也是查询转换中不可或缺的部分。 -->谓词推进 这个术语听起来高大上,有点故弄玄虚...

26711
来自专栏数据和云

案例分析:倾斜值传入导致 SQL 资源消耗升高

作者 | 邓秋爽:云和恩墨技术工程师,有超过七年超大型数据库专业服务经验,擅长 Oracle 数据库优化、SQL 优化和 Troubleshooting。

1054
来自专栏乐沙弥的世界

当心外部连接中的ON子句

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

754
来自专栏乐沙弥的世界

Oracle RAC环境下配置statspack

    Statspack是Oracle 9i时代的产物,对于监控与分析数据库性能有着跨里程碑的意义,是AWR的前身。在Oracle 10g后AWR取代了sta...

573
来自专栏用户2442861的专栏

面试必会函数源代码 strcpy/memcpy/atoi/kmp/quicksort

        继上一次发表了一片关于参加秋招的学弟学妹们如何准备找工作的博客之后,反响很大,顾在此整理一下,以便大家复习。好多源自july的这篇博客,也有很...

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

一条简单的sql在11g和12c中的不同(r5笔记第2天)

今天在查看awr报告的时候,有一句很简单的sql语句引起了我的注意,因为它排在SQL Order by Reads的第2位。 Physical ReadsExe...

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

生产环境大型sql语句调优实战第一篇(一) (r2笔记第31天)

在生产环境中有一条sql语句的性能极差,在早晨非高峰时段运行抽取数据,平均要花费40分钟,有时候竟然要跑10个多小时。 sql语句比较长,需要点耐心往下看。我对...

3144
来自专栏数据库新发现

如何从结果集中获得随机结果

全表扫描(Full table Scan) 全表扫描返回表中所有的记录。 执行全表扫描,Oracle读表中的所有记录,考查每一行是否满足WHERE条件。Orac...

712

扫码关注云+社区