当前位置:  数据库>oracle

Oracle本地分区索引跨分区对成本的影响

    来源: 互联网  发布时间：2017-06-23

    本文导语: 分区索引分为本地索引和全局索引,但对于在分区表上建索引,一般用的比较多的还是普通索引和本地分区索引,而全局分区索引相对用的比较少.以下测试为验证:分区表上的本地分区索引因为查询条件引起跨分区,是否改为普通索引...

分区索引分为本地索引和全局索引,但对于在分区表上建索引,一般用的比较多的还是普通索引和本地分区索引,而全局分区索引相对用的比较少.
以下测试为验证:分区表上的本地分区索引因为查询条件引起跨分区,是否改为普通索引更合适.

以下测试:
Oracle version:11.2.0.4

建测试表:
drop table SCOTT.TB_TEST01;
create table SCOTT.TB_TEST01
partition by range (CREATED)
(
  partition P_2015 values less than (TO_DATE(' 2016-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')),
  partition P_2016 values less than (TO_DATE(' 2017-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')),
  partition P_MAX values less than (MAXVALUE)
)
as
select * from dba_objects;
##插入测试数据
insert into SCOTT.TB_TEST01
select * from SCOTT.TB_TEST01;
commit;
insert into SCOTT.TB_TEST01
select * from SCOTT.TB_TEST01;
commit;
insert into SCOTT.TB_TEST01
select * from SCOTT.TB_TEST01;
commit;
insert into SCOTT.TB_TEST01
select * from SCOTT.TB_TEST01;
commit;

#修改测试数据使其均匀分布
alter table SCOTT.TB_TEST01 enable row movement;

update SCOTT.TB_TEST01
set created=to_date('20150101','yyyymmdd')+dbms_random.value(1,1000);
commit;

#收集一下统计信息
begin
  dbms_stats.gather_table_stats(ownname=>'scott',tabname => 'TB_TEST01', cascade=> TRUE );
end;

情况1:过滤条件在全分区
查询语句:
select * from  SCOTT.TB_TEST01 where owner='SCOTT' and object_type='TABLE';

#建普通索引
create index scott.idx_TB_TEST01 on SCOTT.TB_TEST01(owner,object_type);
#执行计划
Execution Plan
----------------------------------------------------------
Plan hash value: 2208186213

--------------------------------------------------------------------------------
------------------------------------

| Id  | Operation                          | Name          | Rows  | Bytes | Cos
t (%CPU)| Time    | Pstart| Pstop |

--------------------------------------------------------------------------------
------------------------------------

|  0 | SELECT STATEMENT                  |              |  176 | 36432 |
19  (0)| 00:00:01 |      |      |

|  1 |  TABLE ACCESS BY GLOBAL INDEX ROWID| TB_TEST01    |  176 | 36432 |
19  (0)| 00:00:01 | ROWID | ROWID |

|*  2 |  INDEX RANGE SCAN                | IDX_TB_TEST01 |  176 |      |
 3  (0)| 00:00:01 |      |      |

--------------------------------------------------------------------------------
------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

  2 - access("OWNER"='SCOTT' AND "OBJECT_TYPE"='TABLE')


#统计信息
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        141  consistent gets
          0  physical reads
          0  redo size
      18793  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)
        176  rows processed
#上面的测试cost=19,consistent gets=141

#建本地分区索引
drop index scott.idx_TB_TEST01;
create index scott.idx_TB_TEST01 on SCOTT.TB_TEST01(owner,object_type) local;


SQL> /
#执行计划
Execution Plan
----------------------------------------------------------
Plan hash value: 2644430318

--------------------------------------------------------------------------------
------------------------------------

| Id  | Operation                          | Name          | Rows  | Bytes | Cos
t (%CPU)| Time    | Pstart| Pstop |

--------------------------------------------------------------------------------
------------------------------------

|  0 | SELECT STATEMENT                  |              |  176 | 36432 |
23  (0)| 00:00:01 |      |      |

|  1 |  PARTITION RANGE ALL              |              |  176 | 36432 |
23  (0)| 00:00:01 |    1 |    3 |

|  2 |  TABLE ACCESS BY LOCAL INDEX ROWID| TB_TEST01    |  176 | 36432 |
23  (0)| 00:00:01 |    1 |    3 |

|*  3 |    INDEX RANGE SCAN                | IDX_TB_TEST01 |  176 |      |
 7  (0)| 00:00:01 |    1 |    3 |

--------------------------------------------------------------------------------
------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

  3 - access("OWNER"='SCOTT' AND "OBJECT_TYPE"='TABLE')


#统计信息
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        147  consistent gets
          0  physical reads
          0  redo size
      11678  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)
        176  rows processed

#上面的测试cost=23,consistent gets=147
小结:
1.从上面测试可以看出,本地分区索引成本大于普通索引.因为此时用本地分区索引是需要PARTITION RANGE ALL的.
2.查询语句中的两个过滤条件都是等值的,在建这种复合索引时,索引列的顺序跟成本没有关系.这里没有测试，有兴趣可以测试。


