[20130812]12c Partial Indexes For Partitioned Tables Part II.txt
参考链接：
http://richardfoote.wordpress.com/2013/07/12/12c-partial-indexes-for-partitioned-tables-part-ii-vanishing-act/
更多的是重复作者的测试，加深理解:
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 (select 1 from dual connect by level
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无效。
http://richardfoote.wordpress.com/2013/07/12/12c-partial-indexes-for-partitioned-tables-part-ii-vanishing-act/
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.