安科网

Oracle分区表创建本地索引和局部索引的性能

社会主义好

社会主义好

2017-10-01

关注 关注

1、演示环境：

SQL> select * from v$version;

BANNER

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

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi

PL/SQL Release 10.2.0.4.0 - Production

CORE 10.2.0.4.0 Production

TNS for 64-bit Windows: Version 10.2.0.4.0 - Production

NLSRTL Version 10.2.0.4.0 - Production

2、测试数据创建：
表创建SQL如下：

如下：

create table T65_F_S_PGZ10023_M

(

  STATISTIC_DT DATE,

  INDICKEY VARCHAR2(20),

  OBJKEY VARCHAR2(64),

  INDICVAL1 NUMBER(30,2),

  INDICVAL2 NUMBER(30,2),

  INDICVAL3 NUMBER(30,2),

  INDICVAL4 NUMBER(30,2),

  INDICVAL5 NUMBER(30,2)

)

partition by range (STATISTIC_DT)

(

  partition PT_20111231 values less than (TO_DATE(\'2012-01-01 00:00:00\', \'YYYY-MM-DD HH24:MI:SS\')) tablespace tsdat01,

  partition PT_20120101 values less than (TO_DATE(\'2012-01-02 00:00:00\', \'YYYY-MM-DD HH24:MI:SS\')) tablespace tsdat01,

  partition PT_20120102 values less than (TO_DATE(\'2012-01-03 00:00:00\', \'YYYY-MM-DD HH24:MI:SS\')) tablespace tsdat01,

  partition PT_20120103 values less than (TO_DATE(\'2012-01-04 00:00:00\', \'YYYY-MM-DD HH24:MI:SS\')) tablespace tsdat01,

  partition PT_20120104 values less than (TO_DATE(\'2012-01-05 00:00:00\', \'YYYY-MM-DD HH24:MI:SS\')) tablespace tsdat01,

  partition PT_20120105 values less than (TO_DATE(\'2012-01-06 00:00:00\', \'YYYY-MM-DD HH24:MI:SS\')) tablespace tsdat01,

  partition PT_20120106 values less than (TO_DATE(\'2012-01-07 00:00:00\', \'YYYY-MM-DD HH24:MI:SS\')) tablespace tsdat01,

  partition PT_20120107 values less than (TO_DATE(\'2012-01-08 00:00:00\', \'YYYY-MM-DD HH24:MI:SS\')) tablespace tsdat01,

  partition PT_20120108 values less than (TO_DATE(\'2012-01-09 00:00:00\', \'YYYY-MM-DD HH24:MI:SS\')) tablespace tsdat01,

  partition PT_20120109 values less than (TO_DATE(\'2012-01-10 00:00:00\', \'YYYY-MM-DD HH24:MI:SS\')) tablespace tsdat01,

  partition PT_20120100 values less than (TO_DATE(\'2012-01-11 00:00:00\', \'YYYY-MM-DD HH24:MI:SS\')) tablespace tsdat01,

  partition PT_20120111 values less than (TO_DATE(\'2012-01-12 00:00:00\', \'YYYY-MM-DD HH24:MI:SS\')) tablespace tsdat01

);

CREATE INDEX PI_T65_F_S_PGZ10023_M ON T65_F_S_PGZ10023_M (OBJKEY) TABLESPACE TSDAT01 PARALLEL LOCAL;

 

 

测试数据生成SQL如下：

SQL>insert into T65_F_S_PGZ10023_M(INDICKEY,STATISTIC_DT,OBJKEY,INDICVAL1)

 select 'PGZ10023' as INDICKEY, to_date('2012-01-08','yyyy-mm-dd') as STATISTIC_DT ,'81'||lpad(to_char(rownum),7,'0') as OBJKEY  ,trunc(dbms_random.value(1,rownum)) as INDICVAL1 from dual connect by  rownum <100000;

 

修改日期可以得到2012-01-01 到2012-01-08这8天的测试数据

测试数据按天汇总的记录数

SQL>  select statistic_dt,count(*) from t65_f_s_pgz10023_m group by statistic_dt;
 
STATISTIC_DT   COUNT(*)
------------ ----------
2012-01-01        99999
2012-01-02        99999
2012-01-03        99999
2012-01-04        99999
2012-01-05        99999
2012-01-06        99999
2012-01-07        99999
2012-01-08        99999

3、 使用AUTOTRACE查看执行计划

打开CMD命令窗口，使用 aml 用户连接数据库

C:\Users\Administrator>sqlplus aml/abc

开启执行计划

SQL> set autotrace traceonly

设置行长度

SQL> set lines 300;
SQL> CREATE INDEX PI_T65_F_S_PGZ10023_M ON T65_F_S_PGZ10023_M (OBJKEY) TABLESPACE TSDAT01 local;
SQL> select * from T65_F_S_PGZ10023_M where  objkey='810000836';

得到如下的执行计划，此索引是本地索引

SQL> select * from T65_F_S_PGZ10023_M where  objkey='810000836';

8 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2061015615


---------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                               | Name                             |  Rows  | Bytes | Cost (%CPU)| Time      | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                                 |                                      |     8    |   960 |    14   (0)    | 00:00:01 |         |        |
|   1 |  PARTITION RANGE ALL                             |                                     |     8    |   960 |    14   (0)    | 00:00:01 |     1  |    12 |
|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID    | T65_F_S_PGZ10023_M     |     8    |   960 |    14   (0)    | 00:00:01 |     1  |    12 |
|*  3 |    INDEX RANGE SCAN                              | PI_T65_F_S_PGZ10023_M |     8    |         |    13   (0)    | 00:00:01 |     1 |    12 |
---------------------------------------------------------------------------------------------------------------------------

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

   3 - access("OBJKEY"='810000836')

Note
-----
   - dynamic sampling used for this statement

Statistics
----------------------------------------------------------
         32    recursive calls
          0    db block gets
        197   consistent gets
         32    physical reads
          0     redo size
       1182  bytes sent via SQL*Net to client
        492   bytes received via SQL*Net from client
          2    SQL*Net roundtrips to/from client
          0    sorts (memory)
          0    sorts (disk)
          8    rows processed

SQL>


我们删除本地索引，现在创建局部索引

SQL> drop index PI_T65_F_S_PGZ10023_M;

SQL> CREATE INDEX I_T65_F_S_PGZ10023_M ON T65_F_S_PGZ10023_M (OBJKEY) TABLESPACE TSDAT01 global;

将缓冲池清空

SQL> alter system flush buffer_cache;

再次执行刚才的SQL

SQL> select * from T65_F_S_PGZ10023_M where  objkey='810000836';

全局索引的执行计划

8 rows selected.

Execution Plan

----------------------------------------------------------
Plan hash value: 3364546307


---------------------------------------------------------------------------------------------------------------------------
| Id   | Operation                                             | Name                           | Rows  | Bytes | Cost (%CPU)| Time     | Pstart    | Pstop   |
---------------------------------------------------------------------------------------------------------------------------
|   0  | SELECT STATEMENT                              |                                    |     8   |   960 |    12   (0)    | 00:00:01 |            |            |
|   1  |  TABLE ACCESS BY GLOBAL INDEX ROWID| T65_F_S_PGZ10023_M    |     8   |   960 |    12   (0)    | 00:00:01 | ROWID | ROWID |
|*  2 |   INDEX RANGE SCAN                              | I_T65_F_S_PGZ10023_M |     8   |          |     3   (0)    | 00:00:01 |            |            |
---------------------------------------------------------------------------------------------------------------------------


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

   2 - access("OBJKEY"='810000836')

Note
-----
   - dynamic sampling used for this statement

Statistics
----------------------------------------------------------
          0    recursive calls
          0    db block gets
         12   consistent gets
         32   physical reads
          0    redo size
       1182  bytes sent via SQL*Net to client
        492  bytes received via SQL*Net from client
          2    SQL*Net roundtrips to/from client
          0    sorts (memory)
          0    sorts (disk)
          8    rows processed

 

 objkey字段索引比较

Statistics

本地索引（LOCAL）

全局索引（GLOBAL）

recursive calls （递归调用）

   32

     0 

db block gets  （数据块）

    0

     0

consistent get  (一致性读)

   197

    12 

physical reads  （物理读）

   32

    32 

redo size                         

   0

     0 

bytes sent via SQL*Net to client     

 1182

  1182 

bytes received via SQL*Net from client

  492

   492 

SQL*Net roundtrips to/from client     

    2

     2 

sorts (memory)                     

   0

     0 

sorts (disk)                       

    0

     0 

rows processed                     

    8

     8 

查看段的记录数：

SQL> select Segment_NAME,PARTITION_NAME,SEGMENT_TYPE from user_segments a where  a.segment_name='I_T65_F_S_PGZ10023_M';
 
SEGMENT_NAME                                                                     PARTITION_NAME                 SEGMENT_TYPE
-------------------------------------------------------------------------------- ------------------------------ ------------------
I_T65_F_S_PGZ10023_M                                                                                            INDEX

全局索引的数据段只有一条记录，而本地索引的数据段有12条记录（创建了12个分区定义）。

 

SQL> drop index i_t65_f_s_pgz10023_m;
 
Index dropped
 
SQL> CREATE INDEX I_T65_F_S_PGZ10023_M ON T65_F_S_PGZ10023_M (OBJKEY) TABLESPACE TSDAT01 local;
 
Index created
 
SQL> select Segment_NAME,PARTITION_NAME,SEGMENT_TYPE from user_segments a where  a.segment_name='I_T65_F_S_PGZ10023_M';
 
SEGMENT_NAME                                                                     PARTITION_NAME                 SEGMENT_TYPE
-------------------------------------------------------------------------------- ------------------------------ ------------------
I_T65_F_S_PGZ10023_M                                                             PT_20111231                    INDEX PARTITION
I_T65_F_S_PGZ10023_M                                                             PT_20120100                    INDEX PARTITION
I_T65_F_S_PGZ10023_M                                                             PT_20120101                    INDEX PARTITION
I_T65_F_S_PGZ10023_M                                                             PT_20120102                    INDEX PARTITION
I_T65_F_S_PGZ10023_M                                                             PT_20120103                    INDEX PARTITION
I_T65_F_S_PGZ10023_M                                                             PT_20120104                    INDEX PARTITION
I_T65_F_S_PGZ10023_M                                                             PT_20120105                    INDEX PARTITION
I_T65_F_S_PGZ10023_M                                                             PT_20120106                    INDEX PARTITION
I_T65_F_S_PGZ10023_M                                                             PT_20120107                    INDEX PARTITION
I_T65_F_S_PGZ10023_M                                                             PT_20120108                    INDEX PARTITION
I_T65_F_S_PGZ10023_M                                                             PT_20120109                    INDEX PARTITION
I_T65_F_S_PGZ10023_M                                                             PT_20120111                    INDEX PARTITION
 
12 rows selected
 

看了索引的数据段分布，现在可以理解本地索引的一致性读与物理读为什么那么高，这是因为查找 objkey='810000836'这个值要去每个分区的段中查找，并将结果合并后返回。

 

创建分区字段的索引比较

SQL> CREATE INDEX PI_T65_F_S_PGZ10023_M_DT ON T65_F_S_PGZ10023_M (STATISTIC_DT) TABLESPACE TSDAT01 LOCAL;
SQL> exec dbms_stats.gather_table_stats(ownname => 'AML',tabname => 'T65_F_S_PGZ10023_M');

SQL> select * from T65_F_S_PGZ10023_M where STATISTIC_DT=to_date('2012-01-04','yyyy-mm-dd');

99999 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3412225213


-------------------------------------------------------------------------------------------------------------
| Id   | Operation                         | Name                          | Rows  | Bytes  | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------
|   0  | SELECT STATEMENT          |                                  | 99990 |  3027K|   141   (3)| 00:00:02 |       |       |
|   1  |  PARTITION RANGE SINGLE  |                                  | 99990 |  3027K|   141   (3)| 00:00:02 |     5 |     5 |
|*  2 |   TABLE ACCESS FULL          | T65_F_S_PGZ10023_M | 99990 |  3027K|   141   (3)| 00:00:02 |     5 |     5 |

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


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


   2 - filter("STATISTIC_DT"=TO_DATE(' 2012-01-04 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))


Note
-----
   - dynamic sampling used for this statement

Statistics
----------------------------------------------------------
          1         recursive calls
          0        db block gets
       7188      consistent gets
         549     physical reads
          0        redo size
    3186021   bytes sent via SQL*Net to client
      73818    bytes received via SQL*Net from client
       6668     SQL*Net roundtrips to/from client
          0       sorts (memory)
          0       sorts (disk)
      99999   rows processed
 

 

创建全局索引的执行计划

SQL> drop index PI_T65_F_S_PGZ10023_M_DT;

SQL> CREATE INDEX I_T65_F_S_PGZ10023_M_DT ON T65_F_S_PGZ10023_M (STATISTIC_DT) TABLESPACE TSDAT01 global;

SQL> alter system flush buffer_cache;

SQL> select * from T65_F_S_PGZ10023_M where STATISTIC_DT=to_date('2012-01-04','yyyy-mm-dd');

99999 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3412225213


-------------------------------------------------------------------------------------------------------------
| Id   | Operation              | Name               | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------
|   0  | SELECT STATEMENT       |                    | 99991 |  3027K|   141   (3)| 00:00:02 |       |       |
|   1  |  PARTITION RANGE SINGLE|                    | 99991 |  3027K|   141   (3)| 00:00:02 |     5 |     5 |
|*  2 |   TABLE ACCESS FULL    | T65_F_S_PGZ10023_M | 99991 |  3027K|   141   (3)| 00:00:02 |     5 |     5 |
-------------------------------------------------------------------------------------------------------------


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


   2 - filter("STATISTIC_DT"=TO_DATE(' 2012-01-04 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

Statistics
----------------------------------------------------------
          1     recursive calls
          0     db block gets
       7188   consistent gets
        549    physical reads
          0      redo size
    3186021  bytes sent via SQL*Net to client
      73818   bytes received via SQL*Net from client
       6668   SQL*Net roundtrips to/from client
          0     sorts (memory)
          0     sorts (disk)
      99999  rows processed
 

STATISTIC_DT字段索引比较

Statistics

本地索引（LOCAL）

全局索引（GLOBAL）

recursive calls （递归调用）

      1

        1

db block gets  （数据块）

         0

        0

consistent get  (一致性读)

 7188

   7188

physical reads  （物理读）

     549

      549

redo size                         

         0

        0

bytes sent via SQL*Net to client     

   3186014

  3186014

bytes received via SQL*Net from client

     73818

    73818

SQL*Net roundtrips to/from client     

      6668

     6668

sorts (memory)                     

         0

        0

sorts (disk)                       

         0

        0

rows processed                     

     99999

    99999

oracle分区表 索引 分区表 date

社会主义好

社会主义好

0 关注 0 粉丝 0 动态

关注 关注

相关推荐

记一次Oracle分区表全局索引重建的过程

SELECT Upper "表空间名", D.TOT_GROOTTE_MB "表空间大小", D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间"

zhangchaoming 2020-05-17

Oracle 数据表之分区表

　　1，创建4个测试用的表空间，每个表空间作为一个独立分区。　　范围分区就是对数据表中的某个值的范围进行分区，根据某个值的范围，决定将该数据存储在哪个分区上。如根据序号分区，根据业务记录的创建日期进行分区等。　　　　id number not null,　

Carlos 2020-02-21

Oracle partition table 分区表详解

分区表就是通过使用分区技术，将一张大表，拆分成多个表分区，从而提升数据访问的性能，以及日常的可维护性。分区表中，每个分区的逻辑结构必须相同。对于应用而言完全透明，分区前后没有变化，不需要进行修改。分区表相关试图显示表分区信息 显示数据库所有分区表的详细分区

LuoXinLoves 2020-02-02

Oracle 11g数据库的分区表扩展(按年度)

一般一张表超过2G的大小，Oracle推荐使用分区表。oracle 11g支持自动分区，也可按年度、季度、月份手动扩展分区。有一张分区表定义如下,需扩展2020年分区。扩展表分区SQL语法：alter table [scheam.]表名 add parti

LuoXinLoves 2020-01-19

Oracle 11g 分区表创建（自动按年、月、日分区）

前言：工作中有一张表一年会增长100多万的数据，量虽然不大，可是表字段多，所以一年下来也会达到 1G，而且只增不改，故考虑使用分区表来提高查询性能，提高维护性。　　Oracle 11g 支持自动分区，不过得在创建表时就设置好分区。　　如果已经存在的表需要改

gaobudong 2019-03-27

ORACLE分区表、分区索引

List分区方式，一般是在range基础上做的二级分区较多，是一种列举方式进行分区，一般讲某些地区、状态或指定规则的编码等进行划分。分区可以进行两两组合，ORACLE 11G以前两两组合都必须以range作为一级分区的开头，ORACLE目前最多支持2级别分

HackAge 2013-11-01

Oracle中分区表的使用

4)添加数据文件（在建立表空间时，若是约束了表空间的大小，那么一段时间后，这个表空间就会被装满，无法再添加其他对象。create undo tablespace undotBS2 datafile 'full_path\UNDOTBS02.DBF' siz

EricRay 2011-08-09

Oracle_为分区表添加新分区

exchange partitionunusedORA-14097ORA-14323CTAS需求：添加分区。离线消息表OFFLMG，在原有分区的基础上添加一个分区。values;执行报错，错误代码ORA-14323：list分区表中有default分区存在

xuanzi 2019-06-26

Oracle分区表删除分区数据时导致索引失效解决

今天有个小任务就是要删除些数据，哈哈，先自己小开心一下。这个分区表是按照里面有个创建时间字段来分区的，1个季度为1个分区。所以我现在要将2017年7月1日之前的数据删除，可以直接删除表分区数据就好。如果要是用delete去删除这么多的数据，我还要写存储过程

xuanzi 2018-02-18

Oracle中分区表中表空间属性

Oracle中的分区表是Oracle中的一个很好的特性，可以把大表划分成多个小表，从而提高对于该大表的SQL执行效率，而各个分区对应用又是透明的。分区表中的每个分区有独立的存储特性，包括表空间、PCT_FREE等。下面使用一个例子来解释上面的这些问题。而分

itisaamazing 2017-05-26

Oracle 分区表进行shrink操作

公司业务系统的数据增长很快，需要进行定期进行归档，其中有些数据用分区表进行存储，归档完之后释放了大量的空间，之前已经有文档介绍普通表进行shrink操作的步骤，本文档记录对Oracle分区表和复合分区表进行shrink的操作；分区表的查询select t3

zipp 2017-02-27

Oracle普通表转换成分区表的操作

Oracle官方建议当表的大小大于2GB的时候就使用分区表进行管理，分区表相对于小的表管理和性能上都有很大的优势，本文档暂时不介绍具体的优势，主要介绍几种普通表转换成分区表的方法；做过一个大小2.3GB，总行数360万行的表，整个过程大概花了56秒的时间，

dataminer 2016-11-15

Oracle 11g维护分区（四）——Exchanging Partitions

你可以通过交换数据段将一个分区转化为一个非分区表，也可以将一个非分区表转化为一个分区表的分区。你也可以将一个哈希分区表转化为一个复合哈希分区表的一个分区，或者将一个复合哈希分区表的一个分区转化为一个哈希分区表。类似的，你可以将一个分区表转化为一个复合分区表

我的DBA之路 2015-07-27

Oracle分区表数据迁移、管理自动化过程

下面过程围绕公司Oracle数据库运行环境，写出来主要目的为了实现自动化。过程完成任务有1.自动添加前一天分区，以时间做分区2.自动删除T_partition_1分区表6天前分区，T_partition_1是当前表3.自动删除T_partition_2分区

kiddwyl 2015-06-16

详解Oracle partition分区表

随着表中行数的增多，管理和性能性能影响也将随之增加。表空间的数目不必等于分区的数目，即n不一定等于m，如果指定的分区数目比表空间的数目多，则分区将会以循环的方式分配到表空间中，一个表空间可以含有多个分区：　4，列表分区 列表分区告诉Oracle所有可能的

海内存知己 2015-05-06

Oracle普通表—>分区表转换(9亿数据量)

某普通表T，由于前期设计不当没有分区，如今几年来的数据量已达9亿+, 空间占用大约350G，在线重定义为分区表不现实，故采取申请时间窗口停此表应用，改造为分区表。-- Create table 创建分区表T_PART,分区从14年6月开始。使用分区添加工具

joyleeLyhua 2015-04-03

Oracle在线重定义DBMS_REDEFINITION 普通表—>分区表

实验环境：RHEL 6.4 + Oracle 11.2.0.3实验：在线重定义 普通表 为 分区表，包括主键对应的索引都改造为分区索引.create table t_objects as select * from dba_objects;alter ta

蓦然回首择城终老 2015-04-03

Oracle中的临时表、外部表和分区表

临时表在Oracle中，临时表是“静态”的，它与普通的数据表一样只需要一次创建，其结构从创建到删除的整个期间都是有效的。相对于其他类型的表，临时表只有在用户实际向表中添加数据时，才会为其分配空间，并且分配的空间来自临时表空间。这就避免了与永久对象的数据争用

qingfengfeixin 2015-03-02

Oracle分区表的层次查询如何才能用到分区？

最近在调优Oracle分区表的层次查询时，发现用不到分区，做了一个实验，发现还是可以用的到的，只是写法上有些要求。create table testpartition by list;insert into test values;insert into

sbtmbj00 2015-02-13

Oracle分区表执行计划

Oracle分区表有很多好处，以大化小，一小化了，加上并行的使用，在loap中能往往能提高几十倍甚至几百倍的效果。当然表设计得不好也会适得其反，效果比普通表跟糟糕。为了更好的使用分区表，这里看一下分区表的执行计划。PARTITION RANGE ALL：扫

JaneChiu 2015-02-11
社会主义好

社会主义好

W3CSchool教程
HTML 教程
CSS 教程
Bootstrap 教程
Javascript 教程
jQuery 教程
后端教程
C 教程
Java 教程
PHP 教程
Python 教程
Go 教程
移动开发
Android 教程
Swift 教程
Kotlin 教程
jQuery Mobile 教程
ionic 教程
关于我们
新闻动态
联系方式
招聘英才
安科实验室
帮助与反馈

安科网(Ancii)，中国第一极客网

Copyright © 2013 - 2019 Ancii.com

京ICP备18063983号 京公网安备11010802014868号