Chinaunix首页 | 论坛 | 博客
  • 博客访问: 1148834
  • 博文数量: 231
  • 博客积分: 2500
  • 博客等级: 少校
  • 技术积分: 2662
  • 用 户 组: 普通用户
  • 注册时间: 2009-11-03 16:35
个人简介

学无止境

文章分类

全部博文(231)

文章存档

2014年(7)

2013年(103)

2011年(11)

2010年(53)

2009年(57)

分类: Oracle

2013-12-23 15:56:52

建立反向键索引
SQL> create index idx_tindex_name on tindex(object_name) reverse;

Index created.

SQL> exec dbms_stats.gather_table_stats(ownname=>'LDY',tabname=>'tindex',cascade=>true);

PL/SQL procedure successfully completed.

反向键索引,并不能让前通配符的语句走索引范围扫描,经过测试,是索引快速全扫描,虽然比全表扫描快,但仍然有极大开销

SQL> select object_name from tindex where object_name like '%TINDEX';

Elapsed: 00:00:00.37

Execution Plan
----------------------------------------------------------
Plan hash value: 3672022132

----------------------------------------------------------------------------------------
| Id  | Operation            | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                 | 10158 |   247K|   197   (2)| 00:00:03 |
|*  1 |  INDEX FAST FULL SCAN| IDX_TINDEX_NAME | 10158 |   247K|   197   (2)| 00:00:03 |
----------------------------------------------------------------------------------------

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

   1 - filter("OBJECT_NAME" LIKE '%TINDEX')


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       1012  consistent gets
          0  physical reads
          0  redo size
        584  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)
          4  rows processed

建立反向函数索引
SQL> create index idx_tindex_name on tindex(reverse(object_name));

Index created.

SQL> exec dbms_stats.gather_table_stats(ownname=>'LDY',tabname=>'tindex',cascade=>true);

PL/SQL procedure successfully completed.

使用反向函数,将通配符变为在后面
SQL> select object_name from tindex where reverse(object_name) like 'XEDNIT%';

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 3095835948

-----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                 |     7 |   175 |     8   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TINDEX          |     7 |   175 |     8   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_TINDEX_NAME |     7 |       |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

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

   2 - access(REVERSE("OBJECT_NAME") LIKE 'XEDNIT%')
       filter(REVERSE("OBJECT_NAME") LIKE 'XEDNIT%')


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          8  consistent gets
          0  physical reads
          0  redo size
        584  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)
          4  rows processed

阅读(2513) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~