Oracle 维护分区表的索引

维护分区表的索引
虽然分区表和分区索引提供了许多好处,但在设计应用程序时就必须考虑到,创建分区表和分区索引对维护产生的影响。根据环境的情况和具体的应用程序设计和数据库设计,维护操作也会有所不同。对于你的具体应用程序,这包括应用程序的DML模式和被插入,更新和删除的数据量。索引的存在会造成DML操作速度减慢。在某些应用程序中,特别是在数据仓库领域,执行分区一级的操作有利于加快应用程序整体处理速度。分区级的操作可能会对索引产生重大影响,这取决于应用程序使用了什么类型的索引。

下面的例子展示了分区级的操作对不同类型索引的影响。每个例子使用一个测试表,其中包含员工数据。此表上有三个索引:一个非分区索引(employees_part_i1),一个本地分区索引(employees_part_li1)和一个全局分区索引(employees_parttest_gi1)。你会看到对表执行分区级别的操作对表内每个索引的实际影响。

注意:如果在分区表任何一个空的分区上执行下列操作,所有相关索引均不受影响。这对各种索引类型都是成立的,不管它们是本地分区,全局分区还是非分区的索引。

SQL> create table employees_part
  2  (
  3  employee_id number(6) not null,
  4  first_name varchar2(20),
  5  last_name varchar2(25) not null,
  6  email varchar2(25) not null,
  7  phone_numbr varchar2(20),
  8  hire_date date not null,
  9  job_id varchar2(10) not null,
 10  salary number(8,2),
 11  commission_pct number(2,2),
 12  manager_id number(6),
 13  department_id number(4),
 14  constraint employees_part_pk primary key(employee_id,hire_date)
 15  )
 16  partition by range(hire_date)
 17  (
 18  partition p1990 values less than(to_date('1991-01-01','yyyy-mm-dd')),
 19  partition p1991 values less than(to_date('1992-01-01','yyyy-mm-dd')),
 20  partition p1992 values less than(to_date('1993-01-01','yyyy-mm-dd')),
 21  partition p1993 values less than(to_date('1994-01-01','yyyy-mm-dd')),
 22  partition p1994 values less than(to_date('1995-01-01','yyyy-mm-dd')),
 23  partition p1995 values less than(to_date('1996-01-01','yyyy-mm-dd')),
 24  partition p1996 values less than(to_date('1997-01-01','yyyy-mm-dd')),
 25  partition p1997 values less than(to_date('1998-01-01','yyyy-mm-dd')),
 26  partition p1998 values less than(to_date('1999-01-01','yyyy-mm-dd')),
 27  partition p1999 values less than(to_date('2000-01-01','yyyy-mm-dd')),
 28  partition p2000 values less than(to_date('2001-01-01','yyyy-mm-dd')),
 29  partition pmax values less than(maxvalue)
 30  );

Table created.

SQL> create index employee_part_li on employees_part(hire_date) local;

Index created.

SQL> select partition_name,status from user_ind_partitions where index_name='EMPLOYEE_PART_LI';

PARTITION_NAME                                                                                                                   STATUS
-------------------------------------------------------------------------------------------------------------------------------- --------
P1990                                                                                                                            USABLE
P1991                                                                                                                            USABLE
P1992                                                                                                                            USABLE
P1993                                                                                                                            USABLE
P1994                                                                                                                            USABLE
P1995                                                                                                                            USABLE
P1996                                                                                                                            USABLE
P1997                                                                                                                            USABLE
P1998                                                                                                                            USABLE
P1999                                                                                                                            USABLE
P2000                                                                                                                            USABLE
PMAX                                                                                                                             USABLE

12 rows selected.

1 添加分区
往表中添加分区对表上现有的索引影响最小。事实上,无论你选择使用什么索引类型(本地分区,全局分区或非分区),在添加分区操作后,任何现存的索引分区都不会受到影响,也不需要特定的索引级别的操作。下面的例子向测试员工表中添加一个分区,用于保存2001年的历史数据:

SQL> alter table employees_part add partition p2001 values less than(to_date('2002-01-01','yyyy-mm-dd'));
alter table employees_part add partition p2001 values less than(to_date('2002-01-01','yyyy-mm-dd'))
                                         *
ERROR at line 1:
ORA-14074: partition bound must collate higher than that of the last partition


SQL> alter table employees_part drop partition pmax;

Table altered.

SQL> select partition_name,status from user_ind_partitions where index_name='EMPLOYEE_PART_LI';

PARTITION_NAME                                                                                                                   STATUS
-------------------------------------------------------------------------------------------------------------------------------- --------
P1990                                                                                                                            USABLE
P1991                                                                                                                            USABLE
P1992                                                                                                                            USABLE
P1993                                                                                                                            USABLE
P1994                                                                                                                            USABLE
P1995                                                                                                                            USABLE
P1996                                                                                                                            USABLE
P1997                                                                                                                            USABLE
P1998                                                                                                                            USABLE
P1999                                                                                                                            USABLE
P2000                                                                                                                            USABLE

11 rows selected.

SQL> alter table employees_part add partition p2001 values less than(to_date('2002-01-01','yyyy-mm-dd'));

Table altered.

添加分区后,可以运行以下查询来确实添加分区(add partition)操作对表上现有索引的影响:

SQL> select index_name,null partition_name,status from user_indexes where table_name='EMPLOYEES_PART' and partitioned='NO'
  2  union
  3  select index_name,partition_name,status from user_ind_partitions where index_name in
  4  (select index_name from user_indexes where table_name='EMPLOYEES_PART')
  5  order by 1,2,3;

INDEX_NAME                     PARTITION_NAME                           STATUS
------------------------------ ---------------------------------------- --------
EMPLOYEES_PART_PK                                                       VALID
EMPLOYEE_PART_LI               P1990                                    USABLE
EMPLOYEE_PART_LI               P1991                                    USABLE
EMPLOYEE_PART_LI               P1992                                    USABLE
EMPLOYEE_PART_LI               P1993                                    USABLE
EMPLOYEE_PART_LI               P1994                                    USABLE
EMPLOYEE_PART_LI               P1995                                    USABLE
EMPLOYEE_PART_LI               P1996                                    USABLE
EMPLOYEE_PART_LI               P1997                                    USABLE
EMPLOYEE_PART_LI               P1998                                    USABLE
EMPLOYEE_PART_LI               P1999                                    USABLE
EMPLOYEE_PART_LI               P2000                                    USABLE
EMPLOYEE_PART_LI               P2001                                    USABLE

