博学,审问,慎思,明辨,笃行

导航

ORACLE 分区与索引 (转)

引言:oracle的分区和索引可以说是它自己的亮点,可能你会说在其他数据库上也有,嗯是的,但oracle的种类 性能 便利性可以说是比较人性化的,下面我们通过实验来阐述它们的特性和功能

1.分别给出一个B-tree索引针对全表扫描性能高和低的例子。

索引定义:oracle数据库中索引就是为了加快数据访问速度的一种目录结构

B-tree索引特点:

1)二叉树结构

2)用比较大小方式查找索引块

3)适合创建在键值重复率低的字段

例如  主键字段:强调表的参照关系,即可以被外键引用

         唯一性约束字段:强调字段键值的唯一性

4)第一次扫描时,从root根节点进入,后面就不在返回进入了

5)叶子与叶子之间有指针链,不用返回上一层,可以直接定位到下一个叶子节点

6)主键字段做搜索时效率与数据量无关,例如 1万条记录  1亿条记录检索效率差不多

7)索引块是按顺序存放的,数据块是打散存放的

8)结果集越小性能越好,结果集越大性能越不好

9)相比位图索引,占用空间较多

实验

LEO1@LEO1> drop table leo1;

Table dropped.

LEO1@LEO1> drop table leo2;

Table dropped.

先清理环境,我们重新创建表和索引,看看在不同执行计划下的性能如何。

LEO1@LEO1> create table leo1 as select * from dba_objects;

Table created.

我们创建leo1表用于全表扫描

LEO1@LEO1> create table leo2 as select * from dba_objects;

Table created.

我们创建leo2表用于走B-tree索引

LEO1@LEO1> create index idx_leo2 on leo2(object_id);

Index created.

LEO1@LEO1> execute dbms_stats.gather_table_stats('LEO1','LEO1',cascade=>true);

PL/SQL procedure successfully completed.

LEO1@LEO1> execute dbms_stats.gather_table_stats('LEO1','LEO2',cascade=>true);

PL/SQL procedure successfully completed.

leo1leo2表及表上的索引进行统计分析,以便让oracle了解它们的数据分布情况

LEO1@LEO1> select table_name, num_rows, last_analyzed, object_type from dba_tab_statistics  where owner = 'LEO1';

TABLE_NAME                       NUM_ROWS  LAST_ANAL  OBJECT_TYPE

------------------------------ ---------- --------- -------------------------------- ---------- --------- -------

LEO1                                71961     09-JAN-13    TABLE

LEO2                                71962     09-JAN-13    TABLE

好已经显示出对2张表进行统计分析了,并且还知道了表上的数据有71961行和71962

LEO1@LEO1> set autotrace traceonly      启动执行计划

LEO1@LEO1> select * from leo1 where object_id=10000;      

Execution Plan

----------------------------------------------------------

Plan hash value: 2716644435

--------------------------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      |     1 |    97 |   287   (1)| 00:00:04 |

|*  1 |  TABLE ACCESS FULL | LEO1  |     1 |    97 |   287   (1)| 00:00:04 |

--------------------------------------------------------------------------

走全表扫描,代价Cost=287

Predicate Information (identified by operation id):

---------------------------------------------------------------------------

   1 - filter("OBJECT_ID"=10000)           谓词条件

Statistics

---------------------------------------------------------------------------

          1  recursive calls

          0  db block gets

       1031  consistent gets             1031个一致性读

       1026  physical reads

          0  redo size

       1626  bytes sent via SQL*Net to client

        524  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

LEO1@LEO1> select * from leo2 where object_id=10000;     

Execution Plan

----------------------------------------------------------

Plan hash value: 2495991774

----------------------------------------------------------------------------------------

| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |          |     1 |    97 |     2   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID | LEO2      |     1 |    97 |     2   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN         | IDX_LEO2  |     1 |       |     1   (0)| 00:00:01 |

----------------------------------------------------------------------------------------

B-tree索引,代价Cost=2

Predicate Information (identified by operation id):

----------------------------------------------------------------------------------------

   2 - access("OBJECT_ID"=10000)        谓词条件

Statistics

