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

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

3、 使用AUTOTRACE查看执行计划

相关推荐

3、使用AUTOTRACE查看执行计划