Oracle 分区索引

    分区索引(或索引分区)主要是针对分区表而言的。随着数据量的不断增长，普通的堆表需要转换到分区表，其索引呢，则对应的转换到分区索引。分区索引的好处是显而易见的。就是简单地把一个索引分成多个片断，在获取所需数据时，只需要访问更小的索引片断(块)即可实现。同时把分区放在不同的表空间可以提高分区的可用性和可靠性。本文主要描述了分区索引的相关特性并给出演示示例。

 

1、分区索引的相关概念
a、分区索引的几种方式：表被分区而索引未被分区；表未被分区，而索引被分区；表和索引都被分区
b、分区索引可以分为本地分区索引以及全局分区索引
本地分区索引：
   本地分区索引信息的存放依赖于父表分区。也就是说对于本地索引一定是基于分区表创建的。
   缺省情况下，创建本地索引时，如未指定索引存放表空间，会自动将本地索引存放到数据所在分区定义时的表空间。
   本地索引的分区机制和表的分区机制一样，本地索引可以是是B树索引或位图索引。
   本地索引是对单个分区的，每个分区索引只指向一个表分区，为对等分区。
   本地索引支持分区独立性，因此对于这些单独的分区增加，截取，删除，分割，脱机等处理无需同时删除或重建。
   本地索引多应用于数据仓库环境中。
      
全局分区索引：
   全局分区索引时分区表和全局索引的分区机制不一样，在创建时必须定义分区键的范围和值。
   全局分区索引在创建时应指定Global关键字且全局分区索引只能是B树索引。
   全局索引可以分区，也可以是不分区索引，全局索引必须是前缀索引，即索引列必须包含分区键。
   全局索引分区中，一个分区索引能指向n个表分区，同时，一个表分区，也可能指向n个索引分区。
   默认情况下全局索引对于分区增加，截取，删除，分割等都必须重建或修改时指定update global indexs。
   全局分区索引只按范围或者散列hash分区。
   全局分区索引多应用于oltp系统中。
  
c、有前缀索引和无前缀索引
本地和全局分区索引又分为两个子类型即有前缀索引和无前缀索引。
前缀和非前缀索引都可以支持索引分区消除，前提是查询的条件中包含索引分区键。
有前缀索引:
   有前缀索引包含了分区键，即分区键列被包含在索引中。
   有前缀索引支持本地分区索引以及全局分区索引。

无前缀索引：
   无前缀索引即没有把分区键的前导列作为索引的前导列。
   无前缀索引仅仅支持本地分区索引。  

 

2、本地分区索引演示

--环境
SQL> select * from v$version where rownum<2;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi

SQL> create user leshami identified by xxx;

SQL> grant dba to leshami;

--创建演示需要用到的表空间
SQL> create tablespace tbs_tmp datafile '/u02/database/SYBO2/oradata/tbs_tmp.dbf' size 10m autoextend on;

SQL> alter user leshami default tablespace tbs_tmp;

SQL> create tablespace tbs1 datafile '/u02/database/SYBO2/oradata/tbs1.dbf' size 10m autoextend on;

SQL> create tablespace tbs2 datafile '/u02/database/SYBO2/oradata/tbs2.dbf' size 10m autoextend on;

SQL> create tablespace tbs3 datafile '/u02/database/SYBO2/oradata/tbs3.dbf' size 10m autoextend on;

SQL> create tablespace idx1 datafile '/u02/database/SYBO2/oradata/idx1.dbf' size 10m autoextend on;

SQL> create tablespace idx2 datafile '/u02/database/SYBO2/oradata/idx2.dbf' size 10m autoextend on;

SQL> create tablespace idx3 datafile '/u02/database/SYBO2/oradata/idx3.dbf' size 10m autoextend on;

SQL> conn leshami/xxx

-- 创建一个lookup表
CREATE TABLE lookup (
  id            NUMBER(10),
  description   VARCHAR2(50)
);

--添加主键约束
ALTER TABLE lookup ADD (
  CONSTRAINT lookup_pk PRIMARY KEY (id)
);

--插入数据
INSERT INTO lookup (id, description) VALUES (1, 'ONE');
INSERT INTO lookup (id, description) VALUES (2, 'TWO');
INSERT INTO lookup (id, description) VALUES (3, 'THREE');
COMMIT;

CREATE TABLE big_table (
  id            NUMBER(10),
  created_date  DATE,
  lookup_id     NUMBER(10),
  data          VARCHAR2(50)
)
PARTITION BY RANGE (created_date)
(PARTITION big_table_2012 VALUES LESS THAN (TO_DATE('01/01/2013', 'DD/MM/YYYY')) tablespace tbs1,
 PARTITION big_table_2013 VALUES LESS THAN (TO_DATE('01/01/2014', 'DD/MM/YYYY')) tablespace tbs2,
 PARTITION big_table_2014 VALUES LESS THAN (MAXVALUE)tablespace tbs3 ) ;