13 rows selected.

2 截断分区
截断分区是删除一个表分区内所有数据的最简单的方法。而且,对于创建了本地分区索引的表,截断表分区不影响底层的索引分区,其中包括被截断的分区。然而,如果你的表上有非分区索引或者全局分区索引,截断分区就会使得Oracle无法知道哪些索引条目受到了截断操作的影响。因此,Oracle别无选择,只能简单地把整个索引标记为unusable。例如:

SQL> create index employees_part_uk1 on employees_part(manager_id)
  2  global
  3  partition by range(manager_id)
  4  (
  5  partition manager_100 values less than(100),
  6  partition manager_200 values less than(200),
  7  partition manager_300 values less than(300),
  8  partition manager_400 values less than(400),
  9  partition manager_500 values less than(500),
 10  partition manager_600 values less than(600),
 11  partition manager_700 values less than(700),
 12  partition manager_800 values less than(800),
 13  partition manager_900 values less than(900),
 14  partition manager_max values less than(maxvalue)
 15  );

Index created.

SQL> alter table employees_part truncate partition p1995;

Table truncated.

SQL> select index_name,null partition_name,status from user_indexes where table_name='EMPLOYEES_PART' and partitioned='NO'
  2  union
  3  select index_name,partition_name,status from user_ind_partitions where index_name in
  4  (select index_name from user_indexes where table_name='EMPLOYEES_PART')
  5  order by 1,2,3;

INDEX_NAME                     PARTITION_NAME                           STATUS
------------------------------ ---------------------------------------- --------
EMPLOYEES_PART_PK                                                       VALID
EMPLOYEES_PART_UK1             MANAGER_100                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_200                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_300                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_400                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_500                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_600                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_700                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_800                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_900                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_MAX                              UNUSABLE
EMPLOYEE_PART_LI               P1990                                    USABLE
EMPLOYEE_PART_LI               P1991                                    USABLE
EMPLOYEE_PART_LI               P1992                                    USABLE
EMPLOYEE_PART_LI               P1993                                    USABLE
EMPLOYEE_PART_LI               P1994                                    USABLE
EMPLOYEE_PART_LI               P1995                                    USABLE
EMPLOYEE_PART_LI               P1996                                    USABLE
EMPLOYEE_PART_LI               P1997                                    USABLE
EMPLOYEE_PART_LI               P1998                                    USABLE
EMPLOYEE_PART_LI               P1999                                    USABLE
EMPLOYEE_PART_LI               P2000                                    USABLE
EMPLOYEE_PART_LI               P2001                                    USABLE

23 rows selected.

3 移动分区
移动分区有各种原因。你可能需要将一些表或分区移动到不同的表空间,也可能会决定压缩某个分区的数据,或者可能因为大量的更新操作已经导致了行迁移而需要重组表的分区。

执行移动分区操作时,你会再次看到本地分区索引受到的影响是最小的。本地分区索引的分区级移动操作与截断操作的一个关键的区别在于,被移动分区的索引已被标记为unusable。因为移动分区中的每一行后,现在每一行的rowid值都与先前的不同,所以Oracle需要这么做,因此该索引分区的条目现在是无效的,因为它们现在包含过时的rowid项。被移动的索引分区需要重建,以反映新的rowid值。

与截断分区的例子一样,Oracle不了解该表上的全局分区和非分区索引的表分区边界,因此需要把整个索引标志为unusable,且整个索引都需要重建安。对于本地分区索引,只需要重建受移动的表分区影响的索引分区。

在下面的例子中,需要把较旧的1995年的员工数据移动到它自己的表空间,这可能是为了能把它设置成只读:

SQL> alter table employees_part move partition p1995 tablespace reporting_index;

Table altered.

SQL> select index_name,null partition_name,status from user_indexes where table_name='EMPLOYEES_PART' and partitioned='NO'
  2  union
  3  select index_name,partition_name,status from user_ind_partitions where index_name in
  4  (select index_name from user_indexes where table_name='EMPLOYEES_PART')
  5  order by 1,2,3;

INDEX_NAME                     PARTITION_NAME                           STATUS
------------------------------ ---------------------------------------- --------
EMPLOYEES_PART_PK                                                       VALID
EMPLOYEES_PART_UK1             MANAGER_100                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_200                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_300                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_400                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_500                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_600                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_700                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_800                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_900                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_MAX                              UNUSABLE
EMPLOYEE_PART_LI               P1990                                    USABLE
EMPLOYEE_PART_LI               P1991                                    USABLE
EMPLOYEE_PART_LI               P1992                                    USABLE
EMPLOYEE_PART_LI               P1993                                    USABLE
EMPLOYEE_PART_LI               P1994                                    USABLE
EMPLOYEE_PART_LI               P1995                                    UNUSABLE
EMPLOYEE_PART_LI               P1996                                    USABLE
EMPLOYEE_PART_LI               P1997                                    USABLE
EMPLOYEE_PART_LI               P1998                                    USABLE
EMPLOYEE_PART_LI               P1999                                    USABLE
EMPLOYEE_PART_LI               P2000                                    USABLE
EMPLOYEE_PART_LI               P2001                                    USABLE

23 rows selected.

4 拆分分区
拆分分区表的分区,通常是因为它不再符合应用程序的要求,或出于维护的原因需要拆分。最常见的原因之一是,需要简单地往表中添加一个分区,而它不是表最高端的分区,正因为如此,这需要通过拆分分区,而不是添加分区来实现。

下面的例子拆分了maxvalue分区以便为早于2003年1月的数据添加一个分区。对于本地分区索引,因为拆分操作触动了pmax分区,并创建一个新的分区(p2002),pmax分区中的行已经被拆分到两个分区中。因为一些数据可能从pmax表分区移动到新的p2002表分区,所以pmax分区的索引项是过时的。因此,在此拆分操作的情况下,这两个本地索引分区都被标记为unusable。由于新的分区以前不存在,其实没有现有的本地索引分区与它对应,所以Oracle自动创建一个索引分区,并把它标记为unusable。