----------------------------------------------------------

          1  recursive calls

          0  db block gets

          4  consistent gets     4个一致性读=先访问root->在找branch->在找leaf+递归IO

          0  physical reads

          0  redo size

       1629  bytes sent via SQL*Net to client

        524  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

我们从上面的执行计划可以看出,走B-tree索引效率要比全表扫描高出很多很多,尤其在键值重复率低的字段非常适合使用B-tree索引(流程:先访问root->在找branch->在找leaf->在找到键值key->访问对应ROWID数据块->提取数据),我们还要知道当结果集越小使用索引访问速度越快,如果结果集较大那么,我们看看性能如何呢?

LEO1@LEO1> select * from leo1 where object_id>=10000;

62253 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 2716644435

--------------------------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      | 62216 |  5893K|   287   (1)| 00:00:04 |

|*  1 |  TABLE ACCESS FULL | LEO1  | 62216 |  5893K|   287   (1)| 00:00:04 |

--------------------------------------------------------------------------

走全表扫描,代价Cost=287

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter("OBJECT_ID">=10000)

Statistics

----------------------------------------------------------

          0  recursive calls

          0  db block gets

       5118  consistent gets                 5118个一致性读

          0  physical reads

          0  redo size

    3245084  bytes sent via SQL*Net to client

      46174  bytes received via SQL*Net from client

       4152  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

      62253  rows processed

LEO1@LEO1> select /*+ index(leo2 idx_leo2) */ * from leo2 where object_id>=10000;

62254 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 2495991774

----------------------------------------------------------------------------------------

| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |          | 62217 |  5893K|  1073   (1)| 00:00:13 |

|   1 |  TABLE ACCESS BY INDEX ROWID| LEO2     | 62217 |  5893K|  1073   (1)| 00:00:13 |

|*  2 |   INDEX RANGE SCAN         | IDX_LEO2 | 62217 |       |   139   (0)| 00:00:02 |

----------------------------------------------------------------------------------------

B-tree索引,代价Cost=1073

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("OBJECT_ID">=10000)

Statistics

----------------------------------------------------------

          1  recursive calls

          0  db block gets

       9312  consistent gets             哇塞居然9312个一致性读

         49  physical reads

          0  redo size

    7232860  bytes sent via SQL*Net to client

      46174  bytes received via SQL*Net from client

       4152  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

      62254  rows processed

我用hint方式强制走索引,很明显索引的效率大大低于全表扫描,看来CBO的判断还是正确的,因为会出现这种情况呢,走索引的原理是先访问一次索引块,在访问一次数据块,这样便至少是2IO,当你查询结果集越大时,消耗IO资源就越多(一致性读次数就越多),所以呢还不如直接访问表效率高,你这时很聪明,可能会问结果集多大时,索引效率就不好了呢,一般超过总量1/5时效率就会变差,这只是一个经验值,大家要多多测试出真知。

 

2.分别给出一个Bitmap索引针对b-tree索引性能高和低的例子。

Bitmap索引特点:

1)键值行结构

2)使用位图标识键值

3)适合创建在键值重复率高的字段

4)键值重复率越高,占用的空间越少,每个独立键值占用一行

5)适合OLAP系统

6DML操作会锁定整个位图索引段,导致阻塞和无法大并发

7)位运算效率非常高,例如  and   or   not  运算

实验

注:我的实验要结合上下文一起看哦,有的表或者对象会在上面的实验中创建

LEO1@LEO1> create table leo3 as select * from dba_objects;    创建leo3leo2表结构一样

Table created.

LEO1@LEO1> create bitmap index idx_leo3 on leo3(object_id);  创建bitmap索引

Index created.

LEO1@LEO1> execute dbms_stats.gather_table_stats('LEO1','LEO3',cascade=>true);  统计分析

PL/SQL procedure successfully completed.

LEO1@LEO1> set autotrace off;

LEO1@LEO1> select table_name, num_rows, last_analyzed, object_type from dba_tab_statistics  where owner = 'LEO1';

TABLE_NAME                       NUM_ROWS LAST_ANAL OBJECT_TYPE

------------------------------ ---------- --------- ------------

LEO1                                71961 09-JAN-13 TABLE

LEO2                                71962 09-JAN-13 TABLE

LEO3                                71964 09-JAN-13 TABLE

LEO1@LEO1> select count(*) from leo2 where object_id>10000;

