本文共 10005 字，大约阅读时间需要 33 分钟。
探究Oracle 12c 中在同一个列上建立多个索引
Oracle 12c允许在同一组列上建立多个索引，但是只有一个索引是可见的，并且所有索引在某些方面可以有不同。
示例
Non-Partitioned Tables
例如，我们可以创建一个非分区表t1，并插入测试数据。
然后在 create_date列上创建一个索引；当第二次使用语句
CREATE INDEX t1_idx2 ON t1(created_date) INVISIBLE;
在 created_date列上再次创建索引时，提示错误。
SQL> CREATE TABLE t1 ( 2 id NUMBER, 3 description VARCHAR2(50), 4 created_date DATE 5 );表已创建。已用时间: 00: 00: 00.08SQL>SQL> INSERT INTO t1 VALUES (1, 't1 ONE', TO_DATE('01/07/2014', 'DD/MM/YYYY'));已创建 1 行。已用时间: 00: 00: 00.03SQL> INSERT INTO t1 VALUES (2, 't1 TWO', TO_DATE('01/07/2015', 'DD/MM/YYYY'));已创建 1 行。已用时间: 00: 00: 00.01SQL> INSERT INTO t1 VALUES (3, 't1 THREE', TO_DATE('01/07/2016', 'DD/MM/YYYY'));已创建 1 行。已用时间: 00: 00: 00.00SQL> COMMIT;提交完成。已用时间: 00: 00: 00.00SQL> CREATE INDEX t1_idx1 ON t1(created_date) VISIBLE;索引已创建。已用时间: 00: 00: 00.01SQL> CREATE INDEX t1_idx2 ON t1(created_date) INVISIBLE;CREATE INDEX t1_idx2 ON t1(created_date) INVISIBLE *第 1 行出现错误:ORA-01408: 此列列表已索引已用时间: 00: 00: 00.06SQL> CREATE BITMAP INDEX t1_idx3 ON t1(created_date) INVISIBLE;索引已创建。已用时间: 00: 00: 00.11SQL> 但是可以使用这个语句在created_date列上来创建一个BITMAP类型的索引：
CREATE BITMAP INDEX t1_idx3 ON t1(created_date) INVISIBLE;
Partitioned Tables
创建一个分区表t1：
SQL> DROP TABLE t1 PURGE;表已删除。SQL>SQL> CREATE TABLE t1 ( 2 id NUMBER, 3 description VARCHAR2(50), 4 created_date DATE 5 ) 6 PARTITION BY RANGE (created_date) ( 7 PARTITION part_2014 VALUES LESS THAN (TO_DATE('01/01/2015', 'DD/MM/YYYY')) TABLESPACE users, 8 PARTITION part_2015 VALUES LESS THAN (TO_DATE('01/01/2016', 'DD/MM/YYYY')) TABLESPACE users, 9 PARTITION part_2016 VALUES LESS THAN (TO_DATE('01/01/2017', 'DD/MM/YYYY')) TABLESPACE users 10 );表已创建。SQL>SQL> INSERT INTO t1 VALUES (1, 't1 ONE', TO_DATE('01/07/2014', 'DD/MM/YYYY'));已创建 1 行。SQL> INSERT INTO t1 VALUES (2, 't1 TWO', TO_DATE('01/07/2015', 'DD/MM/YYYY'));已创建 1 行。SQL> INSERT INTO t1 VALUES (3, 't1 THREE', TO_DATE('01/07/2016', 'DD/MM/YYYY'));已创建 1 行。SQL> COMMIT;提交完成。SQL> 在分区表t1上创建一个索引：
CREATE INDEX t1_idx1 ON t1(created_date) GLOBAL VISIBLE;
SQL> CREATE INDEX t1_idx1 ON t1(created_date) GLOBAL VISIBLE;索引已创建。SQL>
在不同的分区上创建索引;
CREATE INDEX t1_idx2 ON t1(created_date) GLOBALPARTITION BY RANGE (created_date) ( PARTITION t1_p1 VALUES LESS THAN (TO_DATE('01/01/2015', 'DD/MM/YYYY')) TABLESPACE users, PARTITION t1_p2 VALUES LESS THAN (TO_DATE('01/01/2016', 'DD/MM/YYYY')) TABLESPACE users, PARTITION t1_p3 VALUES LESS THAN (MAXVALUE) TABLESPACE users)INVISIBLE; SQL> CREATE INDEX t1_idx2 ON t1(created_date) GLOBAL 2 PARTITION BY RANGE (created_date) ( 3 PARTITION t1_p1 VALUES LESS THAN (TO_DATE('01/01/2015', 'DD/MM/YYYY')) TABLESPACE users, 4 PARTITION t1_p2 VALUES LESS THAN (TO_DATE('01/01/2016', 'DD/MM/YYYY')) TABLESPACE users, 5 PARTITION t1_p3 VALUES LESS THAN (MAXVALUE) TABLESPACE users 6 ) 7 INVISIBLE;索引已创建。SQL> SQL> CREATE INDEX t1_idx3 ON t1(created_date) LOCAL INVISIBLE;索引已创建。SQL> CREATE BITMAP INDEX t1_idx4 ON t1(created_date) LOCAL INVISIBLE;索引已创建。SQL>
为什么要使用多索引？即为什么要在一个列上创建多个索引呢？
可以通过在一个列上创建不同的索引，通过改变索引的可见性（visible或者invisible）来测试索引的影响。
可以通过下面的查询来查看索引的可见性
SQL> -- Check visibility of indexes.SQL> COLUMN index_name FORMAT A10SQL> COLUMN index_type FORMAT A10SQL> COLUMN partitioned FORMAT A12SQL> COLUMN locality FORMAT A8SQL> COLUMN visibility FORMAT A10SQL>SQL> SELECT a.index_name, 2 a.index_type, 3 a.partitioned, 4 b.partitioning_type, 5 b.locality, 6 a.visibility 7 FROM user_indexes a 8 LEFT OUTER JOIN user_part_indexes b ON a.index_name = b.index_name 9 ORDER BY index_name;INDEX_NAME INDEX_TYPE PARTITIONED PARTITIONING_TYPE LOCALITY VISIBILITY---------- ---------- ------------ ------------------ -------- ----------IDX_DEPTCO NORMAL NO VISIBLEPY_DEPTNOIDX_DEPTNO NORMAL NO VISIBLEIDX_MGR NORMAL NO VISIBLEPK_DEPT NORMAL NO VISIBLEPK_EMP NORMAL NO VISIBLET1_IDX1 NORMAL NO VISIBLET1_IDX2 NORMAL YES RANGE GLOBAL INVISIBLET1_IDX3 NORMAL YES RANGE LOCAL INVISIBLET1_IDX4 BITMAP YES RANGE LOCAL INVISIBLETEST_TAB_I NORMAL NO VISIBLED已选择 10 行。SQL>
看看查询中索引的使用情况。执行下面的查询，通过查看执行计划发现使用了索引，
SQL> SET AUTOTRACE TRACE EXPLAINSQL> SELECT * 2 FROM t1 3 WHERE created_date = TO_DATE('01/07/2014', 'DD/MM/YYYY');执行计划----------------------------------------------------------Plan hash value: 1106166644----------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |----------------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 49 | 1 (0)| 00:00:01 | | || 1 | TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| T1 | 1 | 49 | 1 (0)| 00:00:01 | 1 | 1 ||* 2 | INDEX RANGE SCAN | T1_IDX1 | 1 | | 1 (0)| 00:00:01 | | |----------------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 2 - access("CREATED_DATE"=TO_DATE(' 2014-07-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))Note----- - dynamic statistics used: dynamic sampling (level=2)SQL> 改变索引的状态
SQL> -- Switch indexes.SQL> ALTER INDEX t1_idx1 INVISIBLE;索引已更改。SQL> ALTER INDEX t1_idx2 VISIBLE;索引已更改。
查看索引的状态是否改变：
SQL> -- Check visibility of indexes.SQL> SELECT a.index_name, 2 a.index_type, 3 a.partitioned, 4 b.partitioning_type, 5 b.locality, 6 a.visibility 7 FROM user_indexes a 8 LEFT OUTER JOIN user_part_indexes b ON a.index_name = b.index_name 9 ORDER BY index_name;INDEX_NAME INDEX_TYPE PARTITIONED PARTITIONING_TYPE LOCALITY VISIBILITY---------- ---------- ------------ ------------------ -------- ----------IDX_DEPTCO NORMAL NO VISIBLEPY_DEPTNOIDX_DEPTNO NORMAL NO VISIBLEIDX_MGR NORMAL NO VISIBLEPK_DEPT NORMAL NO VISIBLEPK_EMP NORMAL NO VISIBLET1_IDX1 NORMAL NO INVISIBLET1_IDX2 NORMAL YES RANGE GLOBAL VISIBLET1_IDX3 NORMAL YES RANGE LOCAL INVISIBLET1_IDX4 BITMAP YES RANGE LOCAL INVISIBLETEST_TAB_I NORMAL NO VISIBLED
执行查询，查看索引是否可用：
SQL> -- Test the index usage.SQL> SET AUTOTRACE TRACE EXPLAINSQL>SQL> SELECT * 2 FROM t1 3 WHERE created_date = TO_DATE('01/07/2014', 'DD/MM/YYYY');执行计划----------------------------------------------------------Plan hash value: 3769679070-----------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |-----------------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 49 | 1 (0)| 00:00:01 | | || 1 | PARTITION RANGE SINGLE | | 1 | 49 | 1 (0)| 00:00:01 | 1 | 1 || 2 | TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| T1 | 1 | 49 | 1 (0)| 00:00:01 | 1 | 1 ||* 3 | INDEX RANGE SCAN | T1_IDX2 | 1 | | 1 (0)| 00:00:01 | 1 | 1 |-----------------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 3 - access("CREATED_DATE"=TO_DATE(' 2014-07-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))Note----- - dynamic statistics used: dynamic sampling (level=2)SQL> 改变索引的状态：
SQL> -- Switch indexes.SQL> ALTER INDEX t1_idx3 INVISIBLE;索引已更改。SQL> ALTER INDEX t1_idx4 VISIBLE;ALTER INDEX t1_idx4 VISIBLE*第 1 行出现错误:ORA-14147: 同一列集上已存在定义的 VISIBLE 索引。SQL>SQL>SQL> -- Check visibility of indexes.SQL> SELECT a.index_name, 2 a.index_type, 3 a.partitioned, 4 b.partitioning_type, 5 b.locality, 6 a.visibility 7 FROM user_indexes a 8 LEFT OUTER JOIN user_part_indexes b ON a.index_name = b.index_name 9 ORDER BY index_name;INDEX_NAME INDEX_TYPE PARTITIONED PARTITIONING_TYPE LOCALITY VISIBILITY---------- ---------- ------------ ------------------ -------- ----------IDX_DEPTCO NORMAL NO VISIBLEPY_DEPTNOIDX_DEPTNO NORMAL NO VISIBLEIDX_MGR NORMAL NO VISIBLEPK_DEPT NORMAL NO VISIBLEPK_EMP NORMAL NO VISIBLET1_IDX1 NORMAL NO INVISIBLET1_IDX2 NORMAL YES RANGE GLOBAL VISIBLET1_IDX3 NORMAL YES RANGE LOCAL INVISIBLET1_IDX4 BITMAP YES RANGE LOCAL INVISIBLETEST_TAB_I NORMAL NO VISIBLED已选择 10 行。SQL>
转载地址：http://gytdi.baihongyu.com/