与前面提到的截断和移动操作一样,拆分分区后,所有的全局分区和非分区索引全部被标记为unusable。

SQL> alter table employees_part add partition pmax values less than(maxvalue);

Table altered.

SQL> select index_name,null partition_name,status from user_indexes where table_name='EMPLOYEES_PART' and partitioned='NO'
  2  union
  3  select index_name,partition_name,status from user_ind_partitions where index_name in
  4  (select index_name from user_indexes where table_name='EMPLOYEES_PART')
  5  order by 1,2,3;

INDEX_NAME                     PARTITION_NAME                           STATUS
------------------------------ ---------------------------------------- --------
EMPLOYEES_PART_PK                                                       VALID
EMPLOYEES_PART_UK1             MANAGER_100                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_200                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_300                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_400                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_500                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_600                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_700                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_800                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_900                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_MAX                              UNUSABLE
EMPLOYEE_PART_LI               P1990                                    USABLE
EMPLOYEE_PART_LI               P1991                                    USABLE
EMPLOYEE_PART_LI               P1992                                    USABLE
EMPLOYEE_PART_LI               P1993                                    USABLE
EMPLOYEE_PART_LI               P1994                                    USABLE
EMPLOYEE_PART_LI               P1995                                    UNUSABLE
EMPLOYEE_PART_LI               P1996                                    USABLE
EMPLOYEE_PART_LI               P1997                                    USABLE
EMPLOYEE_PART_LI               P1998                                    USABLE
EMPLOYEE_PART_LI               P1999                                    USABLE
EMPLOYEE_PART_LI               P2000                                    USABLE
EMPLOYEE_PART_LI               P2001                                    USABLE
EMPLOYEE_PART_LI               PMAX                                     UNUSABLE

24 rows selected.



SQL> alter table employees_part split partition pmax at (to_date('2003-01-01','yyyy-mm-dd')) into
  2  (partition p2002 tablespace reporting_index,partition pmax tablespace reporting_index);

Table altered.

SQL> select index_name,null partition_name,status from user_indexes where table_name='EMPLOYEES_PART' and partitioned='NO'
  2  union
  3  select index_name,partition_name,status from user_ind_partitions where index_name in
  4  (select index_name from user_indexes where table_name='EMPLOYEES_PART')
  5  order by 1,2,3;

INDEX_NAME                     PARTITION_NAME                           STATUS
------------------------------ ---------------------------------------- --------
EMPLOYEES_PART_PK                                                       VALID
EMPLOYEES_PART_UK1             MANAGER_100                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_200                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_300                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_400                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_500                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_600                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_700                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_800                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_900                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_MAX                              UNUSABLE
EMPLOYEE_PART_LI               P1990                                    USABLE
EMPLOYEE_PART_LI               P1991                                    USABLE
EMPLOYEE_PART_LI               P1992                                    USABLE
EMPLOYEE_PART_LI               P1993                                    USABLE
EMPLOYEE_PART_LI               P1994                                    USABLE
EMPLOYEE_PART_LI               P1995                                    UNUSABLE
EMPLOYEE_PART_LI               P1996                                    USABLE
EMPLOYEE_PART_LI               P1997                                    USABLE
EMPLOYEE_PART_LI               P1998                                    USABLE
EMPLOYEE_PART_LI               P1999                                    USABLE
EMPLOYEE_PART_LI               P2000                                    USABLE
EMPLOYEE_PART_LI               P2001                                    USABLE
EMPLOYEE_PART_LI               P2002                                    UNUSABLE
EMPLOYEE_PART_LI               PMAX                                     UNUSABLE

25 rows selected.
< ?pre>
要强调的重要注意事项是,如果某个实质上往表中添加分区的分区拆分操作是必要的,因为需要一个高端分区来保存某种形式的默认数据,即使只是添加一个空分区的分区拆分操作,也可能会花费相当多的时间。因为Oracle需要分析现有的分区中的每一行,以便有效地执行正确的拆分。在这种情况下,对于指定用maxvalue指定了高端值的表,在高端分区以下,添加一个未被使用且始终为空的“虚拟”分区可能是有益的。那么,如果添加历史分区到表中,你可以始使用空的“虚拟”分区来拆分分区。这提供了两个关键的好处:首先,分区拆分操作会很快,因为没有需要分析的数据;其次,也没有索引将被标记为不可用,因为被拆分的分区没有数据,而新的分区也没有数据。

查看下面的创建表的DDL语句。对于employees_part表,员工离开了公司以后,他们的记录中的hire_date列被修改为maxvalue并留在表中作为历史记录。在这种情况下,pmax分区中将永远有数据。当你在表的pmax分区上执行分区拆分以添加今后几年的分区时,这么做总是会花时间的,而底层索引分区将标记为unusable。通过把p9999分区创建为“虚拟”分区,你绝不会把任何行添加到这个分区中。那么,当你拆分p9999分区来添加2001年及以后年份的数据分区时,它始终是在一个空的分区上进行拆分。因为拆分操作发生在空的分区上面,所以拆分操作将很快完成,而所有底层的本地索引分区将继续可用。
create table employees_part
(
employee_id number(6) not null,
first_name varchar2(20),
last_name varchar2(25) not null,
email varchar2(25) not null,
phone_numbr varchar2(20),
hire_date date not null,
job_id varchar2(10) not null,
salary number(8,2),
commission_pct number(2,2),
manager_id number(6),
department_id number(4),
constraint employees_part_pk primary key(employee_id,hire_date)
)
partition by range(hire_date)
(
partition p1990 values less than(to_date('1991-01-01','yyyy-mm-dd')),
partition p1991 values less than(to_date('1992-01-01','yyyy-mm-dd')),
partition p1992 values less than(to_date('1993-01-01','yyyy-mm-dd')),
partition p1993 values less than(to_date('1994-01-01','yyyy-mm-dd')),
partition p1994 values less than(to_date('1995-01-01','yyyy-mm-dd')),
partition p1995 values less than(to_date('1996-01-01','yyyy-mm-dd')),
partition p1996 values less than(to_date('1997-01-01','yyyy-mm-dd')),
partition p1997 values less than(to_date('1998-01-01','yyyy-mm-dd')),
partition p1998 values less than(to_date('1999-01-01','yyyy-mm-dd')),
partition p1999 values less than(to_date('2000-01-01','yyyy-mm-dd')),
partition p2000 values less than(to_date('2001-01-01','yyyy-mm-dd')),
partition p9999 values less than(to_date('9999-12-31','yyyy-mm-dd')),
partition pmax values less than(maxvalue)
);