Execution Plan

----------------------------------------------------------

Plan hash value: 788375040

----------------------------------------------------------------------------------

| Id  | Operation             | Name     | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------------

|   0 | SELECT STATEMENT      |          |     1 |     5 |    45   (0)| 00:00:01 |

|   1 |  SORT AGGREGATE      |          |     1 |     5 |            |          |

|*  2 |   INDEX FAST FULL SCAN | IDX_LEO2 | 62216 |   303K|    45   (0)| 00:00:01 |

----------------------------------------------------------------------------------

快速索引全扫描,把索引链分割成若干区域,多索引块并行扫描,所以很快,Cost=45

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - filter("OBJECT_ID">10000)

Statistics

----------------------------------------------------------

          1  recursive calls

          0  db block gets

        167  consistent gets                 167个一致性读,一次IO读取多个块

          1  physical reads

          0  redo size

        528  bytes sent via SQL*Net to client

        524  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

LEO1@LEO1> select count(*) from leo3 where object_id>10000;

Execution Plan

----------------------------------------------------------

Plan hash value: 1835111598

--------------------------------------------------------------------------------------

| Id  | Operation                 | Name     | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT          |          |     1 |     5 |   218   (0)| 00:00:03 |

|   1 |  SORT AGGREGATE           |          |     1 |     5 |            |          |

|   2 |   BITMAP CONVERSION COUNT |          | 62218 |   303K|   218   (0)| 00:00:03 |

|*  3 |    BITMAP INDEX RANGE SCAN | IDX_LEO3 |       |       |            |          |

--------------------------------------------------------------------------------------

位图索引范围扫描->位图值转换成统计值->集合排序,Cost=218

Predicate Information (identified by operation id):

---------------------------------------------------

   3 - access("OBJECT_ID">10000)

       filter("OBJECT_ID">10000)

Statistics

----------------------------------------------------------

          1  recursive calls

          0  db block gets

        219  consistent gets                 219个一致性读

          0  physical reads

          0  redo size

        528  bytes sent via SQL*Net to client

        524  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

LEO1@LEO1> select segment_name,extents,blocks from dba_segments where segment_name in ('IDX_LEO4','IDX_LEO5');

SEGMENT_NAME                   EXTENTS      BLOCKS

IDX_LEO2    B-tree索引            17           256

IDX_LEO3    Bitmap索引           18           384      

因为B-tree索引和Bitmap索引组成结构不同,在Bitmap索引中每个独立键值占用一行,我们知道object_id是没有重复值的,所以组成Bitmap索引时每个object_id键值都占用一行,因此就比B-tree索引占用的索引块多,占用的索引块多扫描的就多一致性IO就多效率就低,总而言之,Bitmap索引适合重复率高的字段

LEO1@LEO1> create table leo4 as select * from dba_objects;     创建leo4

Table created.

LEO1@LEO1> create table leo5 as select * from dba_objects;     创建leo5

Table created.

LEO1@LEO1> create index idx_leo4 on leo4(object_type);        创建B-tree

Index created.

LEO1@LEO1> create bitmap index idx_leo5 on leo5(object_type);  创建Bitmap

Index created.

LEO1@LEO1> execute dbms_stats.gather_table_stats('LEO1','LEO4',cascade=>true);   统计分析

PL/SQL procedure successfully completed.

LEO1@LEO1> execute dbms_stats.gather_table_stats('LEO1','LEO5',cascade=>true);   统计分析

PL/SQL procedure successfully completed.

LEO1@LEO1> select table_name, num_rows, last_analyzed, object_type from dba_tab_statistics  where owner = 'LEO1';

TABLE_NAME                       NUM_ROWS LAST_ANAL OBJECT_TYPE

------------------------------ ---------- --------- ------------

LEO1                                71961 09-JAN-13 TABLE

LEO2                                71962 09-JAN-13 TABLE

LEO3                                71964 09-JAN-13 TABLE

LEO4                                71966 09-JAN-13 TABLE

LEO5                                71967 09-JAN-13 TABLE

LEO1@LEO1> select * from leo4 where object_type='TABLE';

2807 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 412720909

----------------------------------------------------------------------------------------

| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |          |  1674 |   158K|    75   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID | LEO4     |  1674 |   158K|    75   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN         | IDX_LEO4 |  1674 |       |     5   (0)| 00:00:01 |

----------------------------------------------------------------------------------------

B-tree索引范围扫描,Cost=75

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("OBJECT_TYPE"='TABLE')

Statistics

----------------------------------------------------------

         99  recursive calls

          0  db block gets

        568  consistent gets                568个一致性读

          0  physical reads

          0  redo size

     312088  bytes sent via SQL*Net to client

       2581  bytes received via SQL*Net from client

        189  SQL*Net roundtrips to/from client

          3  sorts (memory)

          0  sorts (disk)

       2807  rows processed

LEO1@LEO1> select * from leo5 where object_type='TABLE';

2808 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 174753293

-----------------------------------------------------------------------------------------

| Id  | Operation                    | Name     | Rows  | Bytes | Cost (%CPU)| Time     |

-----------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT             |          |  1674 |   158K|   203   (0)| 00:00:03 |

|   1 |  TABLE ACCESS BY INDEX ROWID  | LEO5     |  1674 |   158K|   203   (0)| 00:00:03 |

|   2 |   BITMAP CONVERSION TO ROWIDS|         |      |       |           |        |

|*  3 |    BITMAP INDEX SINGLE VALUE  | IDX_LEO5 |       |       |            |        |

-----------------------------------------------------------------------------------------

Bitmap索引

Predicate Information (identified by operation id):

---------------------------------------------------

   3 - access("OBJECT_TYPE"='TABLE')

Statistics

----------------------------------------------------------

          1  recursive calls

          0  db block gets

        355  consistent gets           355个一致性读

          0  physical reads

          0  redo size

     312171  bytes sent via SQL*Net to client

       2581  bytes received via SQL*Net from client

        189  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

       2808  rows processed

LEO1@LEO1> select segment_name,extents,blocks from dba_segments where segment_name in ('IDX_LEO4','IDX_LEO5');

SEGMENT_NAME          EXTENTS      BLOCKS

IDX_LEO4                 17           256

IDX_LEO5                 2            16

如上所示两个索引各自占用空间情况,下面我们来分析一下,在键值重复率高情况下为什么位图索引效率好?

不出我们所料Bitmap索引比B-tree索引效率要高哦,正好中了键值重复率越高,占用的空间越少这句话,因为object_type字段有很多重复键值,我们在查询过程中只对object_type='TABLE'的“键值行”扫描一遍即可知道哪些记录符合条件,从占用索引块数量上也能看出扫描16个块要比扫描256个块快大发啦!哈哈

3.演示DML操作导致位图索引锁定位图段示例

Bitmap锁定特点:

1)当我们操作同一个“键值行”时,会产生锁定整个键值行

2)所以不建议频繁进行DML操作

3)适合OLAP系统  例如  报表生成  位运算  统计分析

实验

LEO1@LEO1> select distinct sid from v$mystat;      显示当前会话id,用于区别其他会话操作

       SID

----------------------

       133

会话133

LEO1@LEO1> create table leo6 (id int,name varchar2(20));    创建leo6表,2个字段

Table created.

LEO1@LEO1> create bitmap index idx_leo6 on leo6(id);       id字段上创建位图索引

Index created.

LEO1@LEO1> insert into leo6 values(1,'leo');                插入记录

1 row created.

LEO1@LEO1> insert into leo6 values(1,'sun');                插入记录

1 row created.

LEO1@LEO1> commit;                                  提交

Commit complete.

LEO1@LEO1> select * from leo6;                          显示数据

        ID NAME

----------------------------

         1 leo

         1 sun

Id列的值相同,从Bitmap索引结构上看,这两条记录都是在同一个键值行上,我们如果操作其中的一条记录那么就是对这个键值行操作

LEO1@LEO1> update leo6 set id=2 where name='leo';         更新一条记录

1 row updated.

会话157,重新打开一个会话

LEO1@LEO1> update leo6 set id=2 where name='sun';   

这时你会惊讶的发现光标不动了,命令提示符也不能显示出来了,怎么回事,想一想你是不是在哪里碰到过这种情况,对这就是传说中的“阻塞”现象,是被第133会话给阻塞了,而我们原来碰到的是当2个会话同时修改同一条记录时会发生阻塞,而现在我们更新的是2条不同记录为什么也会发生阻塞呢,一位伟人说过“存在即合理”,那么既然发生了,就必然会有关系,只是这种关系与记录无关,而是发生在索引键值行上。

