oracle_分区表的索引类型以及是否带分区键索引的区别
发表于:2024-10-27 作者:千家信息网编辑
千家信息网最后更新 2024年10月27日,One. 介绍一下分区表的索引类型,以及简述各个类型的适用场景。 Two. 验证一下组合分区索引带不带分区键的区别,用数据来说话。以下说明都是针对分区表的索引介绍。(想着物理存储属性更能了解下面索引的
千家信息网最后更新 2024年10月27日oracle_分区表的索引类型以及是否带分区键索引的区别One. 介绍一下分区表的索引类型,以及简述各个类型的适用场景。 Two. 验证一下组合分区索引带不带分区键的区别,用数据来说话。
以下说明都是针对分区表的索引介绍。(想着物理存储属性更能了解下面索引的说明)
1. 本地索引和全局索引
本地索引 : 索引分区键值等于表的分区键值 本地前缀: 在索引定义中,表的分区键是索引的前导列。 本地非前缀: 在索引定义中, 表的分区键不是索引的前导列。
全局分区索引: 分区索引不是本地的。全局分区索引也可以用于非分区表上。
全局非分区索引: 索引不是分区的。
2. 验证带分区键本地分区索引的区别。
SQL> SELECT * FROM v$version;
BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
SQL> 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 条件带分区的情况下,单分区带不带分区键好像意义不大, 跨分区扫描的情况下,带前导分区键的索引效率高。 综合所述,如果需要创建组合索引,建议创建带前导分区键的分区索引。
3. 测试在非分区表上创建全局分区索引与普通索引区别,看着意义不大,使用场景未明。
CREATE TABLE gpart AS select owner,object_id,object_name,created from DBA_OBJECTS; SELECT distinct TO_char(created,'YYYY-MM-DD') FROM gpart;
exec dbms_stats.gather_table_stats('SUN','GPART',cascade=>true,no_invalidate=>false,method_opt=>'for all columns size 1',estimate_percent=>dbms_stats.auto_sample_size,degree=>24) ;
create index idx_gpart1 ON gpart(created) nologging; DROP INDEX idx_gpart1;
set autotrace traceonly SELECT * FROM gpart t WHERE created > TO_DATE('2015-04-02','YYYY-MM-DD') ;
Execution Plan ---------------------------------------------------------- Plan hash value: 4136711861
------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1005 | 36180 | 13 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| GPART | 1005 | 36180 | 13 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | IDX_GPART1 | 1005 | | 4 (0)| 00:00:01 | ------------------------------------------------------------------------------------------
Predicate Information (identified by operation id): ---------------------------------------------------
2 - access("CREATED">TO_DATE(' 2015-04-02 00:00:00', 'syyyy-mm-dd hh34:mi:ss'))
Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 34 consistent gets 0 physical reads 0 redo size 9616 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) 174 rows processed create index idx_gpart2 on gpart(created) global partition by range (created) (partition GLOBAL1 values less than (TO_DATE('2014-12-15','YYYY-MM-DD')), partition GLOBAL2 values less than (TO_DATE('2015-03-11','YYYY-MM-DD')), partition GLOBAL3 values less than (TO_DATE('2015-03-24','YYYY-MM-DD')), partition GLOBAL4 values less than (TO_DATE('2015-04-01','YYYY-MM-DD')), partition GLOBAL5 values less than (MAXVALUE)) nologging; DROP INDEX idx_gpart2;
set autotrace traceonly SELECT * FROM gpart t WHERE created > TO_DATE('2015-04-02','YYYY-MM-DD') ;
Execution Plan ---------------------------------------------------------- Plan hash value: 4217733073
----------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ----------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1005 | 36180 | 13 (0)| 00:00:01 | | | | 1 | PARTITION RANGE SINGLE | | 1005 | 36180 | 13 (0)| 00:00:01 | 5 | 5 | | 2 | TABLE ACCESS BY INDEX ROWID| GPART | 1005 | 36180 | 13 (0)| 00:00:01 | | | |* 3 | INDEX RANGE SCAN | IDX_GPART2 | 1005 | | 4 (0)| 00:00:01 | 5 | 5 | -----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id): ---------------------------------------------------
3 - access("CREATED">TO_DATE(' 2015-04-02 00:00:00', 'syyyy-mm-dd hh34:mi:ss'))
Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 34 consistent gets 0 physical reads 0 redo size 5769 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) 174 rows processed
以下说明都是针对分区表的索引介绍。(想着物理存储属性更能了解下面索引的说明)
1. 本地索引和全局索引
本地索引 : 索引分区键值等于表的分区键值 本地前缀: 在索引定义中,表的分区键是索引的前导列。 本地非前缀: 在索引定义中, 表的分区键不是索引的前导列。
全局分区索引: 分区索引不是本地的。全局分区索引也可以用于非分区表上。
全局非分区索引: 索引不是分区的。
2. 验证带分区键本地分区索引的区别。
SQL> SELECT * FROM v$version;
BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
SQL> 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 条件带分区的情况下,单分区带不带分区键好像意义不大, 跨分区扫描的情况下,带前导分区键的索引效率高。 综合所述,如果需要创建组合索引,建议创建带前导分区键的分区索引。
3. 测试在非分区表上创建全局分区索引与普通索引区别,看着意义不大,使用场景未明。
CREATE TABLE gpart AS select owner,object_id,object_name,created from DBA_OBJECTS; SELECT distinct TO_char(created,'YYYY-MM-DD') FROM gpart;
exec dbms_stats.gather_table_stats('SUN','GPART',cascade=>true,no_invalidate=>false,method_opt=>'for all columns size 1',estimate_percent=>dbms_stats.auto_sample_size,degree=>24) ;
create index idx_gpart1 ON gpart(created) nologging; DROP INDEX idx_gpart1;
set autotrace traceonly SELECT * FROM gpart t WHERE created > TO_DATE('2015-04-02','YYYY-MM-DD') ;
Execution Plan ---------------------------------------------------------- Plan hash value: 4136711861
------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1005 | 36180 | 13 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| GPART | 1005 | 36180 | 13 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | IDX_GPART1 | 1005 | | 4 (0)| 00:00:01 | ------------------------------------------------------------------------------------------
Predicate Information (identified by operation id): ---------------------------------------------------
2 - access("CREATED">TO_DATE(' 2015-04-02 00:00:00', 'syyyy-mm-dd hh34:mi:ss'))
Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 34 consistent gets 0 physical reads 0 redo size 9616 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) 174 rows processed create index idx_gpart2 on gpart(created) global partition by range (created) (partition GLOBAL1 values less than (TO_DATE('2014-12-15','YYYY-MM-DD')), partition GLOBAL2 values less than (TO_DATE('2015-03-11','YYYY-MM-DD')), partition GLOBAL3 values less than (TO_DATE('2015-03-24','YYYY-MM-DD')), partition GLOBAL4 values less than (TO_DATE('2015-04-01','YYYY-MM-DD')), partition GLOBAL5 values less than (MAXVALUE)) nologging; DROP INDEX idx_gpart2;
set autotrace traceonly SELECT * FROM gpart t WHERE created > TO_DATE('2015-04-02','YYYY-MM-DD') ;
Execution Plan ---------------------------------------------------------- Plan hash value: 4217733073
----------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ----------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1005 | 36180 | 13 (0)| 00:00:01 | | | | 1 | PARTITION RANGE SINGLE | | 1005 | 36180 | 13 (0)| 00:00:01 | 5 | 5 | | 2 | TABLE ACCESS BY INDEX ROWID| GPART | 1005 | 36180 | 13 (0)| 00:00:01 | | | |* 3 | INDEX RANGE SCAN | IDX_GPART2 | 1005 | | 4 (0)| 00:00:01 | 5 | 5 | -----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id): ---------------------------------------------------
3 - access("CREATED">TO_DATE(' 2015-04-02 00:00:00', 'syyyy-mm-dd hh34:mi:ss'))
Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 34 consistent gets 0 physical reads 0 redo size 5769 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) 174 rows processed
索引
条件
查询
中加
情况
相同
只是
前导
全局
加分
分区表
意义
类型
不大
前缀
场景
物理
组合
验证
普通
数据库的安全要保护哪些东西
数据库安全各自的含义是什么
生产安全数据库录入
数据库的安全性及管理
数据库安全策略包含哪些
海淀数据库安全审计系统
建立农村房屋安全信息数据库
易用的数据库客户端支持安全管理
连接数据库失败ssl安全错误
数据库的锁怎样保障安全
数据库适合女生学吗
校园网络安全知识讲座策划书
两个服务器ssh数据拷贝
数据库eer
数据库如何建立薪水等级表
粘土服务器如何退出队伍
软件开发要学web基础
银行业面临的主要网络安全
关系数据库的概述
江苏个人软件开发电话多少
网络安全黄埔军校
无人机地面软件开发
网心服务器拨号管理口
sql数据库显示质疑
綦江软件开发哪家好
佛山优尚品品网络技术
中公优就业软件开发专业怎么样
数据库的5个安全等级
广电网络技术构成
点创网络技术郑州
后端从数据库取数返回给前端
web属于客户服务器系统吗
a股科技互联网股票
蒂森克虏伯电梯服务器
珠海软件开发定制app小程序
网络安全教育班主任总结
dede数据库字段
dos窗口怎么打卡数据库
同城配送系统数据库
关于网络安全的诗歌50字