ITPub博客

首页 > Linux操作系统 > Linux操作系统 > @dbsnake-用合适的函数索引来避免看似无法避免的全表扫描

@dbsnake-用合适的函数索引来避免看似无法避免的全表扫描

原创 Linux操作系统 作者:bisal 时间:2013-09-24 09:37:04 0 删除 编辑

昨天听了@dbsnake的SQL方法论,感觉比第一次要更有感觉,希望对实际工作能有帮助。


昨天讲到一处利用reverse函数建立索引,避免全表扫描的case,颇有感触,拿出来试一下。

SQL> create table rev (id number, name varchar2(5));
Table created.


SQL> select * from rev;
        ID NAME
---------- -----
         1 abc
         2 bc
         3 c


SQL> create index rev_idx0 on rev(name);
Index created.


SQL> set autot on
SQL> select id, name from rev where name like '%bc';
        ID NAME
---------- -----
         1 abc
         2 bc

Execution Plan
----------------------------------------------------------
Plan hash value: 3205185662


--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     2 |    34 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| REV  |     2 |    34 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("NAME" LIKE '%bc')

Note
-----
   - dynamic sampling used for this statement

Statistics
----------------------------------------------------------
          5  recursive calls
          0  db block gets
         16  consistent gets
          0  physical reads
          0  redo size
        633  bytes sent via SQL*Net to client
        492  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          2  rows processed

这里建立了name的B树索引,但由于使用了%bc为条件,所以不会用索引,这里用了全表扫描。


如何能让%bc条件使用索引呢?这里讲到%bc不能用索引的原因是因为索引键值按照索引二进制的顺序排序,%在前就无法精确定位,因此无法使用索引。既然%在后面可以使用索引,那就想办法将%的条件放在后面组织。


SQL> create index rev_idx on rev(reverse(name));
Index created.


SQL> select id, name from rev where reverse(name) like reverse('%bc');
        ID NAME
---------- -----
         2 bc
         1 abc

Execution Plan
----------------------------------------------------------
Plan hash value: 2418054352

---------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |     2 |    34 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| REV     |     2 |    34 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | REV_IDX |     1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access(REVERSE("NAME") LIKE 'cb%')
       filter(REVERSE("NAME") LIKE 'cb%')

Note
-----
   - dynamic sampling used for this statement

Statistics
----------------------------------------------------------
         28  recursive calls
          0  db block gets
         14  consistent gets
          0  physical reads
          0  redo size
        633  bytes sent via SQL*Net to client
        492  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          2  rows processed

这里用了reverse函数,相当于方向匹配字符串bc,这样就将前面的%放到了后面。从执行计划看cost从3变为2。


注:

这里可以看到无论哪次执行,物理读都是0,原因我觉得就是第一次执行过一个select * from rev;,因为数据量比较小,第一次select之后,记录就从data file缓存到buffer cache,即使根据LRU算法,负载不是太大的DB,很快的时间内这些数据还可能在其中,没被age out,所以再次执行SQL时,就可能物理读是0。


总结:

以上的示例就是@dbsnake讲的“用合适的函数索引来避免看似无法避免的全表扫描“。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/7192724/viewspace-773276/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
Oracle ACE,10g/11g OCP,11g OCM,国内首批Oracle YEP成员(Oracle Young Expert Program,Oracle用户组年轻专家项目),EXIN DevOps Master,Oracle爱好者,微信公众号:bisal的个人杂货铺

注册时间:2013-07-26

  • 博文量
    340
  • 访问量
    2629627