这就是Bitmap索引的一个特性:DML操作会锁定整个位图段(键值行)导致阻塞现象,这是因为oracle为了保证数据一致性和完整性,必须将索引键值行锁定,防止其他会话对其修改,归根结底这都是由于位图索引的构造原理造成的,一个键值行对应多条记录,当其中任意记录值被修改时,oracle会锁定整个键值行信息,此时另一个会话如果要修改这个键值行中的记录时,这个操作就会被之前的操作所阻塞。

解决方案:要么commit/rollback 133会话,要么终止157会话

我们从v$lock视图中看一下锁定情况

LEO1@LEO1> select sid,type,id1,id2,lmode,request,block from v$lock where sid in (133,157);

       SID TY        ID1        ID2      LMODE    REQUEST      BLOCK

---------- -- ---------- ---------- ---------- ---------- ------------ -- ---------- ---------- ---------- ----------

       133 AE        100          0          4          0          0

       157 AE        100          0          4          0          0

       133 TO      65927          1          3          0          0

       133 TO       5003          1          3          0          0

       157 TM      73837          0          3          0          0

       133 TM      73837          0          3          0          0

       157 TX     589827       1307          6          0          0

       133 TX     131088       1284          6          0          1

       157 TX     131088       1284          0          4          0

SID:会话id

ID1+ID2:修改数据块上记录地址

LMODE:持有的锁类型

REQUEST:正在请求的锁

BLOCK:阻塞会话个数

说明:133会话正在持有一个6TX事务锁(排他锁)并且正好阻塞另一个会话,从ID1+ID2地址上看133会话恰恰正在阻塞157会话,而157会话目前没有锁正在请求一个4TX事务锁,只有133会话提交后才会释放6TX

133会话

LEO1@LEO1> commit;

Commit complete.

LEO1@LEO1> select sid,type,id1,id2,lmode,request,block from v$lock where sid in (133,157);

       SID TY        ID1        ID2      LMODE    REQUEST      BLOCK

---------- -- ---------- ---------- ---------- ---------- ----------

       133 AE        100          0          4          0          0

       157 AE        100          0          4          0          0

       133 TO      65927          1          3          0          0

       133 TO       5003          1          3          0          0

       157 TM      73837          0          3          0          0

       157 TX      589827       1307          6          0          0

此时133会话的6TX锁已经被释放了,157会话阻塞解除可以继续操作并获得一个6级锁

4.创建一个全文索引(Text index),比较它和传统的模糊查询的性能。

全文索引特点:

(1)使用字符串拆字方法检索“字 词 短语”,适合文本搜索

(2)场景在用文本模糊查询时,使用全文索引比较高效  例 where name like ‘%leo%’

(3)我们目的找到所有包含“leo”字符串的记录,由于leo字符串在字段中的位置是不固定的,使用B-tree索引就没有办法进行检索,而使用全文索引就可以很好按照拆分字的原理进行检索

(4)全文索引是一个逻辑名,内部包含许多基表和基索引,它们实际占用空间,而全文索引名不占用空间不是段对象,这些内部对象组成了全文索引

(5)全文索引占用空间大,一般全文索引是基表大小1.5倍

(6)管理维护成本高,bug多

实验

LEO1@LEO1> create table leo7 (id number,name varchar2(20));      创建leo7表

Table created.

LEO1@LEO1> create index idx_leo7 on leo7(name);        在name字段创建B-tree索引

Index created.

LEO1@LEO1> insert into leo7 values (1,'leo');             

1 row created.

LEO1@LEO1> insert into leo7 values (2,'leo leo');

1 row created.

LEO1@LEO1> insert into leo7 values (3,'leo leo leo');        插入3条记录

1 row created.

LEO1@LEO1> commit;                                提交

Commit complete.

LEO1@LEO1> select * from leo7;                        显示有3条

        ID NAME

---------- --------------------

         1 leo

         2 leo leo

         3 leo leo leo

LEO1@LEO1> create table leo8 as select * from leo7;       我们通过leo7创建leo8表