--填充数据到分区表
DECLARE
  l_lookup_id    lookup.id%TYPE;
  l_create_date  DATE;
BEGIN
  FOR i IN 1 .. 10000 LOOP
    IF MOD(i, 3) = 0 THEN
      l_create_date := ADD_MONTHS(SYSDATE, -24);
      l_lookup_id   := 2;
    ELSIF MOD(i, 2) = 0 THEN
      l_create_date := ADD_MONTHS(SYSDATE, -12);
      l_lookup_id   := 1;
    ELSE
      l_create_date := SYSDATE;
      l_lookup_id   := 3;
    END IF;

    INSERT INTO big_table (id, created_date, lookup_id, data)
    VALUES (i, l_create_date, l_lookup_id, 'This is some data for ' || i);
  END LOOP;
  COMMIT;
END;
/ 

--未指定索引分区及存储表空间情形下创建索引
SQL> CREATE INDEX bita_created_date_i ON big_table(created_date) LOCAL;

Index created.

SQL> select index_name, partitioning_type, partition_count from user_part_indexes;

INDEX_NAME                     PARTITI PARTITION_COUNT
------------------------------ ------- ---------------
BITA_CREATED_DATE_I            RANGE                 3

--Author : Leshami
--Blog   : http://blog.csdn.net/leshami

--从下面的查询可知，索引直接存放到分表表对应的表空间
SQL> select partition_name, high_value, tablespace_name from user_ind_partitions;

