如何创建为单个分区创建索引

来源：互联网发布：办公室网络安装编辑：程序博客网时间：2024/06/08 16:35

设想有一个分区表，上面建了一个local index，如果只是想在最近的分区上使用索引，而老的分区上不需要创建索引，这样的功能只有在12c中提供，而此时就可以使用这个unusable特性，先创建local索引并将其状态置为unusable，然后将需要使用的索引分区进行rebuild，从而实现了针对单个分区创建索引的目的。

SQL> CREATE TABLE INV_HISTORY

2 (inv_no NUMBER(10), inv_date DATE, inv_amt NUMBER(10,2))

3 partition by range (inv_date) interval(numtoyminterval(1,'month'))

4 (partition p0 values less than (to_date('01-01-2005','dd-mm-yyyy')),

5 partition p1 values less than (to_date('01-01-2006','dd-mm-yyyy'))

6 );

Table created.

SQL> insert into inv_history select rownum,to_date('2005-01-01','yyyy-mm-dd'),1000 from dual connect by level<=200000;

200000 rows created.

SQL> /

200000 rows created.

SQL> insert into inv_history select rownum,to_date('2004-01-01','yyyy-mm-dd'),1000 from dual connect by level<=200000;

200000 rows created.

SQL> /

200000 rows created.

SQL> commit;

Commit complete.

SQL> select count(*) from INV_HISTORY partition(p0);

COUNT(*)

----------

400000

SQL> select count(*) from INV_HISTORY partition(p1);

COUNT(*)

----------

400000

SQL> select segment_name,segment_type,partition_name,bytes/1024/1024 from user_segments;

SEGMENT_NAME SEGMENT_TYPE PARTITION_NAME BYTES/1024/1024

------------------------------ ------------------ ------------------------------ ---------------

INV_HISTORY TABLE PARTITION P0 10

INV_HISTORY TABLE PARTITION P1 11

--创建索引并设置为unusable

SQL> CREATE INDEX inv_no_ix ON INV_HISTORY(inv_no) LOCAL unusable;

Index created.

--查看segment情况，发现unusable的索引没有创建segment

SQL> select segment_name,segment_type,partition_name,bytes/1024/1024 from user_segments;

SEGMENT_NAME SEGMENT_TYPE PARTITION_NAME BYTES/1024/1024

------------------------------ ------------------ ------------------------------ ---------------

INV_HISTORY TABLE PARTITION P0 10

INV_HISTORY TABLE PARTITION P1 11

--此时可以对一个索引进行rebuild，rebuild之后该索引将变为usable状态

SQL> alter index INV_NO_IX rebuild partition p1;

Index altered.

SQL> select segment_name,segment_type,partition_name,bytes/1024/1024 from user_segments;

SEGMENT_NAME SEGMENT_TYPE PARTITION_NAME BYTES/1024/1024

------------------------------ ------------------ ------------------------------ ---------------

INV_HISTORY TABLE PARTITION P0 10

INV_HISTORY TABLE PARTITION P1 11

INV_NO_IX INDEX PARTITION P1 8

SQL> select index_name,partition_name,status from user_ind_partitions ;

INDEX_NAME PARTITION_NAME STATUS

------------------------------ ------------------------------ --------

INV_NO_IX P1 USABLE

INV_NO_IX P0 UNUSABLE

--此时如果通过inv_no查询分区p1，则会使用索引

SQL> select * from inv_history where inv_date=to_date('2005-01-01','yyyy-mm-dd') and inv_no=1;

INV_NO INV_DATE INV_AMT

---------- --------- ----------

1 01-JAN-05 1000

Execution Plan

----------------------------------------------------------

Plan hash value: 2903314601

------------------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 2 | 32 | 5 (0)| 00:00:01 | | |

| 1 | PARTITION RANGE SINGLE | | 2 | 32 | 5 (0)| 00:00:01 | 2 | 2 |

|* 2 | TABLE ACCESS BY LOCAL INDEX ROWID| INV_HISTORY | 2 | 32 | 5 (0)| 00:00:01 | 2 | 2 |

|* 3 | INDEX RANGE SCAN | INV_NO_IX | 2 | | 3 (0)| 00:00:01 | 2 | 2 |

------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - filter("INV_DATE"=TO_DATE(' 2005-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

3 - access("INV_NO"=1)

Statistics

----------------------------------------------------------

1 recursive calls

0 db block gets

6 consistent gets

2 physical reads

0 redo size

739 bytes sent via SQL*Net to client

519 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

2 rows processed

--而查询partition p0时，由于相应的索引分区为unusable状态，不会使用索引，但不影响查询

SQL> select * from inv_history where inv_date=to_date('2004-01-01','yyyy-mm-dd') and inv_no=1;

INV_NO INV_DATE INV_AMT

---------- --------- ----------

1 01-JAN-04 1000

Execution Plan

----------------------------------------------------------

Plan hash value: 3216071522

------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 2 | 32 | 347 (2)| 00:00:05 | | |

| 1 | PARTITION RANGE SINGLE| | 2 | 32 | 347 (2)| 00:00:05 | 1 | 1 |

|* 2 | TABLE ACCESS FULL | INV_HISTORY | 2 | 32 | 347 (2)| 00:00:05 | 1 | 1 |

------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - filter("INV_NO"=1 AND "INV_DATE"=TO_DATE(' 2004-01-01 00:00:00', 'syyyy-mm-dd

hh24:mi:ss'))

Statistics

----------------------------------------------------------

1 recursive calls

0 db block gets

1213 consistent gets

0 physical reads

0 redo size

739 bytes sent via SQL*Net to client

519 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

2 rows processed

--如果再次将partition p1设置为unusable，那么该分区索引对应的段将被删除

SQL> alter index INV_NO_IX modify partition p1 unusable;

Index altered.

SQL> select index_name,partition_name,status from user_ind_partitions ;

INDEX_NAME PARTITION_NAME STATUS

------------------------------ ------------------------------ --------

INV_NO_IX P1 UNUSABLE

INV_NO_IX P0 UNUSABLE

SQL> select segment_name,segment_type,partition_name,bytes/1024/1024 from user_segments;

SEGMENT_NAME SEGMENT_TYPE PARTITION_NAME BYTES/1024/1024

------------------------------ ------------------ ------------------------------ ---------------

TEST TABLE 3

INV_HISTORY TABLE PARTITION P0 10

INV_HISTORY TABLE PARTITION P1

0 0