5 交换分区
在数据仓库环境中,交换分区进行大批量数据加载特别常见。因为数据被加载到一个独立的表中,所以在加载操作期间读取操作不会受到影响。然后做一个分区交换,这实质上是对数据字典的修改,把一个独立的表段重新指向一个分区表的一部分,并把相关受影响的(原)表分区指向一个独立的表段。

分区交换与分区移动是类似的,只有受影响的本地分区索引的交换分区被标记为unusable。对于全局分区和非分区索引,你可以看到整个索引都已被标记为unusable。

SQL> alter table employees_part exchange partition p1995 with table employees_part_exch;

Table altered.


SQL> select index_name,null partition_name,status from user_indexes where table_name='EMPLOYEES_PART' and partitioned='NO'
  2  union
  3  select index_name,partition_name,status from user_ind_partitions where index_name in
  4  (select index_name from user_indexes where table_name='EMPLOYEES_PART')
  5  order by 1,2,3;

INDEX_NAME                     PARTITION_NAME                           STATUS
------------------------------ ---------------------------------------- --------
EMPLOYEES_PART_PK                                                       UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_100                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_200                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_300                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_400                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_500                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_600                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_700                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_800                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_900                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_MAX                              UNUSABLE
EMPLOYEE_PART_LI               P1990                                    USABLE
EMPLOYEE_PART_LI               P1991                                    USABLE
EMPLOYEE_PART_LI               P1992                                    USABLE
EMPLOYEE_PART_LI               P1993                                    USABLE
EMPLOYEE_PART_LI               P1994                                    USABLE
EMPLOYEE_PART_LI               P1995                                    UNUSABLE
EMPLOYEE_PART_LI               P1996                                    USABLE
EMPLOYEE_PART_LI               P1997                                    USABLE
EMPLOYEE_PART_LI               P1998                                    USABLE
EMPLOYEE_PART_LI               P1999                                    USABLE
EMPLOYEE_PART_LI               P2000                                    USABLE
EMPLOYEE_PART_LI               P2001                                    USABLE
EMPLOYEE_PART_LI               P2002                                    USABLE
EMPLOYEE_PART_LI               PMAX                                     USABLE

25 rows selected.

6 删除分区
删除分区,通常发生在基于日期或时间戳的分区表中,因为有些数据已经过期,数据库不再需要这些数据了。对于本地分区索引,删除某个分区对其余的本地索引分区没有任何影响。所有本地分区索引保持在可用(usable)状态。然而,对于全局范围内分区和非分区索引,将再次把整个索引标记为不可用,因为Oracle无法确定这些索引中的哪些行已通过删除分区操作被删除了。

SQL> alter table employees_part drop partition p1995;

Table altered.

SQL> select index_name,null partition_name,status from user_indexes where table_name='EMPLOYEES_PART' and partitioned='NO'
  2  union
  3  select index_name,partition_name,status from user_ind_partitions where index_name in
  4  (select index_name from user_indexes where table_name='EMPLOYEES_PART')
  5  order by 1,2,3;

INDEX_NAME                     PARTITION_NAME                           STATUS
------------------------------ ---------------------------------------- --------
EMPLOYEES_PART_PK                                                       UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_100                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_200                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_300                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_400                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_500                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_600                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_700                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_800                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_900                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_MAX                              UNUSABLE
EMPLOYEE_PART_LI               P1990                                    USABLE
EMPLOYEE_PART_LI               P1991                                    USABLE
EMPLOYEE_PART_LI               P1992                                    USABLE
EMPLOYEE_PART_LI               P1993                                    USABLE
EMPLOYEE_PART_LI               P1994                                    USABLE
EMPLOYEE_PART_LI               P1996                                    USABLE
EMPLOYEE_PART_LI               P1997                                    USABLE
EMPLOYEE_PART_LI               P1998                                    USABLE
EMPLOYEE_PART_LI               P1999                                    USABLE
EMPLOYEE_PART_LI               P2000                                    USABLE
EMPLOYEE_PART_LI               P2001                                    USABLE
EMPLOYEE_PART_LI               P2002                                    USABLE
EMPLOYEE_PART_LI               PMAX                                     USABLE

24 rows selected.

7 合并分区
合并分区操作本质上是分区拆分的逆操作,正如分区拆分操作,执行分区合并也是为了满足某些应用程序要求或是由于维护的原因。

下面的例子将把p2002和pmax分区全并成一个pmax分区。在这种情况下,两个分区中的所有行实质上被合并到一个新的分区。对于本地分区索引,它有一个匹配pmax的索引分区。因为分区合并改变了分区中行的组成,本地分区索引的pmax分区已被标记为unusable.

因为全局分区索引和非分区索引没有表分区组成的信息,所以整个索引又被标记为unusable.

SQL> select index_name,null partition_name,status from user_indexes where table_name='EMPLOYEES_PART' and partitioned='NO'
  2  union
  3  select index_name,partition_name,status from user_ind_partitions where index_name in
  4  (select index_name from user_indexes where table_name='EMPLOYEES_PART')
  5  order by 1,2,3;

