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

相关文章

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

关于oracle中的反连接(r3笔记第95天)

在之前的章节中见到讨论过oracle中的半连接 http://blog.itpub.net/23718752/viewspace-1334483/ 与半连接相对...

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

关于表联结方法(二) (r4笔记第23天)

在比较经典的表联结方法中,nested loop join和hash join是比较常用的,对于sort-merge join来说,可能略微有些陌生。 在数...

2604
来自专栏王小雷

SAS进阶《深入解析SAS》之对多数据集的处理

SAS进阶《深入解析SAS》之对多数据集的处理 1. 数据集的纵向串接: 数据集的纵向串接指的是,将两个或者多个数据集首尾相连,形成一个新的数据集。 据集的横...

2168
来自专栏YG小书屋

Hive数据倾斜优化:两个亿级表join

【现象】 出现reduce阶段一直卡在99%的情况,猜测可能出现数据倾斜问题。 【验证猜测】 1、查看user表strmd5个数,6亿左右,做distin...

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

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

在sql解析器中,在生成执行计划的时候,会在多个执行计划中选择最优的计划,在这个过程中,查询转换就是一个很重要的过程。 虽然最终的执行结果没有变化,但是从优化器...

2955
来自专栏数据库新发现

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

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

762
来自专栏乐沙弥的世界

Oracle ROWID 方式访问数据库

    和ROWNUM一样,ROWID是一个伪列,即是一个非用户定义的列,而又实际存储于数据库之中。每一个表都有一个ROWID列,一个ROWID值用于 唯一确定...

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

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

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

2854
来自专栏java一日一条

MySQL中无GROUP BY情况下直接使用HAVING语句的问题探究

这篇文章主要介绍了MySQL中无GROUP BY情况下直接使用HAVING语句的问题探究,同时探究了该情况下MAX与MIN功能的使用情况,需要的朋友可以参考下:

722
来自专栏乐沙弥的世界

INDEX FULL SCAN vs INDEX FAST FULL SCAN

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

652

扫码关注云+社区