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 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.查詢語句中的過濾條件是區間的,要放在等值的後面.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28539951/viewspace-2122881/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 分割槽表的不同操作對索引的影響索引
- Oracle分割槽表及分割槽索引Oracle索引
- 在範圍分割槽表上分割槽維護操作對索引狀態的影響索引
- 在範圍分割槽表上分割槽維護操作對全域性及本地索引狀態的影響(2)索引
- 操作分割槽表對global和local索引的影響索引
- oracle分割槽表的常規操作導致對索引的影響Oracle索引
- 在範圍分割槽表上分割槽維護操作對索引狀態的影響(1)索引
- 簡單ORACLE分割槽表、分割槽索引Oracle索引
- Oracle索引分割槽Oracle索引
- Oracle帶區域性分割槽索引的分割槽表刪除舊分割槽新增新分割槽Oracle索引
- oracle 針對普通表的索引分割槽及10g新增hash 索引分割槽Oracle索引
- Oracle分割槽之五：建立分割槽索引總結Oracle索引
- 深入學習Oracle分割槽表及分割槽索引Oracle索引
- 全面認識oracle分割槽表及分割槽索引Oracle索引
- oracle分割槽partition及分割槽索引partition index(一)Oracle索引Index
- oracle分割槽索引（二）Oracle索引
- oracle分割槽索引（一）Oracle索引
- rebuild分割槽表分割槽索引的方法Rebuild索引
- 【學習筆記】分割槽表和分割槽索引——管理索引分割槽（四）筆記索引
- oracle索引詳解 分割槽索引Oracle索引
- 全面學習分割槽表及分割槽索引(10)--交換分割槽索引
- 分割槽表及分割槽索引建立示例索引
- 全面學習分割槽表及分割槽索引(17)--其它索引分割槽管理操作索引
- oracle 建立所有分割槽索引Oracle索引
- [轉]Oracle分割槽索引--本地索引和全域性索引比較Oracle索引
- Oracle分割槽索引--本地索引和全域性索引比較（轉）Oracle索引
- Oracle的分割槽索引技術Oracle索引
- Oracle分割槽表基礎運維-06分割槽表索引Oracle運維索引
- 有關Oracle表分割槽進行(DML)維護後對索引的影響的分析Oracle索引
- 全面學習分割槽表及分割槽索引(16)--增加和刪除索引分割槽索引
- 全面學習分割槽表及分割槽索引(13)--分隔表分割槽索引
- 全域性分割槽索引和區域性分割槽索引索引
- 分割槽表分割槽索引查詢效率探究索引
- 分割槽表的分割槽資料刪除、truncate會對全域性和區域性索引產生什麼影響索引
- 全面學習分割槽表及分割槽索引(9)--刪除表分割槽索引
- 全面學習分割槽表及分割槽索引(11)--合併表分割槽索引
- 全面學習分割槽表及分割槽索引(12)--修改list表分割槽索引
- 學習筆記】分割槽表和分割槽索引——新增表分割槽（二）筆記索引