INDEX_NAME                     PARTITION_NAME                           STATUS
------------------------------ ---------------------------------------- --------
EMPLOYEES_PART_PK                                                       UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_100                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_200                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_300                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_400                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_500                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_600                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_700                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_800                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_900                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_MAX                              UNUSABLE
EMPLOYEE_PART_LI               P1990                                    USABLE
EMPLOYEE_PART_LI               P1991                                    USABLE
EMPLOYEE_PART_LI               P1992                                    USABLE
EMPLOYEE_PART_LI               P1993                                    USABLE
EMPLOYEE_PART_LI               P1994                                    USABLE
EMPLOYEE_PART_LI               P1995                                    USABLE
EMPLOYEE_PART_LI               P1996                                    USABLE
EMPLOYEE_PART_LI               P1997                                    USABLE
EMPLOYEE_PART_LI               P1998                                    USABLE
EMPLOYEE_PART_LI               P1999                                    USABLE
EMPLOYEE_PART_LI               P2000                                    USABLE
EMPLOYEE_PART_LI               P2001                                    USABLE
EMPLOYEE_PART_LI               P2002                                    USABLE
EMPLOYEE_PART_LI               PMAX                                     USABLE

25 rows selected.


SQL> alter table employees_part merge partitions p2002,pmax into partition pmax;

Table altered.

SQL> select index_name,null partition_name,status from user_indexes where table_name='EMPLOYEES_PART' and partitioned='NO'
  2  union
  3  select index_name,partition_name,status from user_ind_partitions where index_name in
  4  (select index_name from user_indexes where table_name='EMPLOYEES_PART')
  5  order by 1,2,3;

INDEX_NAME                     PARTITION_NAME                           STATUS
------------------------------ ---------------------------------------- --------
EMPLOYEES_PART_PK                                                       UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_100                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_200                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_300                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_400                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_500                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_600                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_700                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_800                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_900                              UNUSABLE
EMPLOYEES_PART_UK1             MANAGER_MAX                              UNUSABLE

INDEX_NAME                     PARTITION_NAME                           STATUS
------------------------------ ---------------------------------------- --------
EMPLOYEE_PART_LI               P1990                                    USABLE
EMPLOYEE_PART_LI               P1991                                    USABLE
EMPLOYEE_PART_LI               P1992                                    USABLE
EMPLOYEE_PART_LI               P1993                                    USABLE
EMPLOYEE_PART_LI               P1994                                    USABLE
EMPLOYEE_PART_LI               P1995                                    USABLE
EMPLOYEE_PART_LI               P1996                                    USABLE
EMPLOYEE_PART_LI               P1997                                    USABLE
EMPLOYEE_PART_LI               P1998                                    USABLE
EMPLOYEE_PART_LI               P1999                                    USABLE
EMPLOYEE_PART_LI               P2000                                    USABLE

INDEX_NAME                     PARTITION_NAME                           STATUS
------------------------------ ---------------------------------------- --------
EMPLOYEE_PART_LI               P2001                                    USABLE
EMPLOYEE_PART_LI               PMAX                                     UNUSABLE

24 rows selected.

注意:对于表的大多数分区级操作,如果受影响的分区是空的,那么这种操作不会影响任何相关的索引。

Oracle 全局分区索引

全局分区索引实质上意味着该索引与数据表有不同的分区方案,且是基于不同于数据表的一个或一组分区列进行分区的。这主要是为了提高数据库中数据查询的性能。基于用户对给定表的查询,为提高查询性能,可以在给定的查询列上创建全局分区的索引。

可以创建以下几种类型的全局分区索引:
.范围
.哈希

为了说明基于范围的全局分区索引,假定你发现有很多的查询要访问employees表中的manager_id列,那么就可以在manager_id列上创建分区索引,它的分区完全独立于表。查看如下范围分区索引的例子:

SQL> create table employees_jy as select * from employees;

Table created.

SQL> create index employees_gi1
  2  on employees_jy(manager_id)
  3  global
  4  partition by range(manager_id)
  5  (
  6  partition manager_100 values less than(100),
  7  partition manager_200 values less than(200),
  8  partition manager_300 values less than(300),
  9  partition manager_400 values less than(400),
 10  partition manager_500 values less than(500),
 11  partition manager_600 values less than(600),
 12  partition manager_700 values less than(700),
 13  partition manager_800 values less than(800),
 14  partition manager_900 values less than(900),
 15  partition manager_max values less than(maxvalue)
 16  );

Index created.

你可以在分区表或非分区表上创建全局分区索引。换句话说,创建全局分区索引并不需要对表进行分区。正如本章后面所讨论的,因为分区表的全局分区索引维护起来不太方便,全局分区索引并不常用,所以通常很少甚至不用它来提高查询性能,特别是在非分区表中。

基于范围的全局分区索引,必须始终为索引指定一个最大值,即用maxvalue作为最大值。这保证了新插入到相应表的任何数据将在分局分区索引中有地方保存。换句话说,对于全局分区索引,它永远不会有“越界”的情况。如果不在全局分区索引中指定上限分区,Oracle将不允许创建索引,若执行下列语句,就会出现以下错误提示信息:

SQL> create index employees_gi2
  2  on employees_jy(manager_id)
  3  global
  4  partition by range(manager_id)
  5  (
  6  partition manager_100 values less than(100),
  7  partition manager_200 values less than(200),
  8  partition manager_300 values less than(300),
  9  partition manager_400 values less than(400),
 10  partition manager_500 values less than(500),
 11  partition manager_600 values less than(600),
 12  partition manager_700 values less than(700),
 13  partition manager_800 values less than(800),
 14  partition manager_900 values less than(900)
 15  );
)
*
ERROR at line 15:
ORA-14021: MAXVALUE must be specified for all columns

由于每个全局分区索引吕必须包含一个上限分区,因此把分区添加到全局分区索引的唯一方式就是,使用alter index … split分区命令。例如:

SQL> alter index employees_gi1 split partition manager_max at(1000) into (partition manager_max,partition manager_1000);

Index altered.


SQL> select partition_name,status from user_ind_partitions where index_name='EMPLOYEES_GI1';

PARTITION_NAME                                                                                                                   STATUS
-------------------------------------------------------------------------------------------------------------------------------- --------
MANAGER_100                                                                                                                      USABLE
MANAGER_1000                                                                                                                     USABLE
MANAGER_200                                                                                                                      USABLE
MANAGER_300                                                                                                                      USABLE
MANAGER_400                                                                                                                      USABLE
MANAGER_500                                                                                                                      USABLE
MANAGER_600                                                                                                                      USABLE
MANAGER_700                                                                                                                      USABLE
MANAGER_800                                                                                                                      USABLE
MANAGER_900                                                                                                                      USABLE
MANAGER_MAX                                                                                                                      USABLE

