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
--从下面的查询可知，索引直接存放到分表表对应的表空间 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 | ------------------------------------------------------------------------------------------------------
-
本文向大家介绍Oracle 阻塞(blocking blocked)介绍和实例演示，包括了Oracle 阻塞(blocking blocked)介绍和实例演示的使用技巧和注意事项，需要的朋友参考一下 阻塞是DBA经常碰到的情形，尤其是不良的应用程序设计的阻塞将导致性能严重下降直至数据库崩溃。对DBA而言，有必要知道如何定位到当前系统有哪些阻塞，到底谁是阻塞者，谁是被阻塞者。本文对此给出了描述并做了
-
本文向大家介绍Oracle索引质量介绍和分析脚本分享，包括了Oracle索引质量介绍和分析脚本分享的使用技巧和注意事项，需要的朋友参考一下 索引质量的高低对数据库整体性能有着直接的影响。良好高质量的索引使得数据库性能得以数量级别的提升，而低效冗余的索引则使得数据库性能缓慢如牛，即便是使用高档的硬件配置。因此对于索引在设计之初需要经过反复的测试与考量。那对于已经置于生产环境中的数据库，我们也可以通过
-
本文向大家介绍Oracle RAC环境下的阻塞(blocking blocked)介绍和实例演示，包括了Oracle RAC环境下的阻塞(blocking blocked)介绍和实例演示的使用技巧和注意事项，需要的朋友参考一下 RAC环境下的阻塞不同于单实例情形，因为我们需要考虑到位于不同实例的session。也就是说之前查询的v$session，v$lock相应的应变化为全局范围来查找。本文提供
-
本文向大家介绍oracle索引介绍(图文详解)，包括了oracle索引介绍(图文详解)的使用技巧和注意事项，需要的朋友参考一下 对于数据库来说，索引是一个必选项，但对于现在的各种大型数据库来说，索引可以大大提高数据库的性能，以至于它变成了数据库不可缺少的一部分。 索引分类： 逻辑分类 single column or concatenated 对一列或多列建所引 unique or non
-
本文向大家介绍C#索引器介绍，包括了C#索引器介绍的使用技巧和注意事项，需要的朋友参考一下 索引器是一种特殊的类成员，它能够让对象以类似数组的方式来存取，使程序看起来更为直观，更容易编写。 1、索引器的定义 C#中的类成员可以是任意类型，包括数组和集合。当一个类包含了数组和集合成员时，索引器将大大简化对数组或集合成员的存取操作。 定义索引器的方式与定义属性有些类似，其一般形式如下： 修饰符包括 p
-
本文向大家介绍MySQL索引之聚集索引介绍，包括了MySQL索引之聚集索引介绍的使用技巧和注意事项，需要的朋友参考一下 在MySQL里，聚集索引和非聚集索引分别是什么意思，有什么区别? 在MySQL中，InnoDB引擎表是(聚集)索引组织表(clustered index organize table)，而MyISAM引擎表则是堆组织表(heap organize table)。 也有人把聚集索引