Oracle分区表创建本地索引和局部索引的性能

1、演示环境:

SQL> select * from v$version;

BANNER

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

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi

PL/SQL Release 10.2.0.4.0 - Production

CORE 10.2.0.4.0 Production

TNS for 64-bit Windows: Version 10.2.0.4.0 - Production

NLSRTL Version 10.2.0.4.0 - Production

2、测试数据创建:
表创建SQL如下:

如下:

create table T65_F_S_PGZ10023_M

(

  STATISTIC_DT DATE,

  INDICKEY VARCHAR2(20),

  OBJKEY VARCHAR2(64),

  INDICVAL1 NUMBER(30,2),

  INDICVAL2 NUMBER(30,2),

  INDICVAL3 NUMBER(30,2),

  INDICVAL4 NUMBER(30,2),

  INDICVAL5 NUMBER(30,2)

)

partition by range (STATISTIC_DT)

(

  partition PT_20111231 values less than (TO_DATE(\'2012-01-01 00:00:00\', \'YYYY-MM-DD HH24:MI:SS\')) tablespace tsdat01,

  partition PT_20120101 values less than (TO_DATE(\'2012-01-02 00:00:00\', \'YYYY-MM-DD HH24:MI:SS\')) tablespace tsdat01,

  partition PT_20120102 values less than (TO_DATE(\'2012-01-03 00:00:00\', \'YYYY-MM-DD HH24:MI:SS\')) tablespace tsdat01,

  partition PT_20120103 values less than (TO_DATE(\'2012-01-04 00:00:00\', \'YYYY-MM-DD HH24:MI:SS\')) tablespace tsdat01,

  partition PT_20120104 values less than (TO_DATE(\'2012-01-05 00:00:00\', \'YYYY-MM-DD HH24:MI:SS\')) tablespace tsdat01,

  partition PT_20120105 values less than (TO_DATE(\'2012-01-06 00:00:00\', \'YYYY-MM-DD HH24:MI:SS\')) tablespace tsdat01,

  partition PT_20120106 values less than (TO_DATE(\'2012-01-07 00:00:00\', \'YYYY-MM-DD HH24:MI:SS\')) tablespace tsdat01,

  partition PT_20120107 values less than (TO_DATE(\'2012-01-08 00:00:00\', \'YYYY-MM-DD HH24:MI:SS\')) tablespace tsdat01,

  partition PT_20120108 values less than (TO_DATE(\'2012-01-09 00:00:00\', \'YYYY-MM-DD HH24:MI:SS\')) tablespace tsdat01,

  partition PT_20120109 values less than (TO_DATE(\'2012-01-10 00:00:00\', \'YYYY-MM-DD HH24:MI:SS\')) tablespace tsdat01,

  partition PT_20120100 values less than (TO_DATE(\'2012-01-11 00:00:00\', \'YYYY-MM-DD HH24:MI:SS\')) tablespace tsdat01,

  partition PT_20120111 values less than (TO_DATE(\'2012-01-12 00:00:00\', \'YYYY-MM-DD HH24:MI:SS\')) tablespace tsdat01

);

CREATE INDEX PI_T65_F_S_PGZ10023_M ON T65_F_S_PGZ10023_M (OBJKEY) TABLESPACE TSDAT01 PARALLEL LOCAL;

 

 

测试数据生成SQL如下:

SQL>insert into T65_F_S_PGZ10023_M(INDICKEY,STATISTIC_DT,OBJKEY,INDICVAL1)

 select 'PGZ10023' as INDICKEY, to_date('2012-01-08','yyyy-mm-dd') as STATISTIC_DT ,'81'||lpad(to_char(rownum),7,'0') as OBJKEY  ,trunc(dbms_random.value(1,rownum)) as INDICVAL1 from dual connect by  rownum <100000;

 

修改日期可以得到2012-01-01 到2012-01-08这8天的测试数据

测试数据按天汇总的记录数

SQL>  select statistic_dt,count(*) from t65_f_s_pgz10023_m group by statistic_dt;
 
STATISTIC_DT   COUNT(*)
------------ ----------
2012-01-01        99999
2012-01-02        99999
2012-01-03        99999
2012-01-04        99999
2012-01-05        99999
2012-01-06        99999
2012-01-07        99999
2012-01-08        99999

3 使用AUTOTRACE查看执行计划

打开CMD命令窗口,使用 aml 用户连接数据库

C:\Users\Administrator>sqlplus aml/abc

开启执行计划

SQL> set autotrace traceonly

设置行长度

SQL> set lines 300;
SQL> 
CREATE INDEX PI_T65_F_S_PGZ10023_M ON T65_F_S_PGZ10023_M (OBJKEY) TABLESPACE TSDAT01 local;
SQL> select * from T65_F_S_PGZ10023_M where  objkey='810000836';

得到如下的执行计划,此索引是本地索引

SQL> select * from T65_F_S_PGZ10023_M where  objkey='810000836';

8 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2061015615


---------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                               | Name                             |  Rows  | Bytes | Cost (%CPU)| Time      | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                                 |                                      |     8    |   960 |    14   (0)    | 00:00:01 |         |        |
|   1 |  PARTITION RANGE ALL                             |                                     |     8    |   960 |    14   (0)    | 00:00:01 |     1  |    12 |
|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID    | T65_F_S_PGZ10023_M     |     8    |   960 |    14   (0)    | 00:00:01 |     1  |    12 |
|*  3 |    INDEX RANGE SCAN                              | PI_T65_F_S_PGZ10023_M |     8    |         |    13   (0)    | 00:00:01 |     1 |    12 |
---------------------------------------------------------------------------------------------------------------------------

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

   3 - access("OBJKEY"='810000836')

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

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

SQL>


我们删除本地索引,现在创建局部索引

SQL> drop index PI_T65_F_S_PGZ10023_M;

SQL> CREATE INDEX I_T65_F_S_PGZ10023_M ON T65_F_S_PGZ10023_M (OBJKEY) TABLESPACE TSDAT01 global;

将缓冲池清空

SQL> alter system flush buffer_cache;

再次执行刚才的SQL

SQL> select * from T65_F_S_PGZ10023_M where  objkey='810000836';

全局索引的执行计划

8 rows selected.

Execution Plan

----------------------------------------------------------
Plan hash value: 3364546307


---------------------------------------------------------------------------------------------------------------------------
| Id   | Operation                                             | Name                           | Rows  | Bytes | Cost (%CPU)| Time     | Pstart    | Pstop   |
---------------------------------------------------------------------------------------------------------------------------
|   0  | SELECT STATEMENT                              |                                    |     8   |   960 |    12   (0)    | 00:00:01 |            |            |
|   1  |  TABLE ACCESS BY GLOBAL INDEX ROWID| T65_F_S_PGZ10023_M    |     8   |   960 |    12   (0)    | 00:00:01 | ROWID | ROWID |
|*  2 |   INDEX RANGE SCAN                              | I_T65_F_S_PGZ10023_M |     8   |          |     3   (0)    | 00:00:01 |            |            |
---------------------------------------------------------------------------------------------------------------------------


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

   2 - access("OBJKEY"='810000836')

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

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

 

 objkey字段索引比较

Statistics

本地索引(LOCAL

全局索引(GLOBAL

recursive calls (递归调用)

   32

     0 

db block gets  (数据块)

    0

     0

consistent get  (一致性读)

   197

    12 

physical reads  (物理读)

   32

    32 

redo size                         

   0

     0 

bytes sent via SQL*Net to client     

 1182

  1182 

bytes received via SQL*Net from client

  492

   492 

SQL*Net roundtrips to/from client     

    2

     2 

sorts (memory)                     

   0

     0 

sorts (disk)                       

    0

     0 

rows processed                     

    8

     8 

查看段的记录数:

SQL> select Segment_NAME,PARTITION_NAME,SEGMENT_TYPE from user_segments a where  a.segment_name='I_T65_F_S_PGZ10023_M';
 
SEGMENT_NAME                                                                     PARTITION_NAME                 SEGMENT_TYPE
-------------------------------------------------------------------------------- ------------------------------ ------------------
I_T65_F_S_PGZ10023_M                                                                                            INDEX

全局索引的数据段只有一条记录,而本地索引的数据段有12条记录(创建了12个分区定义)。

 

SQL> drop index i_t65_f_s_pgz10023_m;
 
Index dropped
 
SQL> CREATE INDEX I_T65_F_S_PGZ10023_M ON T65_F_S_PGZ10023_M (OBJKEY) TABLESPACE TSDAT01 local;
 
Index created
 
SQL> select Segment_NAME,PARTITION_NAME,SEGMENT_TYPE from user_segments a where  a.segment_name='I_T65_F_S_PGZ10023_M';
 
SEGMENT_NAME                                                                     PARTITION_NAME                 SEGMENT_TYPE
-------------------------------------------------------------------------------- ------------------------------ ------------------
I_T65_F_S_PGZ10023_M                                                             PT_20111231                    INDEX PARTITION
I_T65_F_S_PGZ10023_M                                                             PT_20120100                    INDEX PARTITION
I_T65_F_S_PGZ10023_M                                                             PT_20120101                    INDEX PARTITION
I_T65_F_S_PGZ10023_M                                                             PT_20120102                    INDEX PARTITION
I_T65_F_S_PGZ10023_M                                                             PT_20120103                    INDEX PARTITION
I_T65_F_S_PGZ10023_M                                                             PT_20120104                    INDEX PARTITION
I_T65_F_S_PGZ10023_M                                                             PT_20120105                    INDEX PARTITION
I_T65_F_S_PGZ10023_M                                                             PT_20120106                    INDEX PARTITION
I_T65_F_S_PGZ10023_M                                                             PT_20120107                    INDEX PARTITION
I_T65_F_S_PGZ10023_M                                                             PT_20120108                    INDEX PARTITION
I_T65_F_S_PGZ10023_M                                                             PT_20120109                    INDEX PARTITION
I_T65_F_S_PGZ10023_M                                                             PT_20120111                    INDEX PARTITION
 
12 rows selected
 

看了索引的数据段分布,现在可以理解本地索引的一致性读与物理读为什么那么高,这是因为查找 objkey='810000836'这个值要去每个分区的段中查找,并将结果合并后返回。

 

创建分区字段的索引比较

SQL> CREATE INDEX PI_T65_F_S_PGZ10023_M_DT ON T65_F_S_PGZ10023_M (STATISTIC_DT) TABLESPACE TSDAT01 LOCAL;
SQL> exec dbms_stats.gather_table_stats(ownname => 'AML',tabname => 'T65_F_S_PGZ10023_M');

SQL> select * from T65_F_S_PGZ10023_M where STATISTIC_DT=to_date('2012-01-04','yyyy-mm-dd');

99999 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3412225213


-------------------------------------------------------------------------------------------------------------
| Id   | Operation                         | Name                          | Rows  | Bytes  | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------
|   0  | SELECT STATEMENT          |                                  | 99990 |  3027K|   141   (3)| 00:00:02 |       |       |
|   1  |  PARTITION RANGE SINGLE  |                                  | 99990 |  3027K|   141   (3)| 00:00:02 |     5 |     5 |
|*  2 |   TABLE ACCESS FULL          | T65_F_S_PGZ10023_M | 99990 |  3027K|   141   (3)| 00:00:02 |     5 |     5 |

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


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


   2 - filter("STATISTIC_DT"=TO_DATE(' 2012-01-04 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))


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

Statistics
----------------------------------------------------------
          1         recursive calls
          0        db block gets
       7188      consistent gets
         549     physical reads
          0        redo size
    3186021   bytes sent via SQL*Net to client
      73818    bytes received via SQL*Net from client
       6668     SQL*Net roundtrips to/from client
          0       sorts (memory)
          0       sorts (disk)
      99999   rows processed
 

 

创建全局索引的执行计划

SQL> drop index PI_T65_F_S_PGZ10023_M_DT;

SQL> CREATE INDEX I_T65_F_S_PGZ10023_M_DT ON T65_F_S_PGZ10023_M (STATISTIC_DT) TABLESPACE TSDAT01 global;

SQL> alter system flush buffer_cache;

SQL> select * from T65_F_S_PGZ10023_M where STATISTIC_DT=to_date('2012-01-04','yyyy-mm-dd');

99999 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3412225213


-------------------------------------------------------------------------------------------------------------
| Id   | Operation              | Name               | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------
|   0  | SELECT STATEMENT       |                    | 99991 |  3027K|   141   (3)| 00:00:02 |       |       |
|   1  |  PARTITION RANGE SINGLE|                    | 99991 |  3027K|   141   (3)| 00:00:02 |     5 |     5 |
|*  2 |   TABLE ACCESS FULL    | T65_F_S_PGZ10023_M | 99991 |  3027K|   141   (3)| 00:00:02 |     5 |     5 |
-------------------------------------------------------------------------------------------------------------


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


   2 - filter("STATISTIC_DT"=TO_DATE(' 2012-01-04 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

Statistics
----------------------------------------------------------
          1     recursive calls
          0     db block gets
       7188   consistent gets
        549    physical reads
          0      redo size
    3186021  bytes sent via SQL*Net to client
      73818   bytes received via SQL*Net from client
       6668   SQL*Net roundtrips to/from client
          0     sorts (memory)
          0     sorts (disk)
      99999  rows processed
 

STATISTIC_DT字段索引比较

Statistics

本地索引(LOCAL

全局索引(GLOBAL

recursive calls (递归调用)

      1

        1

db block gets  (数据块)

         0

        0

consistent get  (一致性读)

 7188

   7188

physical reads  (物理读)

     549

      549

redo size                         

         0

        0

bytes sent via SQL*Net to client     

   3186014

  3186014

bytes received via SQL*Net from client

     73818

    73818

SQL*Net roundtrips to/from client     

      6668

     6668

sorts (memory)                     

         0

        0

sorts (disk)                       

         0

        0

rows processed                     

     99999

    99999

相关推荐