Table created.

LEO1@LEO1> create index idx_text_leo8 on leo8(name) indextype is ctxsys.context;

Index created.

注:我们在创建oracle 11g全文索引时等待时间较长,大约8秒钟,为什么会这么长时间呢,它又做了哪些内容呢?

(1)先检查oracle是否已安装“全文检索工具”,oracle11g 默认安装的,oracle10g默认没有安装

(2)再进行“语法分析”就是我们上面提到的“拆字”过程,例如创建词法分析器及相关表等操作

如果你的是oracle10g数据库,请参考《oracle10g 不能成功创建全文索引的解决方法》http://f.dataguru.cn/thread-49019-1-1.html

LEO1@LEO1> set autotrace on                         显示执行计划

LEO1@LEO1> select * from leo7 where name like '%leo%';

        ID NAME

---------- --------------------

         1 leo

         2 leo leo

         3 leo leo leo

Execution Plan

----------------------------------------------------------

Plan hash value: 598568836

----------------------------------------------------------------------------------------

| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |          |     3 |    75 |     0   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| LEO7     |     3 |    75 |     0   (0)| 00:00:01 |

|*  2 |   INDEX FULL SCAN           | IDX_LEO7 |     1 |       |     0   (0)| 00:00:01 |

----------------------------------------------------------------------------------------

走的是索引全扫描,当数据量小的时候还可以,大了就差些了

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - filter("NAME" IS NOT NULL AND "NAME" LIKE '%leo%') 执行计划重写了谓词条件,因为它发现这样更高效

Note

-----

   - dynamic sampling used for this statement (level=2)

Statistics

----------------------------------------------------------

          5  recursive calls

          0  db block gets

         12  consistent gets                           12个一致性读

          0  physical reads

          0  redo size

        680  bytes sent via SQL*Net to client

        524  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          3  rows processed

LEO1@LEO1> select * from leo8 where contains(name,'leo')>0;

        ID NAME

---------- --------------------

         1 leo

         2 leo leo

         3 leo leo leo

Execution Plan

----------------------------------------------------------

Plan hash value: 287112382

---------------------------------------------------------------------------------------------

| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |          |     1 |    37 |     4   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID  | LEO8    |     1 |    37 |     4   (0)| 00:00:01 |

|*  2 |   DOMAIN INDEX              | IDX_TEXT_LEO8 | |       |     4   (0)| 00:00:01 |

---------------------------------------------------------------------------------------------

域索引就是全文索引,可能会有当第一次执行的时候效率较低,多执行几次后效率就提高了

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("CTXSYS"."CONTAINS"("NAME",'leo')>0)

Note

-----

   - dynamic sampling used for this statement (level=2)

Statistics

----------------------------------------------------------

         11  recursive calls

          0  db block gets

         11  consistent gets              11个一致性读

          0  physical reads

          0  redo size

        680  bytes sent via SQL*Net to client

        524  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          3  rows processed

小结:使用全文索引的时候,当检索数据量大时 短语组成较复杂时效率较好。



5.分别演示分区索引的性能优化全局索引和差于全局索引的示例,并分析原因。

本地分区索引特点:

(1)本地分区索引擅长某一个分区内检索数据,缩小扫描范围速度就上去了

(2)当跨分区检索时,由于每个分区上面都有一个独立本地索引,oracle搜索引擎就会遍历每个分区的独立索引段和数据段(在多个索引段间跳跃),这样系统IO就会增大多倍,效率降低

(3)本地分区索引在每个分区要么都有,要么都没有

(4)DDL操作无需rebuild索引,不会导致索引无效

全局分区索引特点:

(1)跨分区检索数据效率较高,因为是一个整体无需跳跃,节约了IO和索引块管理开销

(2)分区内扫描效率没有本地索引高,因为扫描范围较大

(3)DDL操作会导致索引无效  例如  增分区  删分区  truncate分区都需要rebuild索引

(4)一个分区表上经常有DDL操作,将会导致全局索引无效(必须rebuild),在建全局索引时请注意这点

分区优势:体现管理收益而不是性能收益,它在管理和规划上方便了我们运维数据,而性能上不一定能提高多少

实验跨分区扫描

LEO1@LEO1> create table partition_leo9   创建分区表,分区越多实验效果越明显

