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

444次阅读

没有评论

共计 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

打开 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