create table parttest(
owner varchar2(20) not
null ,
object_id number not null
,
object_name varchar2(32) ,
created date
) partition by list(owner)
(
partition part1 values ('SYS') ,
partition part2 values ('OUTLN') ,
partition part3 values ('SYSTEM') ,
partition part4 values ('SUN') ,
partition part5 values ('SQLTXPLAIN')
,
partition part6 values ('APPQOSSYS')
,
partition part7 values ('DBSNMP') ,
partition part8 values ('SQLTXADMIN')
,
partition part9 values ('DIP'),
partition part10 values
('ORACLE_OCM'),
partition part11 values
(default)
)
/
DROP TABLE parttest;
insert into parttest select
owner,object_id,object_name,created from DBA_OBJECTS;
commit;
--索引不包含分区键
create index idx_nopartkey on
parttest(created) local nologging;
-- 索引包含分区键
create index idx_partkey on
parttest(created,owner) local nologging;
create index idx_partkey2 on
parttest(object_NAME,owner) local nologging;
create index idx_partkey3 on
parttest(owner,object_NAME) local nologging;
create index idx_nopartkey2 on
parttest(object_NAME) local nologging;
--收集统计信息
SQL> exec
dbms_stats.gather_table_stats('SUN','PARTTEST',cascade=>true,no_invalidate=>false,method_opt=>'for
all columns size
1',estimate_percent=>dbms_stats.auto_sample_size,degree=>24)
;
PL/SQL procedure successfully completed.
分析过程分如下几个方面
1.用带分区键值的索引进行查询,但在where条件中不加分区条件
2.用带分区键值的索引进行查询,但在where条件中加分区条件
3.用不带分区键值的索引进行查询,但在where条件中不加分区条件
4.用不带分区键值的索引进行查询,但在where条件中加分区条
5.用带分区键值的索引进行查询,但在where条件中加分区条(与4的索引键相同,只是带索引键值)
6.用带分区键值的索引进行查询,但在where条件中加分区条(与4的索引键相同,只是带前导索引键值)
7.用带分区键值的索引进行跨分区查询,但在where条件中加分区条件(与4的索引键相同,只是带索引前导键值)
8.用带分区键值的索引进行跨分区查询,但在where条件中加分区条(与4的索引键相同,只是带后导索引键值)
9.用带分区键值的索引进行跨分区查询,但在where条件中加分区条件(与4的索引键相同,只是带索引键值)
第一种情况:用带分区键值的索引进行查询,但是where条件中不加分区条件
set autotrace traceonly
SELECT object_name FROM parttest WHERE object_name LIKE
'OR%';
Execution Plan
----------------------------------------------------------
Plan hash value: 3693814982
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows |
Bytes | Cost (%CPU)| Time |
Pstart| Pstop |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT |
| 3 |
57 | 12 (0)| 00:00:01 |
|
|
| 1 | PARTITION LIST ALL|
| 3 |
57 | 12 (0)| 00:00:01 |
1 | 11 |
|* 2 | INDEX RANGE SCAN |
IDX_PARTKEY2 | 3 |
57 | 12 (0)| 00:00:01 |
1 | 11 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_NAME"
LIKE 'OR%')
filter("OBJECT_NAME" LIKE 'OR%')
Statistics
----------------------------------------------------------
1 recursive
calls
0 db block
gets
23
consistent gets
0 physical
reads
0 redo
size
3768 bytes sent via SQL*Net to
client
589 bytes received via SQL*Net
from client
8 SQL*Net
roundtrips to/from client
0 sorts
(memory)
0 sorts
(disk)
105 rows processed
第二种情况:用带分区键值的索引进行查询,但是where条件中加分区条件
set autotrace traceonly
SELECT object_name FROM parttest WHERE object_name LIKE 'OR%'
AND owner='SYS';
Execution Plan
----------------------------------------------------------
Plan hash value: 2753556796
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows
| Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |
2 | 46 | 2
(0)| 00:00:01 | | |
| 1 | PARTITION LIST
SINGLE| | 2 |
46 | 2 (0)| 00:00:01 |
KEY | KEY |
|* 2 | INDEX RANGE SCAN
| IDX_PARTKEY2 |
2 | 46 | 2
(0)| 00:00:01 | 1 | 1
|
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_NAME"
LIKE 'OR%' AND "OWNER"='SYS')
filter("OBJECT_NAME" LIKE 'OR%')
Statistics
----------------------------------------------------------
1 recursive
calls
0 db block
gets
6
consistent gets
0 physical
reads
0 redo
size
2279 bytes sent via SQL*Net to
client
556 bytes received via SQL*Net
from client
5 SQL*Net
roundtrips to/from client
0 sorts
(memory)
0 sorts
(disk)
58 rows
processed
第三种情况:用不带分区键值的索引进行查询,但是where条件中不加分区条件
set autotrace traceonly
SELECT object_name FROM parttest WHERE
created=to_date('2014-12-15 22:29:22','YYYY-MM-DD
HH24:MI:SS');
Execution Plan
----------------------------------------------------------
Plan hash value: 646636157
--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name
| Rows |
Bytes | Cost (%CPU)| Time |
Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |
35 | 945 |
13 (0)|
00:00:01 | | |
| 1 | PARTITION LIST ALL
| |
35 | 945 |
13 (0)|
00:00:01 | 1 |
11 |
| 2 | TABLE ACCESS BY
LOCAL INDEX ROWID| PARTTEST | 35 |
945 | 13
(0)| 00:00:01 | 1 | 11
|
|* 3 | INDEX RANGE SCAN | IDX_NOPARTKEY | 35 | | 12
(0)| 00:00:01 | 1 | 11
|
--------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 -
access("CREATED"=TO_DATE(' 2014-12-15 22:29:22', 'syyyy-mm-dd
hh24:mi:ss'))
Statistics
----------------------------------------------------------
1 recursive
calls
0 db block
gets
24
consistent gets
0 physical
reads
0 redo
size
1780 bytes sent via SQL*Net to
client
545 bytes received via SQL*Net
from client
4 SQL*Net
roundtrips to/from client
0 sorts
(memory)
0 sorts
(disk)
41 rows
processed
第四种情况:用不带分区键值的索引进行查询,但是where条件中加分区条件
set autotrace traceonly
SELECT object_name FROM parttest a WHERE
created=to_date('2014-12-15 22:29:22','YYYY-MM-DD
HH24:MI:SS') AND owner='SYS';
Execution Plan
----------------------------------------------------------
Plan hash value: 3242664717
--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name
| Rows |
Bytes | Cost (%CPU)| Time |
Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |
28 | 868 |
2 (0)|
00:00:01 | | |
| 1 | PARTITION LIST
SINGLE |
| 28 |
868 | 2
(0)| 00:00:01 | KEY |
KEY |
| 2 | TABLE ACCESS BY
LOCAL INDEX ROWID| PARTTEST | 28 |
868 | 2
(0)| 00:00:01 | 1 | 1
|
|* 3 | INDEX RANGE SCAN | IDX_NOPARTKEY | 28 | | 1
(0)| 00:00:01 | 1 | 1
|
--------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 -
access("CREATED"=TO_DATE(' 2014-12-15 22:29:22', 'syyyy-mm-dd
hh24:mi:ss'))
Statistics
----------------------------------------------------------
0 recursive
calls
0 db block
gets
7
consistent gets
0 physical
reads
0 redo
size
1191 bytes sent via SQL*Net to
client
534 bytes received via SQL*Net
from client
3 SQL*Net
roundtrips to/from client
0 sorts
(memory)
0 sorts
(disk)
21 rows
processed
第五种情况:用带分区键值的索引进行查询,但在where条件中加分区条(与4的索引键相同,只是带索引键值)
SELECT object_name FROM parttest a WHERE
created=to_date('2014-12-15 22:29:22','YYYY-MM-DD
HH24:MI:SS') AND owner='SYS';
Execution Plan
----------------------------------------------------------
Plan hash value: 1150146376
------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name
| Rows | Bytes | Cost (%CPU)|
Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 28 | 868 | 2 (0)| 00:00:01 |
|
|
| 1 | PARTITION LIST
SINGLE |
|
28 | 868 |
2 (0)|
00:00:01 | KEY | KEY |
| 2 | TABLE ACCESS BY
LOCAL INDEX ROWID| PARTTEST |
28 | 868 |
2 (0)|
00:00:01 | 1 |
1 |
|* 3 | INDEX RANGE SCAN | IDX_PARTKEY | 17 | | 1
(0)| 00:00:01 | 1 | 1
|
------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 -
access("CREATED"=TO_DATE(' 2014-12-15 22:29:22', 'syyyy-mm-dd
hh24:mi:ss') AND "OWNER"='SYS')
Statistics
----------------------------------------------------------
0 recursive
calls
0 db block
gets
7
consistent gets
0 physical
reads
0 redo
size
1191 bytes sent via SQL*Net to
client
534 bytes received via SQL*Net
from client
3 SQL*Net
roundtrips to/from client
0 sorts
(memory)
0 sorts
(disk)
21 rows
processed
第六种情况:用带分区键值的索引进行查询,但在where条件中加分区条(与4的索引键相同,只是带前导索引键值)
set autotrace traceonly
SELECT object_name FROM parttest a WHERE
created=to_date('2014-12-15 22:29:22','YYYY-MM-DD
HH24:MI:SS') AND owner='SYS';
Execution Plan
----------------------------------------------------------
Plan hash value: 1150146376
------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name
| Rows | Bytes | Cost (%CPU)|
Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 28 | 868 | 2 (0)| 00:00:01 |
|
|
| 1 | PARTITION LIST
SINGLE |
|
28 | 868 |
2 (0)|
00:00:01 | KEY | KEY |
| 2 | TABLE ACCESS BY
LOCAL INDEX ROWID| PARTTEST |
28 | 868 |
2 (0)|
00:00:01 | 1 |
1 |
|* 3 | INDEX RANGE SCAN | IDX_PARTKEY | 17 | | 1
(0)| 00:00:01 | 1 | 1
|
------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 -
access("CREATED"=TO_DATE(' 2014-12-15 22:29:22', 'syyyy-mm-dd
hh24:mi:ss') AND "OWNER"='SYS')
Statistics
----------------------------------------------------------
0 recursive
calls
0 db block
gets
7
consistent gets
0 physical
reads
0 redo
size
1191 bytes sent via SQL*Net to
client
534 bytes received via SQL*Net
from client
3 SQL*Net
roundtrips to/from client
0 sorts
(memory)
0 sorts
(disk)
21 rows
processed
第七种情况:用带分区键值的索引进行跨分区查询,但在where条件中加分区条件(与4的索引键相同,只是带索引前导键值)
set autotrace traceonly
SELECT object_name FROM parttest a WHERE
object_name LIKE 'OR%' AND owner IN
('SYS','SUN');
Execution Plan
----------------------------------------------------------
Plan hash value: 1341146800
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows |
Bytes | Cost (%CPU)| Time |
Pstart| Pstop |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT |
| 1 |
25 | 3 (0)| 00:00:01 |
|
|
| 1 | INLIST ITERATOR
| | | | | | | |
| 2 | PARTITION LIST
ITERATOR| | 1 |
25 | 3 (0)| 00:00:01 |KEY(I)
|KEY(I) |
|* 3 | INDEX RANGE SCAN | IDX_PARTKEY3 |
1 | 25 | 3
(0)| 00:00:01 |KEY(I) |KEY(I) |
---------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access(("OWNER"='SUN'
OR "OWNER"='SYS') AND "OBJECT_NAME" LIKE 'OR%')
filter("OBJECT_NAME" LIKE 'OR%')
Statistics
----------------------------------------------------------
1 recursive
calls
0 db block
gets
8
consistent gets
1 physical
reads
0 redo
size
2540 bytes sent via SQL*Net to
client
567 bytes received via SQL*Net
from client
6 SQL*Net
roundtrips to/from client
0 sorts
(memory)
0 sorts
(disk)
62 rows
processed
第八种情况:用带分区键值的索引进行跨分区查询,但在where条件中加分区条(与4的索引键相同,只是带后导索引键值)
set autotrace traceonly
SELECT object_name FROM parttest a WHERE
object_name LIKE 'OR%' AND owner IN
('SYS','SUN');
Execution Plan
----------------------------------------------------------
Plan hash value: 2095150599
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows
| Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |
1 | 25 | 3
(0)| 00:00:01 | | |
| 1 | PARTITION LIST
INLIST| | 1 |
25 | 3 (0)| 00:00:01 |KEY(I)
|KEY(I) |
|* 2 | INDEX RANGE SCAN
| IDX_PARTKEY2 |
1 | 25 | 3
(0)| 00:00:01 |KEY(I) |KEY(I) |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_NAME"
LIKE 'OR%')
filter("OBJECT_NAME" LIKE 'OR%')
Statistics
----------------------------------------------------------
209 recursive calls
2 db block
gets
180 consistent gets
0 physical
reads
0 redo
size
2497 bytes sent via SQL*Net to
client
567 bytes received via SQL*Net
from client
6 SQL*Net
roundtrips to/from client
13 sorts
(memory)
0 sorts
(disk)
62 rows
processed
第九种情况:用带分区键值的索引进行跨分区查询,但在where条件中加分区条件(与4的索引键相同,只是带索引键值)
set autotrace traceonly
SELECT object_name FROM parttest a WHERE
object_name LIKE 'OR%' AND owner IN
('SYS','SUN');
Execution Plan
----------------------------------------------------------
Plan hash value: 2097624711
---------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name
| Rows |
Bytes | Cost (%CPU)| Time |
Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 |
25 | 5 (0)| 00:00:01 |
|
|
| 1 | PARTITION LIST
INLIST |
| 1 | 25 |
5 (0)|
00:00:01 |KEY(I) |KEY(I) |
| 2 | TABLE ACCESS BY
LOCAL INDEX ROWID| PARTTEST | 1 |
25 | 5 (0)| 00:00:01 |KEY(I)
|KEY(I) |
|* 3 | INDEX RANGE SCAN | IDX_NOPARTKEY2 | 3 | | 3
(0)| 00:00:01 |KEY(I) |KEY(I) |
---------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("OBJECT_NAME"
LIKE 'OR%')
filter("OBJECT_NAME" LIKE 'OR%')
Statistics
----------------------------------------------------------
1 recursive
calls
0 db block
gets
27
consistent gets
1 physical
reads
0 redo
size
2497 bytes sent via SQL*Net to
client
567 bytes received via SQL*Net
from client
6 SQL*Net
roundtrips to/from client
0 sorts
(memory)
0 sorts
(disk)
62 rows
processed
总结:
1.在使用分区表示,WHERE
条件最好带上分区键,要不然就失去了分区的意义,一个分区在物理上是一个表,
全分区表扫描比全非分区表扫描要更多的IO读。
2.WHERE
条件带分区的情况下,单分区带不带分区键好像意义不大, 跨分区扫描的情况下,带前导分区键的索引效率高。
综合所述,如果需要创建组合索引,建议创建带前导分区键的分区索引。