11 rows selected.

SQL>

还可以删除全局分区索引的部分分区。然而,这样做时,如果被删除的分区包含索引条目,上限比它高的相邻分区就被标记为不可用。在下面的代码中,查询了删除分区manager_200前该索引各分区的状态:

SQL> select partition_name,status from user_ind_partitions where index_name='EMPLOYEES_GI1';

PARTITION_NAME                                                                                                                   STATUS
-------------------------------------------------------------------------------------------------------------------------------- --------
MANAGER_100                                                                                                                      USABLE
MANAGER_1000                                                                                                                     USABLE
MANAGER_200                                                                                                                      USABLE
MANAGER_300                                                                                                                      USABLE
MANAGER_400                                                                                                                      USABLE
MANAGER_500                                                                                                                      USABLE
MANAGER_600                                                                                                                      USABLE
MANAGER_700                                                                                                                      USABLE
MANAGER_800                                                                                                                      USABLE
MANAGER_900                                                                                                                      USABLE
MANAGER_MAX                                                                                                                      USABLE

11 rows selected.

然后删除分区manager_200

SQL> alter index employees_gi1 drop partition manager_200;

Index altered.

当再次查询user_ind_partitions时,你可以看到数据库把上限更高的相邻分区标记为unusable状态。

SQL> select partition_name,status from user_ind_partitions where index_name='EMPLOYEES_GI1';

PARTITION_NAME                                                                                                                   STATUS
-------------------------------------------------------------------------------------------------------------------------------- --------
MANAGER_100                                                                                                                      USABLE
MANAGER_1000                                                                                                                     USABLE
MANAGER_300                                                                                                                      UNUSABLE
MANAGER_400                                                                                                                      USABLE
MANAGER_500                                                                                                                      USABLE
MANAGER_600                                                                                                                      USABLE
MANAGER_700                                                                                                                      USABLE
MANAGER_800                                                                                                                      USABLE
MANAGER_900                                                                                                                      USABLE
MANAGER_MAX                                                                                                                      USABLE

10 rows selected.

一旦删除了全局索引分区,就必须执行alter index命令重建由于drop partition操作而被标记为unusable的分区。

SQL> alter index employees_gi1 rebuild partition manager_300;

Index altered.

SQL> select partition_name,status from user_ind_partitions where index_name='EMPLOYEES_GI1';

PARTITION_NAME                                                                                                                   STATUS
-------------------------------------------------------------------------------------------------------------------------------- --------
MANAGER_100                                                                                                                      USABLE
MANAGER_1000                                                                                                                     USABLE
MANAGER_300                                                                                                                      USABLE
MANAGER_400                                                                                                                      USABLE
MANAGER_500                                                                                                                      USABLE
MANAGER_600                                                                                                                      USABLE
MANAGER_700                                                                                                                      USABLE
MANAGER_800                                                                                                                      USABLE
MANAGER_900                                                                                                                      USABLE
MANAGER_MAX                                                                                                                      USABLE

10 rows selected.

因为必须在全局分区索引上用maxvalue指定的一个分区,所以永远无法删除上限最高的分区。例如:

SQL> alter index employees_gi1 drop partition manager_1000;
alter index employees_gi1 drop partition manager_1000
                                         *
ERROR at line 1:
ORA-14078: you may not drop the highest partition of a GLOBAL index

全局分区索引可以是唯一索引,也可以是非唯一索引。本章到目前为止,只创建了非唯一索引。下面列出的是在表上创建全局唯一索引分区的语句:

SQL> create unique index employees_uk1 on employees_jy(manager_id,employee_id)
  2  global
  3  partition by range(manager_id)
  4  (
  5  partition manager_100 values less than(100),
  6  partition manager_200 values less than(200),
  7  partition manager_300 values less than(300),
  8  partition manager_400 values less than(400),
  9  partition manager_500 values less than(500),
 10  partition manager_600 values less than(600),
 11  partition manager_700 values less than(700),
 12  partition manager_800 values less than(800),
 13  partition manager_900 values less than(900),
 14  partition manager_max values less than(maxvalue)
 15  );

Index created.

本地分区索引可以定义为前缀或非前缀索引,而全局分区索引必须创建为前缀索引,也就是说,分区列必须在该索引的前导端。如果你试图创建全局分区的非前缀索引,就会收到以下错误 提示信息:

SQL> create unique index employees_uk1 on employees_jy(employee_id)
  2  global
  3  partition by range(manager_id)
  4  (
  5  partition manager_100 values less than(100),
  6  partition manager_200 values less than(200),
  7  partition manager_300 values less than(300),
  8  partition manager_400 values less than(400),
  9  partition manager_500 values less than(500),
 10  partition manager_600 values less than(600),
 11  partition manager_700 values less than(700),
 12  partition manager_800 values less than(800),
 13  partition manager_900 values less than(900),
 14  partition manager_max values less than(maxvalue)
 15  );
partition by range(manager_id)
                             *
ERROR at line 3:
ORA-14038: GLOBAL partitioned index must be prefixed

另一种全局分区索引类型是哈希分区索引。使用哈希分区索引通常是出于性能原因,因为这使用索引在分区之间更均匀地分布。请看下面创建基于哈希的全局分区索引的例子:

SQL> create index employees_jy_ih1 on employees_jy(department_id) global partition by hash(department_id) partitions 4;

Index created.

注意:全局分区索引必须在堆组织表上创建。而且,不能创建全局分区位图索引。

Oracle管理分区表的主键和唯一索引

管理分区表的主键和唯一索引
当需要在表上定义主键约束时,首先使用主键约束的列创建唯一索引,然后再添加约束,这通常被视为一种良好的做法。例如:

SQL> create unique index employees_part_pk on employees_part(employee_id,hire_date) local;

Index created.

SQL> alter table employees_part add constraint employees_part_pk primary key(employee_id,hire_date);

Table altered.

这样做的好处是,它允许你在必要时禁用和重新启用约束,而不必删除底层索引。当必须在一个大表上执行约束管理时,这可以节省大量的时间。在数据仓库环境中,经常有大量数据要批量加载到表中,禁用约束然后重新启用约束是很常见的操作,因为在这种情况下,在加载之前禁用约束并在加载后重新启用约束,可以节省大量的整体数据处理时间。

