oracle怎么建btree索引,全面解析11GR2中的BTree索引（含视频）

本帖最后由 zcs0237 于 2013-7-29 18:49 编辑

一、局部分区索引有分区维护容易，平时用的最多(位图索引只能为局部分区索引)

1、基于分区表创建。

2、每个索引分区对应一个表分区。

3、又可以分为本地前缀索引和本地非前缀索引。

如果局部索引的索引列以分区键开头，则称为前缀局部索引。

如果局部索引的列不是以分区键开头，或者不包含分区键列，则称为非前缀索引。

二、全局分区索引主要是维护麻烦，不太建议使用(跨区查询等一些特殊情况下有些优势)

1、基于非分区表或分区表创建

2、全局索引的分区键和分区数和表的分区键和分区数可能都不相同

3、一个索引分区能指向n个表分区，同时，一个表分区，也可能指向n个索引分区

09.01-范围分区表(分区表共四类：范围+散列+列表+复合)

一、创建表空间

SYS@zcs11g>   conn / as sysdba

set sqlprompt "_user'@zcs'11g> "

drop tablespace ZZ11  INCLUDING CONTENTS and datafiles;

drop tablespace ZZ12  INCLUDING CONTENTS and datafiles;

drop tablespace ZZ13  INCLUDING CONTENTS and datafiles;

drop tablespace ZZ11i  INCLUDING CONTENTS and datafiles;

drop tablespace ZZ12i  INCLUDING CONTENTS and datafiles;

drop tablespace ZZ13i  INCLUDING CONTENTS and datafiles;

select '--'||name from v$tablespace where name like '%ZZ%' order by name;

-- no rows selected

SYS@zcs11g>   create tablespace zz11  datafile  '/tmp/zz11.dbf' size 99M reuse uniform size 3M;

create tablespace zz12  datafile  '/tmp/zz12.dbf' size 99M reuse uniform size 3M;

create tablespace zz13  datafile  '/tmp/zz13.dbf' size 99M reuse uniform size 3M;

create tablespace zz11i  datafile  '/tmp/zz11i.dbf' size 99M reuse uniform size 3M;

create tablespace zz12i  datafile  '/tmp/zz12i.dbf' size 99M reuse uniform size 3M;

create tablespace zz13i  datafile  '/tmp/zz13i.dbf' size 99M reuse uniform size 3M;

select '--'||name from v$tablespace where name like '%ZZ%' order by name;

/

--ZZ11

--ZZ11I

--ZZ12

--ZZ12I

--ZZ13

--ZZ13I

二、创建范围分区表

1、创建分区表

SYS@zcs11g>   create table t1_part

(id number(6),date1  date) partition by range(date1)

( partition p2011 values less than (to_date('01/01/2012','DD/MM/YYYY')) tablespace zz11

, partition p2012 values less than (to_date('01/01/2013','DD/MM/YYYY')) tablespace zz12) ;

set pagesize 0

select '--'||partitioning_type,subpartitioning_type,status from all_part_tables where table_name='T1_PART';

--RANGE     NONE      VALID

2、表引分的大小

SYS@zcs11g> select '--',partition_name,bytes/1024/1024||'MB' from dba_segments where segment_name='T1_PART';

--P2012                          3MB

--P2011                          3MB

09.02-创建本地(唯一)分区索引

一、为分区表添加分区索引

SYS@zcs11g>  drop index t1_part_idx;

create unique index t1_part_uk ON t1_part(id,date1)

local (partition p2011i  tablespace zz11i,partition p2012i  tablespace zz12i);

select '--',p.partition_name,p.status from user_ind_partitions p,user_indexes i where i.index_name='T1_PART_UK' and  i.index_name=p.index_name;

-- P2011I    USABLE

-- P2012I    USABLE

二、索引名称、索引类型、是否前缀分区

SYS@zcs11g>  select '--',partitioning_type,locality,alignment from user_part_indexes where index_name='T1_PART_UK';

-- RANGE     LOCAL  NON_PREFIXED

三、把索引设为unusable再rebuild(相比drop简楷掉了create index语句)

1、设为unusable

SYS@zcs11g>  alter index t1_part_uk modify partition p2011i unusable;

select '--',partition_name,status from all_ind_partitions where index_name='T1_PART_UK';

-- P2011I    UNUSABLE

-- P2012I    USABLE

2、rebuild本地分区索引

SYS@zcs11g>  alter index  t1_part_uk  rebuild partition p2011i;

select '--',partition_name,status from all_ind_partitions where index_name='T1_PART_UK';

--T1_PART_PK                     P2011I    USABLE

--T1_PART_PK                     P2012I    USABLE

09.03-表级分区改变时本地索引分区是否依然usable

一、添加表级分区——索引不受影响

1、添加表级分区：索引级分区自动添加

SYS@zcs11g>  alter table t1_part add partition p2013 values less than (to_date('01/01/2014','DD/MM/YYYY')) tablespace zz13i;

select '--',partition_name,status from all_ind_partitions where index_name='T1_PART_UK';

-- P2011I                         USABLE

-- P2012I                         USABLE

-- P2013                          USABLE

