ITPub博客

首页 > Linux操作系统 > Linux操作系统 > [20130812]12c Partial Indexes For Partitioned Tables Part II.txt

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

原创 Linux操作系统 作者:lfree 时间:2013-08-15 15:37:24 0 删除 编辑
[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 <= 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无效。

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.

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-768590/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2600
  • 访问量
    6375016