如果你首先创建了唯一索引,然后启用主键约束,如果禁用这个主键约束,那么你的约束和索引在数据库中仍然存在。如下面的例子所示:

SQL> alter table employees_part disable constraint employees_part_pk;

Table altered.

SQL> select i.index_name,c.constraint_type,i.partitioned from user_indexes i left join user_constraints c
  2  on (i.index_name=c.constraint_name) where i.index_name='EMPLOYEES_PART_PK';

INDEX_NAME                     CONSTRAINT_TYPE                PARTITIONED
------------------------------ ------------------------------ ----------------------------------------
EMPLOYEES_PART_PK              P                              YES

如果你用create tabel语句或单个alter table语句内联地创建主键,将同时创建底层的唯一索引。在这种情况下,如果禁用这个主键约束,你可以看到它删除了底层索引。

SQL> alter table employees_part disable constraint employees_part_pk;

Table altered.

SQL> select i.index_name,c.constraint_type,i.partitioned from user_indexes i left join user_constraints c
  2  on (i.index_name=c.constraint_name) where i.index_name='EMPLOYEES_PART_PK';
no rows selected

当对表的分区一级执行操作(如在表上执行分区拆分)时,会使用一个分区的索引变得不可用(unusable),这种情况下,本地分区索引就变得非常有用。至于主键,你可以简单地禁用主键约束(在这种情况下,底层索引保持不变),执行分区操作,再重建该分区索引,然后重新启用主键约束。这样,索引唯一需要重建的是受上述操作影响的分区。

在唯一索引最终要成为主键时,唯一索引必须包含分区列这一限制会导致另一个常见的问题,即,有时用户提出需求,不要把分区列放到表的主键列中去。如下面的例子所示,通过使用先前创建的唯一索引,就可以简单地改变员工表来创建主键约束:

SQL> alter table employees_part add constraint employees_part_pk_1 primary key(employee_id);

Table altered.

如果你的客户需求中规定,分区列(在本列是hire_date)不得作为主键的一部分,那么也可以使用相同的语法简单地创建主键约束,这同时也创建了未分区的底层索引。下面的查询显示了这个结果:

SQL> select i.index_name,c.constraint_type,i.partitioned from user_indexes i,user_constraints c
  2  where i.index_name=c.constraint_name and i.index_name='EMPLOYEES_PART_PK_1';

INDEX_NAME                     CONSTRAINT_TYPE                PARTITIONED
------------------------------ ------------------------------ ----------------------------------------
EMPLOYEES_PART_PK_1            P                              NO

你的表上确实建立了索引,但它不是分区的。这样做的好处是,解除了唯一索引必须包含分区列的限制,可以在单个employee_id列上创建“自然”的主键约束了。缺点是,现在分区表employees上有一个非分区索引。如果现在需要在该表上执行任何分区级操作,如截断分区,移动分区,或拆分分区,等等,那么整个底层非分区索引将被标记为unusable,而且必须在执行任何分区级操作后重建它。

Oracle本地分区索引

1 分区索引
分区索引与非分区索引相比,具有许多好处。使用分区索引的关键好处如下:
.性能方面的优势
.通过DML操作载入数据。
.通过DDL操作载入数据。
.用select语句查询数据。

.维护方面的好处
.重建索引。
.在分区一级把索引设置为不可用或不可见。

可以创建本地分区或全局分区的分区索引。本地分区索引只能存在于分区表,而全局分区的索引既可以在分区表创建,也可以在非分区表上创建。还可以在分区表上创建非分区索引。分区索引最常见的配置是在分区表上创建本地分区索引,只是因为这种配置的整体优势一般大于分区表上的全局分区索引和非分区索引。

注意:创建分区表上的非分区索引与创建非分区表上的非分区索引是相同的。

2 创建本地分区索引
最常见的分区索引类型是本地分区索引。本地分区索引只能在分区表上创建。顾名思义,“本地”是指索引条目和相应的数据之间有直接的关系。数据分区和索引分区之间存在一对一的关系。如果有某个按日期范围分区的表,今年每个月都有一个分区,那么对于2012年1月分区的所有数据,创建的每个索引在2012年1月分区都有相应的索引项。

2.1 最简单的形式
除LOCAL关键字外,最基本的创建本地分区索引的形式与创建非分区索引相同。

create table employees_part
(
EMPLOYEE_ID   NUMBER(6),
FIRST_NAME    VARCHAR2(20),
LAST_NAME     VARCHAR2(25),
EMAIL         VARCHAR2(25),
PHONE_NUMBER  VARCHAR2(20),
HIRE_DATE     DATE,
JOB_ID        VARCHAR2(10),
SALARY        NUMBER(8,2),
COMMISSION_PCT NUMBER(2,2),
MANAGER_ID     NUMBER(6),
DEPARTMENT_ID  NUMBER(4)
)
partition by range(hire_date)
(
   partition p01 values less than(to_date('2002-01-01','yyyy-mm-dd')),
   partition p02 values less than(to_date('2003-01-01','yyyy-mm-dd')),
   partition p03 values less than(to_date('2004-01-01','yyyy-mm-dd')),
   partition p04 values less than(to_date('2005-01-01','yyyy-mm-dd')),
   partition p05 values less than(to_date('2006-01-01','yyyy-mm-dd')),
   partition p06 values less than(to_date('2007-01-01','yyyy-mm-dd')),
   partition p07 values less than(to_date('2008-01-01','yyyy-mm-dd')),
   partition p08 values less than(to_date('2009-01-01','yyyy-mm-dd')),
   partition p09 values less than(to_date('2010-01-01','yyyy-mm-dd')),
   partition p10 values less than(to_date('2011-01-01','yyyy-mm-dd')),
   partition p11 values less than(to_date('2012-01-01','yyyy-mm-dd')),
   partition p12 values less than(to_date('2013-01-01','yyyy-mm-dd'))
);
/




