共计 13029 个字符，预计需要花费 33 分钟才能阅读完成。
分区索引分为本地索引和全局索引, 但对于在分区表上建索引, 一般用的比较多的还是普通索引和本地分区索引, 而全局分区索引相对用的比较少.
以下测试为验证: 分区表上 的本地分区索引 因为查询条件引起跨分区, 是否改为普通索引更合适.
以下测试:
Oracle version:11.2.0.4
建测试表:
drop table SCOTT.TB_TEST01;
create table SCOTT.TB_TEST01
partition by range (CREATED)
(
partition P_2015 values less than (TO_DATE(‘ 2016-01-01 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’)),
partition P_2016 values less than (TO_DATE(‘ 2017-01-01 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’)),
partition P_MAX values less than (MAXVALUE)
)
as
select * from dba_objects;
## 插入测试数据
insert into SCOTT.TB_TEST01
select * from SCOTT.TB_TEST01;
commit;
insert into SCOTT.TB_TEST01
select * from SCOTT.TB_TEST01;
commit;
insert into SCOTT.TB_TEST01
select * from SCOTT.TB_TEST01;
commit;
insert into SCOTT.TB_TEST01
select * from SCOTT.TB_TEST01;
commit;
# 修改测试数据使其均匀分布
alter table SCOTT.TB_TEST01 enable row movement;
update SCOTT.TB_TEST01
set created=to_date(‘20150101′,’yyyymmdd’)+dbms_random.value(1,1000);
commit;
# 收集一下统计信息
begin
dbms_stats.gather_table_stats(ownname=>’scott’,tabname => ‘TB_TEST01’, cascade=> TRUE);
end;
情况 1:过滤条件在全分区
查询语句:
select * from SCOTT.TB_TEST01 where owner=’SCOTT’ and object_type=’TABLE’;
# 建普通索引
create index scott.idx_TB_TEST01 on SCOTT.TB_TEST01(owner,object_type);
# 执行计划
Execution Plan
———————————————————-
Plan hash value: 2208186213
——————————————————————————–
————————————
| Id | Operation | Name | Rows | Bytes | Cos
t (%CPU)| Time | Pstart| Pstop |
——————————————————————————–
————————————
| 0 | SELECT STATEMENT | | 176 | 36432 |
19 (0)| 00:00:01 | | |
| 1 | TABLE ACCESS BY GLOBAL INDEX ROWID| TB_TEST01 | 176 | 36432 |
19 (0)| 00:00:01 | ROWID | ROWID |
|* 2 | INDEX RANGE SCAN | IDX_TB_TEST01 | 176 | |
3 (0)| 00:00:01 | | |
——————————————————————————–
————————————
Predicate Information (identified by operation id):
—————————————————
2 – access(“OWNER”=’SCOTT’ AND “OBJECT_TYPE”=’TABLE’)
# 统计信息
Statistics
———————————————————-
0 recursive calls
0 db block gets
141 consistent gets
0 physical reads
0 redo size
18793 bytes sent via SQL*Net to client
644 bytes received via SQL*Net from client
13 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
176 rows processed
# 上面的测试 cost=19,consistent gets=141
# 建 本地分区索引
drop index scott.idx_TB_TEST01;
create index scott.idx_TB_TEST01 on SCOTT.TB_TEST01(owner,object_type) local;
SQL> /
# 执行计划
Execution Plan
———————————————————-
Plan hash value: 2644430318
——————————————————————————–
————————————
| Id | Operation | Name | Rows | Bytes | Cos
t (%CPU)| Time | Pstart| Pstop |
——————————————————————————–
————————————
| 0 | SELECT STATEMENT | | 176 | 36432 |
23 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE ALL | | 176 | 36432 |
23 (0)| 00:00:01 | 1 | 3 |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| TB_TEST01 | 176 | 36432 |
23 (0)| 00:00:01 | 1 | 3 |
|* 3 | INDEX RANGE SCAN | IDX_TB_TEST01 | 176 | |
7 (0)| 00:00:01 | 1 | 3 |
——————————————————————————–
————————————
Predicate Information (identified by operation id):
—————————————————
3 – access(“OWNER”=’SCOTT’ AND “OBJECT_TYPE”=’TABLE’)
# 统计信息
Statistics
———————————————————-
0 recursive calls
0 db block gets
147 consistent gets
0 physical reads
0 redo size
11678 bytes sent via SQL*Net to client
644 bytes received via SQL*Net from client
13 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
176 rows processed
# 上面的测试 cost=23,consistent gets=147
小结:
1. 从上面测试可以看出, 本地分区索引成本大于普通索引. 因为此时用本地分区索引是需要 PARTITION RANGE ALL 的.
2. 查询语句中的两个过滤条件都是等值的, 在建这种复合索引时, 索引列的顺序跟成本没有关系. 这里没有测试，有兴趣可以测试。
情况 2: 过滤条件在单分区
查询语句:
select * from SCOTT.TB_TEST01
where owner=’SCOTT’ and object_type=’TABLE’ and created between to_date(‘20161230′,’yyyymmdd’) and to_date(‘20160105′,’yyyymmdd’);
# 建一个差的索引
drop index scott.idx_TB_TEST01;
create index scott.idx_TB_TEST01 on SCOTT.TB_TEST01(created,owner,object_type);
SQL> select * from SCOTT.TB_TEST01
where owner=’SCOTT’ and object_type=’TABLE’ and created between to_date(‘20161230′,’yyyymmdd’) and to_date(‘20160105′,’yyyymmdd’);
# 执行计划
Execution Plan
———————————————————-
Plan hash value: 783220382
——————————————————————————–
————————————
| Id | Operation | Name | Rows | Bytes | Cos
t (%CPU)| Time | Pstart| Pstop |
——————————————————————————–
————————————
| 0 | SELECT STATEMENT | | 1 | 207 | 2
34 (0)| 00:00:03 | | |
| 1 | TABLE ACCESS BY GLOBAL INDEX ROWID| TB_TEST01 | 1 | 207 | 2
34 (0)| 00:00:03 | 2 | 2 |
|* 2 | INDEX RANGE SCAN | IDX_TB_TEST01 | 1 | | 2
33 (0)| 00:00:03 | | |
——————————————————————————–
————————————
Predicate Information (identified by operation id):
—————————————————
2 – access(“CREATED”>=TO_DATE(‘ 2016-06-11 00:00:00’, ‘syyyy-mm-dd hh24:mi:ss
‘) AND “OWNER”=’SCOTT’ AND
“OBJECT_TYPE”=’TABLE’ AND “CREATED”<=TO_DATE(‘ 2016-07-11 00:00:00
‘, ‘syyyy-mm-dd hh24:mi:ss’))
filter(“OWNER”=’SCOTT’ AND “OBJECT_TYPE”=’TABLE’)
# 统计信息
Statistics
———————————————————-
0 recursive calls
0 db block gets
226 consistent gets
0 physical reads
0 redo size
1932 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
4 rows processed
# 上面的测试 cost=234,consistent gets=226
# 建普通索引
drop index scott.idx_TB_TEST01;
create index scott.idx_TB_TEST01 on SCOTT.TB_TEST01(owner,object_type,created);
SQL> /
# 执行计划
Execution Plan
———————————————————-
Plan hash value: 783220382
——————————————————————————–
————————————
| Id | Operation | Name | Rows | Bytes | Cos
t (%CPU)| Time | Pstart| Pstop |
——————————————————————————–
————————————
| 0 | SELECT STATEMENT | | 4 | 828 |
14 (0)| 00:00:01 | | |
| 1 | TABLE ACCESS BY GLOBAL INDEX ROWID| TB_TEST01 | 4 | 828 |
14 (0)| 00:00:01 | 2 | 2 |
|* 2 | INDEX RANGE SCAN | IDX_TB_TEST01 | 4 | |
3 (0)| 00:00:01 | | |
——————————————————————————–
————————————
Predicate Information (identified by operation id):
—————————————————
2 – access(“OWNER”=’SCOTT’ AND “OBJECT_TYPE”=’TABLE’ AND “CREATED”>=TO_DATE(‘
2016-06-11 00:00:00′,
‘syyyy-mm-dd hh24:mi:ss’) AND “CREATED”<=TO_DATE(‘ 2016-07-11 00:0
0:00′, ‘syyyy-mm-dd hh24:mi:ss’))
# 统计信息
Statistics
———————————————————-
0 recursive calls
0 db block gets
9 consistent gets
0 physical reads
0 redo size
1932 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
4 rows processed
# 上面的测试 cost=14,consistent gets=9
# 建本地分区索引
drop index scott.idx_TB_TEST01;
create index scott.idx_TB_TEST01 on SCOTT.TB_TEST01(owner,object_type,created) local ;
SQL> /
# 执行计划
Execution Plan
———————————————————-
Plan hash value: 2387350646
——————————————————————————–
————————————
| Id | Operation | Name | Rows | Bytes | Cos
t (%CPU)| Time | Pstart| Pstop |
——————————————————————————–
————————————
| 0 | SELECT STATEMENT | | 4 | 828 |
11 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE SINGLE | | 4 | 828 |
11 (0)| 00:00:01 | 2 | 2 |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| TB_TEST01 | 4 | 828 |
11 (0)| 00:00:01 | 2 | 2 |
|* 3 | INDEX RANGE SCAN | IDX_TB_TEST01 | 4 | |
7 (0)| 00:00:01 | 2 | 2 |
——————————————————————————–
————————————
Predicate Information (identified by operation id):
—————————————————
3 – access(“OWNER”=’SCOTT’ AND “OBJECT_TYPE”=’TABLE’ AND “CREATED”>=TO_DATE(‘
2016-06-11 00:00:00′,
‘syyyy-mm-dd hh24:mi:ss’) AND “CREATED”<=TO_DATE(‘ 2016-07-11 00:0
0:00′, ‘syyyy-mm-dd hh24:mi:ss’))
# 统计信息
Statistics
———————————————————-
0 recursive calls
0 db block gets
8 consistent gets
0 physical reads
0 redo size
1872 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
4 rows processed
# 上面的测试 cost=11,consistent gets=8
小结:
1. 建的第一个差索引, 之所以差, 是因为 created 字段是一个区间值, 此时 access 只能对应 created 字段, 而 owner 字段和 object_type 都是通过 filter 过滤的. 所以过滤条件是区间和要放后面.
2. 从上面测试可以看出, 本地分区索引成本小于普通索引. 此时本地分区索引使用 PARTITION RANGE SINGLE.
情况 3: 过滤条件在跨分区
查询语句:
select * from SCOTT.TB_TEST01
where owner=’SCOTT’ and object_type=’TABLE’ and created between to_date(‘20161220′,’yyyymmdd’) and to_date(‘20170205′,’yyyymmdd’);
# 建普通索引
drop index scott.idx_TB_TEST01;
create index scott.idx_TB_TEST01 on SCOTT.TB_TEST01(owner,object_type,created);
SQL> select * from SCOTT.TB_TEST01
where owner=’SCOTT’ and object_type=’TABLE’ and created between to_date(‘20161220′,’yyyymmdd’) and to_date(‘20170205′,’yyyymmdd’);
# 执行计划
Execution Plan
———————————————————-
Plan hash value: 2208186213
——————————————————————————–
————————————
| Id | Operation | Name | Rows | Bytes | Cos
t (%CPU)| Time | Pstart| Pstop |
——————————————————————————–
————————————
| 0 | SELECT STATEMENT | | 1 | 98 |
5 (0)| 00:00:01 | | |
| 1 | TABLE ACCESS BY GLOBAL INDEX ROWID| TB_TEST01 | 1 | 98 |
5 (0)| 00:00:01 | ROWID | ROWID |
|* 2 | INDEX RANGE SCAN | IDX_TB_TEST01 | 1 | |
3 (0)| 00:00:01 | | |
——————————————————————————–
————————————
Predicate Information (identified by operation id):
—————————————————
2 – access(“OWNER”=’SCOTT’ AND “OBJECT_TYPE”=’TABLE’ AND “CREATED”>=TO_DATE(‘
2016-12-20 00:00:00′,
‘syyyy-mm-dd hh24:mi:ss’) AND “CREATED”<=TO_DATE(‘ 2017-02-05 00:0
0:00′, ‘syyyy-mm-dd hh24:mi:ss’))
# 统计信息
Statistics
———————————————————-
0 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
1836 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3 rows processed
# 上面的测试 cost=5,consistent gets=7
# 建本地分区索引
drop index scott.idx_TB_TEST01;
create index scott.idx_TB_TEST01 on SCOTT.TB_TEST01(owner,object_type,created) local ;
SQL> /
# 执行计划
Execution Plan
———————————————————-
Plan hash value: 353906606
——————————————————————————–
————————————
| Id | Operation | Name | Rows | Bytes | Cos
t (%CPU)| Time | Pstart| Pstop |
——————————————————————————–
————————————
| 0 | SELECT STATEMENT | | 1 | 98 |
7 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE ITERATOR | | 1 | 98 |
7 (0)| 00:00:01 | 2 | 3 |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| TB_TEST01 | 1 | 98 |
7 (0)| 00:00:01 | 2 | 3 |
|* 3 | INDEX RANGE SCAN | IDX_TB_TEST01 | 1 | |
5 (0)| 00:00:01 | 2 | 3 |
——————————————————————————–
————————————
Predicate Information (identified by operation id):
—————————————————
3 – access(“OWNER”=’SCOTT’ AND “OBJECT_TYPE”=’TABLE’ AND “CREATED”>=TO_DATE(‘
2016-12-20 00:00:00′,
‘syyyy-mm-dd hh24:mi:ss’) AND “CREATED”<=TO_DATE(‘ 2017-02-05 00:0
0:00′, ‘syyyy-mm-dd hh24:mi:ss’))
# 统计信息
Statistics
———————————————————-
0 recursive calls
0 db block gets
10 consistent gets
0 physical reads
0 redo size
1803 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3 rows processed
# 上面的测试 cost=7,consistent gets=10
小结:
1. 从上面测试可以看出, 本地分区索引成本大于普通索引. 因为此时用本地分区索引是需要 PARTITION RANGE ITERATOR 的.
总结:
1. 在选择普通索引和本地分区索引时, 分区的跨度影响索引的选择, 但相对于其它因素, 这个影响还是很小的. 如果考虑到将来分区归档的维护, 本地分区索引还是有优势的. 最重要的是索引列的选择, 索引列顺序的选择才是最重要的.
2. 查询语句中的两个过滤条件都是等值的, 在建这种复合索引时, 索引列的顺序跟成本没有关系. 这里没有测试，有兴趣可以测试。
3. 查询语句中的过滤条件是区间的, 要放在等值的后面.
更多 Oracle 相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx?tid=12
本文永久更新链接地址：http://www.linuxidc.com/Linux/2016-08/133901.htm
