Oracle 分区索引介绍和实例演练
发布时间：2021-12-27 13:29:58 所属栏目：语言 来源：互联网
导读：分区索引(或索引分区)主要是针对分区表而言的。随着数据量的不断增长，普通的堆表需要转换到分区表，其索引呢，则对应的转换到分区索引。分区索引的好处是显而易见的。就是简单地把一个索引分成多个片断，在获取所需数据时，只需要访问更小的索引片断(块)即可
|
分区索引(或索引分区)主要是针对分区表而言的。随着数据量的不断增长，普通的堆表需要转换到分区表，其索引呢，则对应的转换到分区索引。分区索引的好处是显而易见的。就是简单地把一个索引分成多个片断，在获取所需数据时，只需要访问更小的索引片断(块)即可实现。同时把分区放在不同的表空间可以提高分区的可用性和可靠性。本文主要描述了分区索引的相关特性并给出演示示例。 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; （编辑：晋中站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