PARTITION_NAME                 HIGH_VALUE                               TABLESPACE_NAME
------------------------------ ---------------------------------------- ------------------------------
BIG_TABLE_2014                 MAXVALUE                                 TBS3
BIG_TABLE_2013                 TO_DATE(' 2014-01-01 00:00:00', 'SYYYY-M TBS2
                               M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

BIG_TABLE_2012                 TO_DATE(' 2013-01-01 00:00:00', 'SYYYY-M TBS1
                               M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

--删除索引
SQL> drop index bita_created_date_i;

--指定索引分区名表空间名创建索引
SQL> CREATE INDEX bita_created_date_i
  2     ON big_table (created_date)
  3     LOCAL (
  4        PARTITION idx_2012 TABLESPACE idx1,
  5        PARTITION idx_2013 TABLESPACE idx2,
  6        PARTITION idx_2014 TABLESPACE idx3)
  7     PARALLEL 3;

Index created.

SQL> select partition_name, high_value, tablespace_name from user_ind_partitions;

PARTITION_NAME                 HIGH_VALUE                               TABLESPACE_NAME
------------------------------ ---------------------------------------- ------------------------------
IDX_2014                       MAXVALUE                                 IDX3
IDX_2013                       TO_DATE(' 2014-01-01 00:00:00', 'SYYYY-M IDX2
                               M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

IDX_2012                       TO_DATE(' 2013-01-01 00:00:00', 'SYYYY-M IDX1
                               M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

SQL> select * from big_table where rownum<2;

        ID CREATED_  LOOKUP_ID DATA
---------- -------- ---------- --------------------------------------------------
      1413 20120625          2 This is some data for 1413

--查看local index是否被使用，从下面的执行计划中可知，索引被使用，支持分区消除
SQL> set autot trace exp;
SQL> select * from big_table where created_date=to_date('20120625','yyyymmdd');

Execution Plan
----------------------------------------------------------
Plan hash value: 2556877094

--------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name                | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                     |     1 |    41 |     2   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE SINGLE            |                     |     1 |    41 |     2   (0)| 00:00:01 |     1 |     1 |
|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID| BIG_TABLE           |     1 |    41 |     2   (0)| 00:00:01 |     1 |     1 |
|*  3 |    INDEX RANGE SCAN                | BITA_CREATED_DATE_I |     1 |       |     1   (0)| 00:00:01 |     1 |     1 |
--------------------------------------------------------------------------------------------------------------------------

3、全局分区索引演示

--为表添加主键
SQL> ALTER TABLE big_table ADD (
  2    CONSTRAINT big_table_pk PRIMARY KEY (id)
  3  );

Table altered.       

SQL> select index_name,index_type,tablespace_name,global_stats,partitioned
  2  from user_indexes where index_name='BIG_TABLE_PK';

INDEX_NAME                     INDEX_TYPE                  TABLESPACE_NAME                GLO PAR
------------------------------ --------------------------- ------------------------------ --- ---
BIG_TABLE_PK                   NORMAL                      TBS_TMP                        YES NO

SQL> set autot trace exp;
SQL> select * from big_table where id=1412;                                                                        

Execution Plan
----------------------------------------------------------
Plan hash value: 2662411593                                                                                        

-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name         | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |              |     1 |    62 |     2   (0)| 00:00:01 |       |       |
|   1 |  TABLE ACCESS BY GLOBAL INDEX ROWID| BIG_TABLE    |     1 |    62 |     2   (0)| 00:00:01 | ROWID | ROWID |
|*  2 |   INDEX UNIQUE SCAN                | BIG_TABLE_PK |     1 |       |     1   (0)| 00:00:01 |       |       |
-------------------------------------------------------------------------------------------------------------------
--如上，在其执行计划中，Pstart与Pstop都为ROWID
--出现了GLOBAL INDEX ROWID，我们添加主键时并未指定Global，但其执行计划表明执行了全局索引访问
--这个地方有待证实，对于分区表，非分区键上的主键或唯一索引是否一定是全局索引

SQL> drop index bita_created_date_i;

--下面创建全局索引，创建时需要指定分区键的范围和值
SQL> CREATE INDEX bita_created_date_i
   ON big_table (created_date)
   GLOBAL PARTITION BY RANGE (created_date)
      (
         PARTITION
            idx_1 VALUES LESS THAN (TO_DATE ('01/01/2013', 'DD/MM/YYYY'))
            TABLESPACE idx1,
         PARTITION
            idx_2 VALUES LESS THAN (TO_DATE ('01/01/2014', 'DD/MM/YYYY'))
            TABLESPACE idx2,
         PARTITION idx_3 VALUES LESS THAN (maxvalue) TABLESPACE idx3);

SQL> select index_name, partitioning_type, partition_count,locality from user_part_indexes;

INDEX_NAME                     PARTITI PARTITION_COUNT LOCALI
------------------------------ ------- --------------- ------
BITA_CREATED_DATE_I_G          RANGE                 3 GLOBAL

SQL> select partition_name, high_value, tablespace_name from user_ind_partitions;

PARTITION_NAME                 HIGH_VALUE             TABLESPACE_NAME
------------------------------ --------------------- ------------------------------
IDX_1                          TO_DATE(' 2013-01-01  IDX1
IDX_2                          TO_DATE(' 2014-01-01  IDX2
IDX_3                          MAXVALUE              IDX3  

--下面是其执行计划，可以看出支持分区消除
SQL> set autot trace exp;
SQL> select * from big_table where created_date=to_date('20130625','yyyymmdd');

Execution Plan
----------------------------------------------------------
Plan hash value: 1378264218

---------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                     |     1 |    41 |     2   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE SINGLE             |                     |     1 |    41 |     2   (0)| 00:00:01 |     2 |     2 |
|   2 |   TABLE ACCESS BY GLOBAL INDEX ROWID| BIG_TABLE           |     1 |    41 |     2   (0)| 00:00:01 |     2 |     2 |
|*  3 |    INDEX RANGE SCAN                 | BITA_CREATED_DATE_I |     1 |       |     1   (0)| 00:00:01 |     2 |     2 |
---------------------------------------------------------------------------------------------------------------------------

--以下为范围查询，Pstart为1，Pstop为2，同样支持分区消除
SQL> select * from big_table
  2  where created_date>=to_date('20120625','yyyymmdd') and created_date<=to_date('20130625','yyyymmdd');

Execution Plan
----------------------------------------------------------
Plan hash value: 213633793

------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name      | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |           |  3334 |   133K|    14   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE ITERATOR|           |  3334 |   133K|    14   (0)| 00:00:01 |     1 |     2 |
|*  2 |   TABLE ACCESS FULL      | BIG_TABLE |  3334 |   133K|    14   (0)| 00:00:01 |     1 |     2 |
------------------------------------------------------------------------------------------------------

相关参考：

使用DBMS_REDEFINITION在线切换普通表到分区表使用exchange方式切换普通表到分区表Oracle 分区表导入导出 Oracle 分区表数据

使用导出导入(datapump)方式将普通表切换为分区表

时间： 2024-10-18 06:02:30

Oracle 分区索引的相关文章

数据-oracle 分区索引效率问题

问题描述 oracle 分区索引效率问题 有A,B两张表,都加了range的分区索引,表空间什么都是一样的,在同一个分区下面 A的数据量和B的数据量是一样多的,但是查询的时候A的查询效率要慢很多,请问大家能帮我分析一下还有什么原因会导致这个结果吗?谢谢 解决方案 信息量实在太少,据你所说,AB两表都一样,那完全没道理查询效率不同.建议提供更详细的资料,如AB表的建表语句. 还有就是从sql角度出发,对sql进行explain

Oracle 分区索引介绍和实例演示_oracle

分区索引(或索引分区)主要是针对分区表而言的.随着数据量的不断增长,普通的堆表需要转换到分区表,其索引呢,则对应的转换到分区索引.分区索引的好处是显而易见的.就是简单地把一个索引分成多个片断,在获取所需数据时,只需要访问更小的索引片断(块)即可实现.同时把分区放在不同的表空间可以提高分区的可用性和可靠性.本文主要描述了分区索引的相关特性并给出演示示例. 1.分区索引的相关概念 a.分区索引的几种方式:表被分区而索引未被分区:表未被分区,而索引被分区:表和索引都被分区 b.分区索引可以分为本地分区

深入oracle分区索引的详解_oracle

表可以按range.hash.list分区,表分区后,其上的索引和普通表上的索引有所不同,oracle对于分区表上的索引分为2类,即局部索引和全局索引,下面分别对这2种索引的特点和局限性做个总结.局部索引local index1.局部索引一定是分区索引,分区键等同于表的分区键,分区数等同于表的分区数,一句话,局部索引的分区机制和表的分区机制一样.2.如果局部索引的索引列以分区键开头,则称为前缀局部索引.3.如果局部索引的列不是以分区键开头,或者不包含分区键列,则称为非前缀索引.4.局部索引只能依

Oracle Local VS Global分区索引简介

在Oracle中,索引和表一样也可以分区.有两种类型的分区索引,本地分区索引(Local )和全局分区索引(Global). 1.本地索引(Local) 本地分区索引使用 LOCAL关键字创建,其分区边界与表相同(即与每个表分区相关联都有一个索引分区),下面 是一个本地分区索引的例子: create table sales_par partitioned by range (year) ( partition p_2009 values less than (2010) partition p_

Oracle中一个通过添加本地分区索引提高SQL性能的案例

今天接到同事求助,说有一个select query,在Oracle上要跑一分多钟,他希望能在5s内 出结果,该sql如下: Select /*+ parallel(src, 8) */ distinct src.systemname as systemname , src.databasename as databasename , src.tablename as tablename , src.username as username from <strong>meta_dbql_tabl

Oracle分区（2） 分区索引

可能很多初学者和我一样,一开始以为只要在分区表上创建的索引就是分区索引,其实不然,索引 是否分区和表是否分区没有必然的关系,表分区索引可以分区也可以不分区,甚至表不分区索引也可 以分区(但很少会这么定义),因此分区索引比分区表要复杂的多. 分区索引主要分为本地分区索引和全局分区索引,本地索引又分为前缀索引和非前缀索引,本文主 要探讨它们的区别. 本地分区索引 本地分区索引是指索引的分区键.分区方式和基表的分区方式一模一样,如下图所示: 本地分区索引具有如下基本特征: 1. 本地索引一定是分区索引

SQL Server已分区索引的特殊指导原则（3）

一.前言 在MSDN上看到一篇关于SQL Server 表分区的文档:已分区索引的特殊指导原则,如果你对表分区没有实战经验的话是比较难理解文档里面描述的意思.这里我就里面的一些概念进行讲解,方便大家的交流. SQL Server 解读[已分区索引的特殊指导原则](1)- 索引对齐 SQL Server 解读[已分区索引的特殊指导原则](2)- 唯一索引分区 二.解读 [对非聚集索引进行分区] "对唯一的非聚集索引进行分区时,索引键必须包含分区依据列.对非唯一的非聚集索引进行分区时,默认情况下 S

Oracle分区（1）分区剪枝

分区剪枝 分区剪枝是指对于分区表或分区索引来说,优化器可以自动从FROM和WHERE字句里根据分区键提取 出需要扫描的分区,从而避免全表扫描,减少扫描的数据块,提高性能.分区剪枝分为静态和动态, 静态分区剪枝发生在编译阶段,动态分区剪枝发生在执行阶段,下面我们分别来看看这两种分区剪枝 执行计划的异同点. 静态分区剪枝 静态分区剪枝在解析阶段就知道需要扫描多少个分区,因此执行计划里的PSTART和PSTOP明确显示 扫描的起止分区数,例如: SQL> explain plan for select

oracle的分区索引和全局索引

分区索引就是在所有每个区上单独创建索引,它能自动维护,在drop或truncate某个分区时不影响该索引的其他分区索引的使用,也就是索引不会失效,维护起来比较方便,但是在查询性能稍微有点影响. 建立分区索引: create index idx_ta_c2 on ta(c2) local (partition p1,partition p2,partition p3,partition p4); 或者 create index idx_ta_c2 on ta(c2) local ; 另外在crea