oracle分区索引使用问题

 

表用A列作分区，索引用B做局部分区索引（local partitioned indexes），若where条件中用B来查询，那么oracle会扫描所有的表

和索引的分区，成本会更高，此时可以考虑用B做全局分区索引（global partitioned indexes）。 

 

全局分区索引（索引的分区键 和 建索引的索引键 要一致）：

 

create index IDX_MER_ACC_DETAIL on TBL_MERCHANT_ACCOUNT_DETAIL(STEP_NAME)  
global partition by range(STEP_NAME)
(
partition pa1 values less than ('dd'),
partition pa2 values less than ('hi'),
partition pa3 values less than ('st'),
partition pa4 values less than ('wt'),
partition pa5 values less than (maxvalue)
)

非常感谢这篇文章：

Oracle数据库中，有两种类型的分区索引，全局索引和本地索引，其中本地索引又可以分为本地前缀索引和本地非前缀索引。下面就分别看看每种类型的索引各自的特点。

全局索引以整个表的数据为对象建立索引，索引分区中的索引条目既可能是基于相同的键值但是来自不同的分区，也可能是多个不同键值的组合。

全局索引既允许索引分区的键值和表分区键值相同，也可以不相同。全局索引和表之间没有直接的联系，这一点和本地索引不同。

SQL> create table orders (
order_no number,
part_no varchar2(40),
ord_date date
)
partition by range (ord_date)
(partition Q1 values less than (TO_DATE('01-APR-1999','DD-MON-YYYY')),
partition Q2 values less than (TO_DATE('01-JUL-1999','DD-MON-YYYY')),
partition Q3 values less than (TO_DATE('01-OCT-1999','DD-MON-YYYY')),
partition Q4 values less than (TO_DATE('01-JAN-2000','DD-MON-YYYY'))
)
;

Table created.

SQL> create index orders_global_1_idx
on orders(ord_date)
global partition by range (ord_date)
(partition GLOBAL1 values less than (TO_DATE('01-APR-1999','DD-MON-YYYY')),
partition GLOBAL2 values less than (TO_DATE('01-JUL-1999','DD-MON-YYYY')),
partition GLOBAL3 values less than (TO_DATE('01-OCT-1999','DD-MON-YYYY')),
partition GLOBAL4 values less than (MAXVALUE)
)
;

Index created.

SQL> create index orders_global_2_idx
on orders(part_no)
global partition by range (part_no)
(partition IND1 values less than (555555),
partition IND2 values less than (MAXVALUE)
)
;

Index created.

从上面的语句可以看出，全局索引和表没有直接的关联，必须显式的指定maxvalue值。假如表中新加了分区，不会在全局索引中自动增加新的分区，必须手工添加相应的分区。

SQL> alter table orders add partition Q5 values less than (TO_DATE('01-APR-2000','DD-MON-YYYY'));

Table altered.

SQL> select TABLE_NAME, PARTITION_NAME from dba_tab_partitions where table_name='ORDERS';

TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------
ORDERS Q1
ORDERS Q2
ORDERS Q3
ORDERS Q4
ORDERS Q5

SQL> select INDEX_NAME, PARTITION_NAME from dba_ind_partitions where index_name=upper('orders_global_1_idx');

INDEX_NAME PARTITION_NAME
------------------------------ ------------------------------
ORDERS_GLOBAL_1_IDX GLOBAL1
ORDERS_GLOBAL_1_IDX GLOBAL2
ORDERS_GLOBAL_1_IDX GLOBAL3
ORDERS_GLOBAL_1_IDX GLOBAL4

使用全局索引，索引键值必须和分区键值相同，这就是所谓的前缀索引。Oracle不支持非前缀的全局分区索引，如果需要建立非前缀分区索引，索引必须建成本地索引。

SQL> create index orders_global_2_idx
2 on orders(part_no)
3 global partition by range (order_no)
4 (partition IND1 values less than (555555),
5 partition IND2 values less than (MAXVALUE)
6 )
7 ;
global partition by range (order_no)
*
ERROR at line 3:
ORA-14038: GLOBAL partitioned index must be prefixed