partition by range (object_id)

(

partition p1 values less than(10000),

partition p2 values less than(20000),

partition p3 values less than(30000),

partition p4 values less than(40000),

partition p5 values less than(50000),

partition p6 values less than(60000),

partition pm values less than(maxvalue)

) as select * from dba_objects;  2    3    4    5    6    7    8    9   10   11  

Table created.

LEO1@LEO1> create table partition_leo10   创建partition_leo10与partition_leo9表结构一样

partition by range (object_id)

(

partition p1 values less than(10000),

partition p2 values less than(20000),

partition p3 values less than(30000),

partition p4 values less than(40000),

partition p5 values less than(50000),

partition p6 values less than(60000),

partition pm values less than(maxvalue)

) as select * from dba_objects;  2    3    4    5    6    7    8    9   10   11  

Table created.

LEO1@LEO1> select count(*) from partition_leo9 partition (p1);

  COUNT(*)

------------------

      9708

LEO1@LEO1> select count(*) from partition_leo10 partition (p1);

  COUNT(*)

------------------

      9708

LEO1@LEO1> select count(*) from partition_leo9 partition (p2);

  COUNT(*)

------------------

      9806

LEO1@LEO1> select count(*) from partition_leo9 partition (p3);

  COUNT(*)

------------------

     10000

LEO1@LEO1> select count(*) from partition_leo9 partition (p4);

  COUNT(*)

------------------

     10000

LEO1@LEO1> select count(*) from partition_leo9 partition (p5);

  COUNT(*)

------------------

     10000

LEO1@LEO1> select count(*) from partition_leo9 partition (p6);

  COUNT(*)

------------------

      9606

LEO1@LEO1> select count(*) from partition_leo9 partition (pm);

  COUNT(*)

------------------

     12872

我们基于每个分区查看一下记录数

LEO1@LEO1> create index idx_partition_leo9 on partition_leo9(object_id);   创建一个全局分区索引

Index created.

LEO1@LEO1> create index idx_partition_leo10 on partition_leo10(object_id) local;创建一个本地分区索引

Index created.

LEO1@LEO1> select table_name,index_name,partitioned,status from dba_indexes where table_name in ('PARTITION_LEO9','PARTITION_LEO10');

TABLE_NAME      INDEX_NAME          PAR   STATUS

------------------------------ ------------------------------ --------------------------- --- -------------------- --- -------------------

PARTITION_LEO10  IDX_PARTITION_LEO10   YES    N/A       本地分区索引,分成7个索引段

PARTITION_LEO9   IDX_PARTITION_LEO9    NO    VALID      全局分区索引,就1个索引段

LEO1@LEO1> select count(*) from partition_leo9 where object_id>1000;

  COUNT(*)

-----------------

     71049

Execution Plan

----------------------------------------------------------

Plan hash value: 549732231

----------------------------------------------------------------------------------------

| Id  | Operation         | Name               | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |                  |     1 |    13 |    12   (0)| 00:00:01 |

|   1 |  SORT AGGREGATE   |                |     1 |    13 |            |          |

|*  2 |   INDEX RANGE SCAN| IDX_PARTITION_LEO9 |  3997 | 51961 |    12   (0)| 00:00:01 |

----------------------------------------------------------------------------------------

走全局索引,在跨分区扫描数据时,效率要好于本地索引,因为不需要再多个索引段间跳跃

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("OBJECT_ID">1000)

Note

-----

   - dynamic sampling used for this statement (level=2)

Statistics

----------------------------------------------------------

          0  recursive calls

          0  db block gets

        199  consistent gets                  199个一致性读

          0  physical reads

          0  redo size

        528  bytes sent via SQL*Net to client

        524  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

LEO1@LEO1> select count(*) from partition_leo10 where object_id>1000;

  COUNT(*)

-----------------

     71057

Execution Plan

----------------------------------------------------------

Plan hash value: 3364377641

------------------------------------------------------------------------------------------------------------

| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |

------------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT     |     |     1 |    13 |    12   (0)| 00:00:01 |       |       |

|   1 |  SORT AGGREGATE   |      |     1 |    13 |           |        |        |       |

|   2 |   PARTITION RANGE ALL|    |  3947 | 51311 |    12   (0)| 00:00:01 |    1 |     7 |

