本文通过对比本地索引和全局索引在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如下：
点击(此处)折叠或打开
1. create table T65_F_S_PGZ10023_M
2. (
3. STATISTIC_DT DATE,
4. INDICKEY VARCHAR2(20),
5. OBJKEY VARCHAR2(64),
6. INDICVAL1 NUMBER(30,2),
7. INDICVAL2 NUMBER(30,2),
8. INDICVAL3 NUMBER(30,2),
9. INDICVAL4 NUMBER(30,2),
10. INDICVAL5 NUMBER(30,2)
11. )
12. partition by range (STATISTIC_DT)
13. (
14. partition PT_20111231 values less than (TO_DATE(\'2012-01-01 00:00:00\', \'YYYY-MM-DD HH24:MI:SS\')) tablespace tsdat01,
15. partition PT_20120101 values less than (TO_DATE(\'2012-01-02 00:00:00\', \'YYYY-MM-DD HH24:MI:SS\')) tablespace tsdat01,
16. partition PT_20120102 values less than (TO_DATE(\'2012-01-03 00:00:00\', \'YYYY-MM-DD HH24:MI:SS\')) tablespace tsdat01,
17. partition PT_20120103 values less than (TO_DATE(\'2012-01-04 00:00:00\', \'YYYY-MM-DD HH24:MI:SS\')) tablespace tsdat01,
18. partition PT_20120104 values less than (TO_DATE(\'2012-01-05 00:00:00\', \'YYYY-MM-DD HH24:MI:SS\')) tablespace tsdat01,
19. partition PT_20120105 values less than (TO_DATE(\'2012-01-06 00:00:00\', \'YYYY-MM-DD HH24:MI:SS\')) tablespace tsdat01,
20. partition PT_20120106 values less than (TO_DATE(\'2012-01-07 00:00:00\', \'YYYY-MM-DD HH24:MI:SS\')) tablespace tsdat01,
21. partition PT_20120107 values less than (TO_DATE(\'2012-01-08 00:00:00\', \'YYYY-MM-DD HH24:MI:SS\')) tablespace tsdat01,
22. partition PT_20120108 values less than (TO_DATE(\'2012-01-09 00:00:00\', \'YYYY-MM-DD HH24:MI:SS\')) tablespace tsdat01,
23. partition PT_20120109 values less than (TO_DATE(\'2012-01-10 00:00:00\', \'YYYY-MM-DD HH24:MI:SS\')) tablespace tsdat01,
24. partition PT_20120100 values less than (TO_DATE(\'2012-01-11 00:00:00\', \'YYYY-MM-DD HH24:MI:SS\')) tablespace tsdat01,
25. partition PT_20120111 values less than (TO_DATE(\'2012-01-12 00:00:00\', \'YYYY-MM-DD HH24:MI:SS\')) tablespace tsdat01
26. );
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
扫一扫
3356
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