接下来再来看看本地分区。

本地索引的分区和其对应的表分区数量相等，因此每个表分区都对应着相应的索引分区。使用本地索引，不需要指定分区范围因为索引对于表而言是本地的，当本地索引创建时，Oracle会自动为表中的每个分区创建独立的索引分区。

创建本地索引不必显式的指定maxvalue值，因为为表新添加表分区时，会自动添加相应的索引分区。

create index orders_local_1_idx
on orders(ord_date)
local
(partition LOCAL1,
partition LOCAL2,
partition LOCAL3,
partition LOCAL4
)
;

Index created.

SQL> select INDEX_NAME, PARTITION_NAME from dba_ind_partitions where index_name=upper('orders_local_1_idx');

INDEX_NAME PARTITION_NAME
------------------------------ ------------------------------
ORDERS_LOCAL_1_IDX LOCAL1
ORDERS_LOCAL_1_IDX LOCAL2
ORDERS_LOCAL_1_IDX LOCAL3
ORDERS_LOCAL_1_IDX LOCAL4

SQL> alter table orders add partition Q5 values less than (TO_DATE('01-APR-2000','DD-MON-YYYY'));

Table altered.

SQL> select INDEX_NAME, PARTITION_NAME from dba_ind_partitions where index_name=upper('orders_local_1_idx');

INDEX_NAME PARTITION_NAME
------------------------------ ------------------------------
ORDERS_LOCAL_1_IDX LOCAL1
ORDERS_LOCAL_1_IDX LOCAL2
ORDERS_LOCAL_1_IDX LOCAL3
ORDERS_LOCAL_1_IDX LOCAL4
ORDERS_LOCAL_1_IDX Q5

这里系统已经自动以和表分区相同的名字自动创建了一个索引分区。同理，删除表分区时相对应的索引分区也自动被删除。

本地索引和全局索引还有一个显著的差别，就是上面提到的，本地索引可以创建成本地非前缀型，而全局索引只能是前缀型。

SQL> create index orders_local_2_idx
on orders(part_no)
local
(partition LOCAL1,
partition LOCAL2,
partition LOCAL3,
partition LOCAL4)
;

Index created.

SQL> select INDEX_NAME, PARTITION_NAME, HIGH_VALUE from dba_ind_partitions
where index_name=upper('orders_local_2_idx');

INDEX_NAME PARTITION_NAME HIGH_VALUE
------------------------------ ------------------------------ ---------------------------------------------------------
ORDERS_LOCAL_2_IDX LOCAL1 TO_DATE(' 1999-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
'NLS_CALENDAR=GREGORIA'
ORDERS_LOCAL_2_IDX LOCAL2 TO_DATE(' 1999-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
'NLS_CALENDAR=GREGORIA'
ORDERS_LOCAL_2_IDX LOCAL3 TO_DATE(' 1999-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
'NLS_CALENDAR=GREGORIA'
ORDERS_LOCAL_2_IDX LOCAL4 TO_DATE(' 2000-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
'NLS_CALENDAR=GREGORIA'

从上面的输出可以看出，虽然索引的键值是part_no，但索引分区的键值仍然和表的分区键值相同，即ord_date，也即是所谓的非前缀型索引。

最后，再引用一个例子说明前缀索引和非前缀索引的应用。

假设有一个使用DATE列分区的大表。我们经常使用一个VARCHAR2列(VCOL)进行查询，但这个列并不是表的分区键值。

有两种可能的方法来访问VCOL列的数据，一是建立基于VCOL列的本地非前缀索引，

| |
------- -------
| | (10 more | |
Values: A.. Z.. partitions here) A.. Z..

另一种是建立基于VCOL列的全局索引，

| |
------- -------
| | (10 more | |
Values: A.. D.. partitions here) T.. Z..

可以看出，如果能够保证VCOL列值的唯一性，全局索引将会是最好的选择。如果VCOL列值不唯一，就需要在本地非前缀索引的并行查询和全局索引顺序查询以及高昂的维护代价之间做出选择。