SQL> create table employees_part
  2  (
  3  EMPLOYEE_ID   NUMBER(6),
  4  FIRST_NAME    VARCHAR2(20),
  5  LAST_NAME     VARCHAR2(25),
  6  EMAIL         VARCHAR2(25),
  7  PHONE_NUMBER  VARCHAR2(20),
  8  HIRE_DATE     DATE,
  9  JOB_ID        VARCHAR2(10),
 10  SALARY        NUMBER(8,2),
 11  COMMISSION_PCT NUMBER(2,2),
 12  MANAGER_ID     NUMBER(6),
 13  DEPARTMENT_ID  NUMBER(4)
 14  )
 15  partition by range(hire_date)
 16  (
 17     partition p01 values less than(to_date('2002-01-01','yyyy-mm-dd')),
 18     partition p02 values less than(to_date('2003-01-01','yyyy-mm-dd')),
 19     partition p03 values less than(to_date('2004-01-01','yyyy-mm-dd')),
 20     partition p04 values less than(to_date('2005-01-01','yyyy-mm-dd')),
 21     partition p05 values less than(to_date('2006-01-01','yyyy-mm-dd')),
 22     partition p06 values less than(to_date('2007-01-01','yyyy-mm-dd')),
 23     partition p07 values less than(to_date('2008-01-01','yyyy-mm-dd')),
 24     partition p08 values less than(to_date('2009-01-01','yyyy-mm-dd')),
 25     partition p09 values less than(to_date('2010-01-01','yyyy-mm-dd')),
 26     partition p10 values less than(to_date('2011-01-01','yyyy-mm-dd')),
 27     partition p11 values less than(to_date('2012-01-01','yyyy-mm-dd')),
 28     partition p12 values less than(to_date('2013-01-01','yyyy-mm-dd'))
 29  );
/
Table created.


SQL> insert into employees_part select * from employees;

107 rows created.

SQL> commit;

Commit complete.


SQL> create index employees_part_i1 on employees_part(hire_date) local;

Index created.

对于这个例子,Oracle创建的分区名称将与数据分区的名称相同。此外,所有分区都将在USERS表空间内创建。

根据应用程序的需求,可能需要指定各个分区的具体信息,比如下列内容:
.分区名称
.表空间名称
.存储参数

2.2 分区级的需求
如果有特定的分区级的需求,就需要在create index ddl内指定每个分区的信息。例如:

SQL> create index employees_part_i2 on employees_part(hire_date) local
  2  (
  3  partition pi01,
  4  partition pi02,
  5  partition pi03,
  6  partition pi04,
  7  partition pi05,
  8  partition pi06,
  9  partition pi07,
 10  partition pi08,
 11  partition pi09,
 12  partition pi10,
 13  partition pi11,
 14  partition pi12
 15  );

Index created.

在这个例子中,对分区名称进行了修改,在表分区名中间插入一个i来表示索引分区。为了让索引具有与表不同的分区名称,必须在create indexddl中指定每个分区的名称。还可以为每个分区指定不同的表空间,因为它代表了一年的数据。把每年的数据放在自己的表空间内,就可以把往年数据的表空间设置为只读。这有助于提高查询速度和备份速度,因为你不需要在数据库的每个备份中都备份只读表空间。

再次强调,要创建本地分区索引,必须是在分区表上创建。否则,就会出现以下错误提示信息:

SQL> create index employees_i1 on employees(hire_date) tablespace users local;
create index employees_i1 on employees(hire_date) tablespace users local
                             *
ERROR at line 1:
ORA-14016: underlying table of a LOCAL partitioned index must be partitioned

2.3 前缀和非前缀选项
可以把本地分区索引创建为前缀或非前缀的。在创建本地分区的前缀索引时,这意味着表的分区列在索引的前导端。如果分区列不在索引的前导端,就被当作非前缘索引。在Oracle的早期版本中,本地前缀索引与非前缀索引相比,在性能上有一些优势。在Oracle后来的版本中,包括版本11gR2,建立本地前缀索引的优势已经减弱。不过,如果你的数据库环境是OLTP系统,本地前缀索引比非前缀索引更有利于提高查询性能,因为为了检索某个查询所需的数据,优化器需要扫描的索引分区可能会较少。

在创建本地分区的唯一索引时,索引必须包括分区列,否则就会出现以下错误提示信息:

SQL> create unique index employees_part_pk on employees_part(employee_id) local;
create unique index employees_part_pk on employees_part(employee_id) local
                                         *
ERROR at line 1:
ORA-14039: partitioning columns must form a subset of key columns of a UNIQUE index

在唯一索引的定义中添加分区列(在本例中是hire_date)后,就可以在employees_part表上创建唯一索引了。

SQL> create unique index employees_part_pk on employees_part(employee_id,hire_date) local;

Index created.

Oracle应用程序域索引

应用程序域索引是最不为人所知且大多数开发人员和DBA也是最少使用的一种。应用域索引支持创建自己的索引结构,以实现目前Oracle数据库中不存在的新的索引类型。Oracle数据库本身实现的全文索引,就是应用程序域索引的一个很好的例子,全文索引常用来促进在大的文本项中的搜索。为了创建全文索引,需要在create index语句中包含indextype子句,如下所示:

SQL> create table test_tab(dsc clob);

Table created.

SQL> insert into test_tab values('hi,this is xiaoming');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test_tab;

DSC
--------------------------------------------------------------------------------
hi,this is xiaoming

SQL> create index test_idx on test_tab(dsc) indextype is ctxsys.context;

Index created.

SQL>
SQL> select * from test_tab where contains(dsc,'this is')>0;

no rows selected

就像收集普通索引的统计信息一样,可以调用dbms_stats.gather_index_stats存储过程来收集全文索引的统计信息。虽然我们这里只是简单地讨论了应用程序域索引,在大多数情况下,这些索引对第三方解决方案供应商有更多的用处,因为他们需要使用创新的索引解决方案来访问各类非传统的数据,如文本和图像。例如,在使用某些类型的数据,如视频剪辑时,Oracle提供的数据类型可能不太适用。此外,这些特殊类型的数据,可能需要特殊类型的操作,例如,图像中的颜色等级。你可以使用能把图像中的颜色分级的比较运算符,来定义这些特殊类型的操作。

Proudly powered by WordPress | Indrajeet by Sus Hill.