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