Tel：028-86922220 我有需求

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

当前文章：oracle_分区表的索引类型以及是否带分区键索引的区别
本文来源：http://bzwzjz.com/article/jpogpo.html

其他资讯

Copyright © 2007-2020 广东宝晨空调科技有限公司 All Rights Reserved 粤ICP备2022107769号
友情链接： 网站建设 企业网站建设 成都h5网站建设 古蔺网站建设 响应式网站设计 企业网站设计 成都网站建设公司 成都网站制作 成都网站设计制作公司 定制级高端网站建设 网站建设方案 网站设计制作 企业网站制作 成都网站建设 四川成都网站建设 成都网站制作公司 成都网站建设 重庆网站设计 H5网站制作 成都网站设计 成都网站设计 成都网站设计