[20130812]12c Partial Indexes For Partitioned Tables Part II.txt

[20130812]12c Partial Indexes For Partitioned Tables Part II.txt


參考連結：


更多的是重複作者的測試，加深理解:

1.測試環境:

SQL> @ver

BANNER                                                                               CON_ID

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

Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production              0

2.測試建立：

SQL> drop table PINK_FLOYD purge ;
Table dropped.

SQL> create table pink_floyd (id number, status varchar2(6), name varchar2(30))
indexing off
partition by range (id)
(partition pf1 values less than (1000001),
partition pf2 values less than (2000001) indexing off,
partition pf3 values less than (maxvalue) indexing on);
Table created.

SQL> insert into pink_floyd
select rownum, 'CLOSED', 'DAVID BOWIE' from
(select 1 from dual connect by level <= 3000) a,
(select 1 from dual connect by level<=1000) b;

3000000 rows created.

SQL> create index pink_floyd_status_i on pink_floyd(status) local indexing partial;

Index created.

SQL> select index_name, partition_name, num_rows, status, leaf_blocks from dba_ind_partitions where index_name = 'PINK_FLOYD_STATUS_I';

INDEX_NAME           PARTITION_NAME         NUM_ROWS STATUS   LEAF_BLOCKS

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

PINK_FLOYD_STATUS_I  PF1                           0 UNUSABLE           0

PINK_FLOYD_STATUS_I  PF2                           0 UNUSABLE           0

PINK_FLOYD_STATUS_I  PF3                     1000000 USABLE          2507

SQL> create unique index pink_floyd_id_i on pink_floyd(id) indexing partial;
create unique index pink_floyd_id_i on pink_floyd(id) indexing partial
                                       *
ERROR at line 1:
ORA-14226: unique index may not be PARTIAL
--可以發現唯一鍵值不能使用indexing partial引數。

SQL> alter table pink_floyd add constraint pink_floyd_pk primary key(id)
using index (create index pink_floyd_id_i on pink_floyd(id) indexing partial);
*
ERROR at line 1:
ORA-14196: Specified index cannot be used to enforce the constraint.

SQL> alter table pink_floyd add constraint pink_floyd_pk primary key(id) using index
(create index pink_floyd_id_i on pink_floyd(id) indexing partial);
*
ERROR at line 1:
ORA-14196: Specified index cannot be used to enforce the constraint.

SQL> create index pink_floyd_id_i on pink_floyd(id) indexing partial;
Index created.

SQL> alter table pink_floyd add primary key(id);
alter table pink_floyd add primary key(id)
*
ERROR at line 1:
ORA-01408: such column list already indexed

--部分索引對於PK，unique無效。



    It clearly doesn't make sense to create a Partial Unique Index or on a Non-Unique Index policing a PK or Unique Key
constraint as it would be impossible to use such an index to guarantee the required unique property. With missing index
entries associated with non-indexed partitions, how can Oracle determine whether a value from new row already exists or
not ? It can't and hence Oracle doesn't permit the creation of such a Partial Index.

    Partial Indexes can potentially be extremely useful in reducing unnecessary storage requirements, reducing index
maintenance overheads and in improving performance by reducing index block accesses.