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

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


以下测试:
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"<=TO_DATE(' 2016-07-11 00:00:00
', 'syyyy-mm-dd hh24:mi:ss'))

       filter("OWNER"='SCOTT' AND "OBJECT_TYPE"='TABLE')



#统计信息
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        226  consistent gets
          0  physical reads
          0  redo size
       1932  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          4  rows processed

#上面的测试cost=234,consistent gets=226

#建普通索引
drop index scott.idx_TB_TEST01;
create index scott.idx_TB_TEST01 on SCOTT.TB_TEST01(owner,object_type,created);

SQL> /

#执行计划
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"<=TO_DATE(' 2016-07-11 00:0
0:00', 'syyyy-mm-dd hh24:mi:ss'))



#统计信息
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          9  consistent gets
          0  physical reads
          0  redo size
       1932  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          4  rows processed
#上面的测试cost=14,consistent gets=9

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


SQL> /

#执行计划
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"<=TO_DATE(' 2016-07-11 00:0
0:00', 'syyyy-mm-dd hh24:mi:ss'))



#统计信息
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          8  consistent gets
          0  physical reads
          0  redo size
       1872  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          4  rows processed

#上面的测试cost=11,consistent gets=8

小结:
1.建的第一个差索引,之所以差,是因为created字段是一个区间值,此时access只能对应created字段,而owner字段和object_type都是通过filter过滤的.所以过滤条件是区间和要放后面.
2.从上面测试可以看出,本地分区索引成本小于普通索引.此时本地分区索引使用PARTITION RANGE SINGLE.


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

#建普通索引
drop index scott.idx_TB_TEST01;
create index scott.idx_TB_TEST01 on SCOTT.TB_TEST01(owner,object_type,created);

SQL> 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"<=TO_DATE(' 2017-02-05 00:0
0:00', 'syyyy-mm-dd hh24:mi:ss'))


#统计信息
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          7  consistent gets
          0  physical reads
          0  redo size
       1836  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          3  rows processed

#上面的测试cost=5,consistent gets=7          
          
#建本地分区索引
drop index scott.idx_TB_TEST01;
create index scott.idx_TB_TEST01 on SCOTT.TB_TEST01(owner,object_type,created) local ;

SQL> /

#执行计划
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"<=TO_DATE(' 2017-02-05 00:0
0:00', 'syyyy-mm-dd hh24:mi:ss'))


#统计信息
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         10  consistent gets
          0  physical reads
          0  redo size
       1803  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          3  rows processed

#上面的测试cost=7,consistent gets=10

小结:
1.从上面测试可以看出,本地分区索引成本大于普通索引.因为此时用本地分区索引是需要PARTITION RANGE ITERATOR的.

总结:
1.在选择普通索引和本地分区索引时,分区的跨度影响索引的选择,但相对于其它因素,这个影响还是很小的.如果考虑到将来分区归档的维护,本地分区索引还是有优势的.最重要的是索引列的选择,索引列顺序的选择才是最重要的.
2.查询语句中的两个过滤条件都是等值的,在建这种复合索引时,索引列的顺序跟成本没有关系.这里没有测试,有兴趣可以测试。
3.查询语句中的过滤条件是区间的,要放在等值的后面.

本页内容版权归属为原作者,如有侵犯您的权益,请通知我们删除。
关于半自动化搭建Data Guard,自己花了一些时间,总算是把这件事情继续推进了一下,还是再啰嗦一句,为什么不自动化,因为安全。主库就是主库,任何变更都要手工检查审核,自动化的工作在备库和中控端来完成。我希望自己的脚本能够只知道主库的IP,不用一次又一次连过去配置和检查,当然要完成自动化还是半自动化,有些网友也提醒的极是,那就是规范和标准。 预先条件: 1.目前的设计是基于11.2.0.4的版本,当然这个很容易定制,在此是作为一个基本的标准,作为环境的初始化和Data Guard对的搭建的基线。 2.默认
   前段时间有个开发的同事向我咨询一个问题,     开发同事:Oracle会存在一个用户插入数据,已经提交了;但是另外一个用户还查询不到吗?都是同一张表     jeanron:   不会的。     开发同事: 我们现在一个用户写入,程序日志是说已经写入;可是读取的用户还读取不到,在线延迟5分钟可能的问题在哪儿?或者你帮忙监控一下?     jeanron:   是Oracle吗,MySQL还可能有这种情况     开发同事: Oracle,MySQL是什么情况下会这样?     jeanron: 