|*  3 |    INDEX RANGE SCAN | IDX_PARTITION_LEO10 | 3947 | 51311 | 12 (0)| 00:00:01 | 1 |   7 |

------------------------------------------------------------------------------------------------------------

走本地索引,Pstart:扫描的起始分区号 1,Pstop:扫描的结束分区号 7,从这里可以看出执行计划在7个分区中的7个本地索引段间进行了跳跃扫描,这样就增大了系统开销

Predicate Information (identified by operation id):

---------------------------------------------------

   3 - access("OBJECT_ID">1000)

Note

-----

   - dynamic sampling used for this statement (level=2)

Statistics

----------------------------------------------------------

        152  recursive calls

          0  db block gets

        290  consistent gets                   290个一致性读,比全局索引多

        162  physical reads

          0  redo size

        528  bytes sent via SQL*Net to client

        524  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

小结:在跨分区检索数据时,全局索引效率好于本地索引,原因上面我已经阐述说明了。

实验分区内扫描

LEO1@LEO1> select /*+ index(partition_leo9 idx_partition_leo9) */ count(*) from partition_leo9 where object_id>1000 and object_id<8000;

  COUNT(*)

----------------

      6891

Execution Plan

----------------------------------------------------------

Plan hash value: 549732231

----------------------------------------------------------------------------------------

| Id  | Operation         | Name               | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |                    |     1 |    13 |    71   (0)| 00:00:01 |

|   1 |  SORT AGGREGATE  |                   |     1 |    13 |            |          |

|*  2 |   INDEX RANGE SCAN| IDX_PARTITION_LEO9 |  3180 | 41340 |    71   (0)| 00:00:01 |

----------------------------------------------------------------------------------------

如果不加hints的话CBO会走全表扫描的(其实全表扫描效率并不高,这次CBO犯傻了),所以我们强制使用hints走全局索引,因为只在一个分区内检索,还要扫描整个索引段,没有缩小扫描范围,效率自然没有本地索引高

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("OBJECT_ID">1000 AND "OBJECT_ID"<8000)

Note

-----

   - dynamic sampling used for this statement (level=2)

Statistics

----------------------------------------------------------

          0  recursive calls

          0  db block gets

         20  consistent gets                  20个一致性读

          0  physical reads

          0  redo size

        527  bytes sent via SQL*Net to client

        524  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

LEO1@LEO1> select /*+ index(partition_leo10 idx_partition_leo10) */ count(*) from partition_leo10 where object_id>1000 and object_id<8000;

  COUNT(*)

----------

      6891

Execution Plan

----------------------------------------------------------

Plan hash value: 785094176

---------------------------------------------------------------------------------------------------------------

| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |

---------------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT|        |     1 |    13 |    67   (0)| 00:00:01 |       |       |

|   1 |  SORT AGGREGATE|       |     1 |    13 |            |          |       |       |

|   2 |   PARTITION RANGE SINGLE|   |  3147 | 40911 |    67   (0)| 00:00:01 |     1 |     1 |

|*  3 |    INDEX RANGE SCAN| IDX_PARTITION_LEO10 |  3147 | 40911 |    67   (0)| 00:00:01 |     1 |     1 |

---------------------------------------------------------------------------------------------------------------

同理我们强制使用hints走本地索引,看我们起始扫描分区和结束扫描分区都是同一个,哈->这就说明CBO执行计划只对这一个分区做扫描,缩小了扫描的范围,自然效率就高

Predicate Information (identified by operation id):

---------------------------------------------------

   3 - access("OBJECT_ID">1000 AND "OBJECT_ID"<8000)

Note

-----

   - dynamic sampling used for this statement (level=2)

Statistics

----------------------------------------------------------

          0  recursive calls

          0  db block gets

         17  consistent gets                      17个一致性读比全局索引少

          0  physical reads

          0  redo size

        527  bytes sent via SQL*Net to client

        524  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

小结:在分区内扫描数据时,本地索引优于全局索引,原理我在上面都阐述清楚了,欢迎大家来讨论交流。

转自:http://yangtingkun.itpub.net/post/468/391015

posted on 2014-03-09 00:25  pengdaijun  阅读(252)  评论(0编辑  收藏  举报