本文通过分析不同查询场景下，使用带分区键和不带分区键的索引对查询性能的影响，揭示了在WHERE条件中包含分区键的重要性。实验表明，当查询涉及分区键时，带分区键的索引能显著提高查询效率，特别是在跨分区查询中。此外，带前导分区键的索引在多分区查询中表现更优。因此，建议在创建组合索引时优先考虑包含分区键。
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 hh34: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 hh34: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 hh34: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 hh34: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 条件带分区的情况下，单分区带不带分区键好像意义不大， 跨分区扫描的情况下，带前导分区键的索引效率高。
综合所述，如果需要创建组合索引，建议创建带前导分区键的分区索引。
扫一扫
1244
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