2、为新的表级索引改名

SYS@zcs11g>  alter index T1_PART_UK rename partition P2013 to P2013i;

select '--',partition_name,status from all_ind_partitions where index_name='T1_PART_UK';

-- P2011I                         USABLE

-- P2012I                         USABLE

-- P2013I                         USABLE

二、截断表级分区——索引不受影响

1、准备：添加数据到表级分区

SYS@zcs11g>  insert into t1_part values(131,(TO_DATE('2013-06-06', 'SYYYY-MM-DD')));

insert into t1_part values(132,(TO_DATE('2013-08-08', 'SYYYY-MM-DD')));

insert into t1_part values(121,(TO_DATE('2012-06-06', 'SYYYY-MM-DD')));

insert into t1_part values(122,(TO_DATE('2012-08-08', 'SYYYY-MM-DD')));

insert into t1_part values(111,(TO_DATE('2011-06-06', 'SYYYY-MM-DD')));

insert into t1_part values(112,(TO_DATE('2011-08-08', 'SYYYY-MM-DD')));

2、截断表级分区

SYS@zcs11g>  select '--'||partition_name from  DBA_TAB_PARTITIONS  where TABLE_NAME='T1_PART';

--P2011

--P2012

--P2013

SYS@zcs11g>  alter table t1_part truncate partition p2013;

3、对索引级分区的影响

SYS@zcs11g>  select '--',partition_name,status from all_ind_partitions where index_name ='T1_PART_UK';

-- P2011I    USABLE

-- P2012I    USABLE

-- P2013I    USABLE

三、拆分表级分区

1、查看分区范围

SYS@zcs11g>  set linesize 999

col high_value for a333

col partition_name for a9

select '--',partition_name,high_value from dba_tab_partitions where table_name='T1_PART';

-- P2011     TO_DATE(' 2012-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

-- P2012     TO_DATE(' 2013-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

-- P2013     TO_DATE(' 2014-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

2、表级分区拆分出小范围子分区对索引级分区的影响(结果：07/01/2013=上半年数据分拆到p2013_1子分区中)

SYS@zcs11g>  alter table t1_part split partition p2013 at (to_date('07/01/2013','DD/MM/YYYY')) into (partition p2013_1 tablespace users,partition p2013 tablespace users);

select '--',partition_name,status from all_ind_partitions where index_name='T1_PART_UK';

-- P2011I    USABLE

-- P2012I    USABLE

-- P2013I    USABLE

-- P2013_1   USABLE

四、移动表级分区——索引不受影响

1、表级分区的位置

SYS@zcs11g>  select '--'||partition_name,tablespace_name from dba_tab_partitions where table_name='T1_PART';

--P2011                          ZZ11

--P2012                          ZZ12

--P2013_1                        USERS

--P2013                          USERS

2、移动表级分区对索引级分区的影响——依然usable

SYS@zcs11g>  alter table t1_part move partition p2013 tablespace zz13;

select '--',partition_name,status from all_ind_partitions where index_name='T1_PART_UK';

-- P2011I    USABLE

-- P2012I    USABLE

-- P2013I    USABLE

-- P2013_1   USABLE

五、合并表级分区——索引不受影响(与DarlKuhn的书中说法不致)

SYS@zcs11g>  col tablespace_name for a6

set linesize 999

select '--'||partition_name,high_value from dba_tab_partitions where table_name='T1_PART';

--P2011  TO_DATE(' 2012-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

--P2012  TO_DATE(' 2013-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

--P2013_1  TO_DATE(' 2013-01-07 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

--P2013    TO_DATE(' 2014-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

SYS@zcs11g> alter table t1_part merge partitions p2013_1,p2013 into partition p2013_1;

--ORA-14275: cannot reuse lower-bound partition as resulting partition

SYS@zcs11g>  alter table t1_part merge partitions p2013_1,p2013 into partition p2013;

select '--',partition_name,status from all_ind_partitions where index_name='T1_PART_UK';

-- P2011I    USABLE

-- P2012I    USABLE

-- P2013I    USABLE

SYS@zcs11g>  col tablespace_name for a6

select '--' ,partition_name,tablespace_name,high_value from dba_tab_partitions where table_name='T1_PART';

--P2011    ZZ11   TO_DATE(' 2012-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

--P2012    ZZ12   TO_DATE(' 2013-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

--P2013    SYSTEM TO_DATE(' 2014-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

六、表级分区所在表空间只读——索引分区也只读，依然usable

SYS@zcs11g>  alter tablespace zz12 read only;

select '--',partition_name,status from all_ind_partitions where index_name='T1_PART_UK';

--T1_PART_PK                     P2011I                         USABLE

--T1_PART_PK                     P2012IUSABLE

--T1_PART_PK                     P2013I                         USABLE

七、表级分区删除——索引级分区依然unusable

SYS@zcs11g>  alter table t1_part drop partition p2011;

select '--',partition_name,status from all_ind_partitions where index_name='T1_PART_UK';

-- P2012I    USABLE

-- P2013I    USABLE

八、清理现场

SYS@zcs11g>  drop table t1_part purge;