Oracle本地分区索引跨分区对成本的影响

分区索引分为本地索引和全局索引,但对于在分区表上建索引,一般用的比较多的还是普通索引和本地分区索引,而全局分区索引相对用的比较少.
以下测试为验证:分区表上的本地分区索引因为查询条件引起跨分区,是否改为普通索引更合适.

以下测试:
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本文永久更新链接地址