资讯

精准传达 • 有效沟通

从品牌网站建设到网络营销策划，从策略到执行的一站式服务

网站建设 >

查看其它板块

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_分区表的索引类型以及是否带分区键索引的区别
本文URL：http://cdkjz.cn/article/jpogpo.html
返回首页 了解更多建站资讯

相关资讯

多年建站经验

多一份参考，总有益处

联系快上网，免费获得专属《策划方案》及报价

咨询相关问题或预约面谈，可以通过以下方式与我们联系

大客户专线   成都：13518219792   座机：028-86922220

在线咨询 提交需求
友情链接 交换友情链接
珉田数据中心成都网站设计普特斯腐植酸简阳做网站营山产后修复广安搬家公司四川门窗绵阳网站建设主机租用一带一路

成都网站建设公司地址：成都市青羊区太升南路288号锦天国际A座10层 建设咨询028-86922220

成都快上网科技有限公司-四川网站建设设计公司 | 蜀ICP备19037934号 Copyright 2020,ALL Rights Reserved cdkjz.cn | 成都网站建设 | © Copyright 2020版权所有.

专家团队为您提供成都网站建设,成都网站设计,成都品牌网站设计,成都营销型网站制作等服务,成都建网站就找快上网！ | 成都网站建设哪家好？ | 网站建设地图