情况2:过滤条件在单分区
查询语句:
select * from  SCOTT.TB_TEST01
where owner='SCOTT' and object_type='TABLE' and created between to_date('20161230','yyyymmdd') and to_date('20160105','yyyymmdd');

#建一个差的索引
drop index scott.idx_TB_TEST01;
create index scott.idx_TB_TEST01 on SCOTT.TB_TEST01(created,owner,object_type);

SQL> select * from  SCOTT.TB_TEST01
where owner='SCOTT' and object_type='TABLE' and created between to_date('20161230','yyyymmdd') and to_date('20160105','yyyymmdd');

#执行计划
Execution Plan
----------------------------------------------------------
Plan hash value: 783220382

--------------------------------------------------------------------------------
------------------------------------

| Id  | Operation                          | Name          | Rows  | Bytes | Cos
t (%CPU)| Time    | Pstart| Pstop |

--------------------------------------------------------------------------------
------------------------------------

|  0 | SELECT STATEMENT                  |              |    1 |  207 |  2
34  (0)| 00:00:03 |      |      |

|  1 |  TABLE ACCESS BY GLOBAL INDEX ROWID| TB_TEST01    |    1 |  207 |  2
34  (0)| 00:00:03 |    2 |    2 |

|*  2 |  INDEX RANGE SCAN                | IDX_TB_TEST01 |    1 |      |  2
33  (0)| 00:00:03 |      |      |