【故障处理】 ORA-19809 错误处理 1    BLOG 文档结构图       2    前言部分 2.1    导读和注意事项 各位技术爱好者,看完本文后,你可以掌握如下的技能,也可以学到一些其它你所不知道的知识,~O(∩_∩)O~ : ① ORA-19809: limit exceeded for recovery files 错误的处理方法 RMAN-03009: failure of backup command on ORA_DISK_1 channel at 07/26/2016 17

手工搭建Data Guard - 2016-08-03 17:08:05

Data Guard的搭建可以使用GC图形化安装,优缺点很明显,优点就是图形化操作,符合国人的习惯(据secooler介绍外国程序员能用图形化做的事就一定用图形做,因为boss看得懂,和国人正相反。。。),缺点就是如同Windows一样,宛如黑盒,换句话说,要时刻祈祷不要出问题,否则有时很难知道他为什么挂了。。。 Data Guard还可以使用命令行操作,正如各位所知,图形化的任何操作背后,其实都是使用的命令。OCM第七场景考试中,我也是纠结了许久,临开始前才决定使用手工方式创建DG,怕的就是图形安装的不

mysql分区技术测试 - 2016-07-25 19:07:38

mysql是否支持partition呢,在mysql下执行show plugins;(显示插件) 其中包含了partition字段就说明你的数据库支持分区技术 1,创建带有分区的表 表明创建了带有hash分区技术的表,t2表有5个分区 mysql支持的几种分区技术,最常用的是range和list二种,Hash主要用来测试 看到test中有12张t2表,每个分区表都有表索引,检索数据更快。 把t2表创建索引 create index in_id on t2(id); 2、现在我们插入一个一千万行的数据来测试

Oracle 学习笔记 - 2016-07-25 18:07:03

1,oracle安装包下载: 官网地址:http://www.oracle.com/technetwork/indexes/downloads/index.html#database 2,安装: 在oracle官网下载安装包,以oracle 11g为例,下载下来有两个压缩包,分别是win32_11gR2_database_1of2和win32_11gR2_database_2of2,把它俩解压到同一个文件夹下,注意路径中不能出现中文符号。 3.然后双击可执行文件【setup.exe】,如图所示: 4.双击
1. Oracle11g安装 http://pan.baidu.com/s/1gfa3e63 ; 这里是我在Oracle官网下载好了Windows系统64位的安装包,有2个zip文件。不想去官网找下载地址的童鞋可以直接用这个百度云盘的下载链接。 1.去 www.oracle.com 下载最新的oracle11g安装包的压缩文件,有2个压缩文件,都需要下载,下载完成以后需要解压缩在同一个目录下。 在开始oracle数据安装之前建议 : 1.关闭本机的病毒防火墙。 2.断开互联网。 这样可以避免解压缩丢失文件

HBASE RPC 详细解析 - 2016-07-25 18:07:33

HBASE RPC 详细解析  注:下面的图是作者随便画的,不怎么严格,大家能看懂就好。 由于篇幅所限,本文只是大略的讲解了一下RPC所涉及到的大概模块,较为模糊,有兴趣者可以继续深挖 RPC (远程过程调用协议) 是不同主机进程间通讯的一种方式,协议采用客户机 - 服务器模式的架构,请求程序为客户机,服务提供程序为服务器, hbase 在 client 与 server 通信上采用的也是 RPC 协议,并在 client 端与 server 端实现了具体的 RPC 协议内容 ,现在我们就从客户机,服务器

mysql入门的十条语句 - 2016-07-25 17:07:06

默认已经装好了mysql服务器,下来介绍一些入门的基本语句。 一、连接数据库所需要的工具   mysql是一种服务器,帮助我们对数据进行管理,是一个服务器端。我们要与mysql服务器进行交互,必须通过客户端。常见的客户端mysql_front,navcat,mysql自带的命令行客户端。现在我们使用windows自带的命令行与mysql服务器进行交互。当然首先要与mysql服务器进行连接。 打开命令行,进入到mysql里面。 系统会报错 ,这是因为没有与mysql服务器进行连接。 1、那么如何连接服务器呢

mysql的分区技术 - 2016-07-25 14:07:48

mysql是否支持partition呢,在mysql下执行show plugins;(显示插件) 其中包含了partition字段就说明你的数据库支持分区技术 1,创建带有分区的表 表明创建了带有hash分区技术的表,t2表有5个分区 mysql支持的几种分区技术,最常用的是range和list二种,Hash主要用来测试 看到test中有12张t2表,每个分区表都有表索引,检索数据更快。 把t2表创建索引 create index in_id on t2(id); 2、现在我们插入一个一千万行的数据来测试