阿里云-云小站(无限量代金券发放中)
【腾讯云】云服务器、云数据库、COS、CDN、短信等热卖云产品特惠抢购

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

204次阅读
没有评论

共计 12437 个字符,预计需要花费 32 分钟才能阅读完成。

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

本文永久更新链接地址:http://www.linuxidc.com/Linux/2017-10/147214.htm

正文完
星哥说事-微信公众号
post-qrcode
 0
星锅
版权声明:本站原创文章,由 星锅 于2022-01-22发表,共计12437字。
转载说明:除特殊说明外本站文章皆由CC-4.0协议发布,转载请注明出处。
【腾讯云】推广者专属福利,新客户无门槛领取总价值高达2860元代金券,每种代金券限量500张,先到先得。
阿里云-最新活动爆款每日限量供应
评论(没有评论)
验证码
【腾讯云】云服务器、云数据库、COS、CDN、短信等云产品特惠热卖中