--------------------------------------------------------------------------------
------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

  2 - access("CREATED">=TO_DATE(' 2016-06-11 00:00:00', 'syyyy-mm-dd hh24:mi:ss
') AND "OWNER"='SCOTT' AND

              "OBJECT_TYPE"='TABLE' AND "CREATED" /

#执行计划
Execution Plan
----------------------------------------------------------
Plan hash value: 783220382

--------------------------------------------------------------------------------
------------------------------------

| Id  | Operation                          | Name          | Rows  | Bytes | Cos
t (%CPU)| Time    | Pstart| Pstop |

--------------------------------------------------------------------------------
------------------------------------

|  0 | SELECT STATEMENT                  |              |    4 |  828 |
14  (0)| 00:00:01 |      |      |

|  1 |  TABLE ACCESS BY GLOBAL INDEX ROWID| TB_TEST01    |    4 |  828 |
14  (0)| 00:00:01 |    2 |    2 |

|*  2 |  INDEX RANGE SCAN                | IDX_TB_TEST01 |    4 |      |
 3  (0)| 00:00:01 |      |      |

--------------------------------------------------------------------------------
------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

  2 - access("OWNER"='SCOTT' AND "OBJECT_TYPE"='TABLE' AND "CREATED">=TO_DATE('
 2016-06-11 00:00:00',

              'syyyy-mm-dd hh24:mi:ss') AND "CREATED" /

#执行计划
Execution Plan
----------------------------------------------------------
Plan hash value: 2387350646

--------------------------------------------------------------------------------
------------------------------------

| Id  | Operation                          | Name          | Rows  | Bytes | Cos
t (%CPU)| Time    | Pstart| Pstop |

--------------------------------------------------------------------------------
------------------------------------

|  0 | SELECT STATEMENT                  |              |    4 |  828 |
11  (0)| 00:00:01 |      |      |

|  1 |  PARTITION RANGE SINGLE            |              |    4 |  828 |
11  (0)| 00:00:01 |    2 |    2 |

|  2 |  TABLE ACCESS BY LOCAL INDEX ROWID| TB_TEST01    |    4 |  828 |
11  (0)| 00:00:01 |    2 |    2 |

|*  3 |    INDEX RANGE SCAN                | IDX_TB_TEST01 |    4 |      |
 7  (0)| 00:00:01 |    2 |    2 |

--------------------------------------------------------------------------------
------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

  3 - access("OWNER"='SCOTT' AND "OBJECT_TYPE"='TABLE' AND "CREATED">=TO_DATE('
 2016-06-11 00:00:00',

              'syyyy-mm-dd hh24:mi:ss') AND "CREATED" select * from  SCOTT.TB_TEST01
where owner='SCOTT' and object_type='TABLE' and created between to_date('20161220','yyyymmdd') and to_date('20170205','yyyymmdd');

#执行计划
Execution Plan
----------------------------------------------------------
Plan hash value: 2208186213

--------------------------------------------------------------------------------
------------------------------------

| Id  | Operation                          | Name          | Rows  | Bytes | Cos
t (%CPU)| Time    | Pstart| Pstop |

--------------------------------------------------------------------------------
------------------------------------

|  0 | SELECT STATEMENT                  |              |    1 |    98 |
 5  (0)| 00:00:01 |      |      |

|  1 |  TABLE ACCESS BY GLOBAL INDEX ROWID| TB_TEST01    |    1 |    98 |
 5  (0)| 00:00:01 | ROWID | ROWID |

|*  2 |  INDEX RANGE SCAN                | IDX_TB_TEST01 |    1 |      |
 3  (0)| 00:00:01 |      |      |

--------------------------------------------------------------------------------
------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

  2 - access("OWNER"='SCOTT' AND "OBJECT_TYPE"='TABLE' AND "CREATED">=TO_DATE('
 2016-12-20 00:00:00',

              'syyyy-mm-dd hh24:mi:ss') AND "CREATED" /

#执行计划
Execution Plan
----------------------------------------------------------
Plan hash value: 353906606

--------------------------------------------------------------------------------
------------------------------------

| Id  | Operation                          | Name          | Rows  | Bytes | Cos
t (%CPU)| Time    | Pstart| Pstop |

--------------------------------------------------------------------------------
------------------------------------

|  0 | SELECT STATEMENT                  |              |    1 |    98 |
 7  (0)| 00:00:01 |      |      |

|  1 |  PARTITION RANGE ITERATOR          |              |    1 |    98 |
 7  (0)| 00:00:01 |    2 |    3 |

|  2 |  TABLE ACCESS BY LOCAL INDEX ROWID| TB_TEST01    |    1 |    98 |
 7  (0)| 00:00:01 |    2 |    3 |

|*  3 |    INDEX RANGE SCAN                | IDX_TB_TEST01 |    1 |      |
 5  (0)| 00:00:01 |    2 |    3 |

--------------------------------------------------------------------------------
------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

  3 - access("OWNER"='SCOTT' AND "OBJECT_TYPE"='TABLE' AND "CREATED">=TO_DATE('
 2016-12-20 00:00:00',

              'syyyy-mm-dd hh24:mi:ss') AND "CREATED"


    
 
 

您可能感兴趣的文章:

  • 请教安装oracle9i的分区建议，硬盘只有10-15G
  • Oracle数据库中分区功能详解
  • Oracle移动数据文件到新分区步骤分析
  • Oracle 数据表分区的策略
  • Oracle 间隔分区
  • ORACLE 分区表的设计
  • 深入oracle分区索引的详解
  • Oracle学习：分区表和索引
  • Oracle使用hash分区优化分析函数查询
  • 使用Oracle Partition Table对日志表进行分区
  • ORACLE8的分区管理
  • oracle表空间表分区详解及oracle表分区查询使用方法
  • oracle分区表之hash分区表的使用及扩展
  • Oracle数据表分区的策略
  • oracle存储过程创建表分区实例
  • 在Oracle中手工对任务进行分区的方法
  • Oracle与Mysql主键、索引及分页的区别小结
  • 从Oracle的约束到索引
  • Oracle 9i轻松取得建表和索引的DDL语句
  • Oracle9i取得建表和索引的DDL语句
  • oracle10g全文索引自动同步语句使用方法
  • Oracle建立二进制文件索引的方法
  • 在Oracle 10g中如何获得索引的专家建议
  • Oracle全文索引设置
  • 用Oracle 9i全索引扫描快速访问数据
  • Oracle中如何把表和索引放在不同的表空间里
  • Oracle索引存储关系到数据库的运行效率
  • Oracle索引聚簇表的数据加载
  • 在Oracle中监控和跟踪索引使用情况
  • oracle 索引的相关介绍（创建、简介、技巧、怎样查看） .
  • Oracle中检查是否需要重构索引的sql
    •  
    本站(WWW.)旨在分享和传播互联网科技相关的资讯和技术，将尽最大努力为读者提供更好的信息聚合和浏览方式。
    本站(WWW.)站内文章除注明原创外，均为转载、整理或搜集自网络。欢迎任何形式的转载，转载请注明出处。












  • 相关文章推荐
  • Oracle 12c发布简单介绍及官方下载地址
  • 在linux下安装oracle,如何设置让oracle自动启动！也就是让oracle那个服务自动启动，不是手动的
  • oracle 11g最新版官方下载地址
  • 请问su oracle 和su - oracle有什么不同？
  • Oracle 数据库(oracle Database)Select 多表关联查询方式
  • 虚拟机装Oracle R12与Oracle10g
  • Oracle数据库(Oracle Database)体系结构及基本组成介绍
  • Oracle 数据库开发工具 Oracle SQL Developer
  • 如何设置让Oracle SQL Developer显示的时间包含时分秒
  • Oracle EBS R12 支持 Oracle Database 11g
  • Oracle 10g和Oracle 11g网格技术介绍
  • SCO unix下安装oracle，但没有光盘，请大家推荐一个oracle下载站点（unix版本的）。谢谢！！！！
  • oracle中如何把表中具有相同值列的多行数据合并成一行
  • 请问大家用oracle数据库, 用import oracle.*;下的东西么? 还是用标准库?
  • Oracle 数据库(oracle Database)性能调优技术详解
  • Linux /$ORACLE_HOME $ORACLE_HOME
  • ORACLE日期相关操作
  • Linux系统下Oracle的启动与Oracle监听的启动
  • ORACLE数据库常用字段数据类型介绍
  • 请问在solaris下安装ORACLE，用root用户和用oracle用户安装有什么区别么？
  • Oracle 12c的九大最新技术特性介绍
  • 网间Oracle的连接，远程连接Oracle服务器？？


    • 站内导航:


    特别声明:169IT网站部分信息来自互联网,如果侵犯您的权利,请及时告知,本站将立即删除！

    ©2012-2021,,E-mail:www_#163.com(请将#改为@)

    浙ICP备11055608号-3