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

太过爱你忘了你带给我的痛 2022-08-11 19:58 200阅读 0赞

设想有一个分区表，上面建了一个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

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

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关关于 Oracle 分区索引的创建和维护

相关 MySQL怎么为JSON字段创建索引

相关 mysql 聚集索引如何创建

相关 mysql 添加索引 mysql 如何创建索引

相关 mysql 创建索引命令_mysql 索引-【创建索引命令】

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

相关创建索引

相关 Oracle如何创建条件索引

相关 mysql 添加索引 mysql 如何创建索引

相关如何创建合适的索引