CNDBA社区
概念
Multiple indexes can be created on the same set of columns as long as some characteristic is different. Qualifying characteristics are:
• B-tree versus bitmap
• Different partitioning strategies
• Unique versus non-unique
•
Providing the capability to create multiple indexes on the same set of columns enables transparent and seamless application migrations without the need to drop an existing index and re-create it with different attributes.
可以在同一组列上创建多个索引，只要某些特征是不同的。限制的特点是:
b -树和位图
不同的分区策略
独特的和非唯一
提供在同一组列上创建多个索引的功能，可以实现透明和无缝的应用迁移，而不需要删除现有索引并重新创建具有不同属性的索引。
当索引以某种方式不同时，可以在同一列上创建多个索引。例如，您可以在同一组列上创建一个b - tree索引和位图索引。
当在同一组列上有多个索引时，这些索引中只有一个可以同时可见，任何其他索引都必须是不可见的。
您可以在同一组列上创建不同的索引，因为它们提供了满足您需求的灵活性。您还可以在同一组列上创建多个索引，以执行应用程序迁移，而不删除现有索引，并使用不同的属性重新创建它。
不同类型的索引在不同的场景中是有用的。例如，b - tree索引通常在有许多并发事务的联机事务处理(OLTP)系统中使用，而位图索引通常用于数据仓库系统中，这些系统主要用于查询。类似地，局部和全局分区索引在不同的场景中是有用的。局部分区索引易于管理，因为分区维护操作会自动应用于它们。当您希望索引的分区方案与表的分区方案不同时，全局分区索引非常有用。实验
Oracle 12c允许同一组列上的多个索引，只提供一个索引是可见的，并且所有索引在某种程度上都是不同的。
[[email protected] ~]$ sqlplus "/as sysdba"
SQL*Plus: Release 12.1.0.2.0 Production on Sat Aug 19 22:39:08 2017
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> alter session set container=pdbcndba;
Session altered.
SQL> conn [email protected]
Connected.
1 普通表建索引
1.1 创建表t1
SQL> DROP TABLE t1 PURGE;
Table dropped.
SQL> CREATE TABLE t1 (
id NUMBER,
description VARCHAR2(50),
created_date DATE
);
Table created.
SQL> INSERT INTO t1 VALUES (1, 't1 ONE', TO_DATE('01/07/2014', 'DD/MM/YYYY'));
1 row created.
SQL>INSERT INTO t1 VALUES (2, 't1 TWO', TO_DATE('01/07/2015', 'DD/MM/YYYY'));
1 row created.
SQL>INSERT INTO t1 VALUES (3, 't1 THREE', TO_DATE('01/07/2016', 'DD/MM/YYYY'));
1 row created.
1.2 创建第一个索引
SQL> CREATE INDEX t1_idx1 ON t1(created_date) VISIBLE;
Index created.
1.3 如果我们尝试在同一列上创建一个不可见的索引，它失败了，因为它具有相同的属性，即现有的索引。
SQL> CREATE INDEX t1_idx2 ON t1(created_date) INVISIBLE;
CREATE INDEX t1_idx2 ON t1(created_date) INVISIBLE
*
ERROR at line 1:
ORA-01408: such column list already indexed
1.4 如果我们改变它，比如使它成为一个位图索引，它就能工作。
SQL> CREATE BITMAP INDEX t1_idx3 ON t1(created_date) INVISIBLE;
Index created.
2 分区表建索引
2.1 创建分区表
SQL> DROP TABLE t1 PURGE;
Table dropped.
SQL> CREATE TABLE t1 (
id NUMBER,
description VARCHAR2(50),
created_date DATE
)
PARTITION BY RANGE (created_date) (
PARTITION part_2014 VALUES LESS THAN (TO_DATE('01/01/2015', 'DD/MM/YYYY')) TABLESPACE users,
PARTITION part_2015 VALUES LESS THAN (TO_DATE('01/01/2016', 'DD/MM/YYYY')) TABLESPACE users,
PARTITION part_2016 VALUES LESS THAN (TO_DATE('01/01/2017', 'DD/MM/YYYY')) TABLESPACE users
);
Table created.
SQL> INSERT INTO t1 VALUES (1, 't1 ONE', TO_DATE('01/07/2014', 'DD/MM/YYYY'));
1 row created.
SQL> INSERT INTO t1 VALUES (2, 't1 TWO', TO_DATE('01/07/2015', 'DD/MM/YYYY'));
1 row created.
SQL> INSERT INTO t1 VALUES (3, 't1 THREE', TO_DATE('01/07/2016', 'DD/MM/YYYY'));
1 row created.
SQL> COMMIT;
Commit complete.
2.2 创建全局索引
SQL> CREATE INDEX t1_idx1 ON t1(created_date) GLOBAL VISIBLE;
Index created.
2.3 创建一些带有不同 partitioning schemes的索引。
SQL> CREATE INDEX t1_idx2 ON t1(created_date) GLOBAL
PARTITION 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;
Index created.
SQL> CREATE INDEX t1_idx3 ON t1(created_date) LOCAL INVISIBLE;
Index created.
--创建位图索引
SQL> CREATE BITMAP INDEX t1_idx4 ON t1(created_date) LOCAL INVISIBLE;
Index created.
在相同的列上有多个索引允许您快速切换它们，从而更快地测试各种索引的影响。请记住，在表上有太多索引的DML性能会受到影响，因此这应该是一个短期的情况。 下面的示例使用了在前一节中创建的分区表和相关索引。
--检查可见性的索引。
SQL> COLUMN index_name FORMAT A10
SQL> COLUMN index_type FORMAT A10
SQL> COLUMN partitioned FORMAT A12
SQL> COLUMN locality FORMAT A8
SQL> COLUMN visibility FORMAT A10
SQL> SELECT a.index_name,
a.index_type,
a.partitioned,
b.partitioning_type,
b.locality,
a.visibility
FROM user_indexes a
LEFT OUTER JOIN user_part_indexes b ON a.index_name = b.index_name
ORDER BY index_name;
INDEX_NAME INDEX_TYPE PARTITIONED PARTITION LOCALITY VISIBILITY
---------- ---------- ------------ --------- -------- ----------
T1_IDX1 NORMAL NO VISIBLE
T1_IDX2 NORMAL YES RANGE GLOBAL INVISIBLE
T1_IDX3 NORMAL YES RANGE LOCAL INVISIBLE
T1_IDX4 BITMAP YES RANGE LOCAL INVISIBLE
--测试索引使用，切换索引可见性和测试。
SQL> SET AUTOTRACE TRACE EXPLAIN
SQL> set line 500
SQL> SELECT *
FROM t1
WHERE created_date = TO_DATE('01/07/2014', 'DD/MM/YYYY'); 2 3
Execution Plan
----------------------------------------------------------
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 | | |
----------------------------------------------------------------------------------------------------------------------
SQL> SET AUTOTRACE OFF
SQL> ALTER INDEX t1_idx1 INVISIBLE;
Index altered.
SQL> ALTER INDEX t1_idx2 VISIBLE;
Index altered.
SQL> SELECT a.index_name,
a.index_type,
a.partitioned,
b.partitioning_type,
b.locality,
a.visibility
FROM user_indexes a
LEFT OUTER JOIN user_part_indexes b ON a.index_name = b.index_name
ORDER BY index_name; 2 3 4 5 6 7 8 9
INDEX_NAME INDEX_TYPE PARTITIONED PARTITION LOCALITY VISIBILITY
---------- ---------- ------------ --------- -------- ----------
T1_IDX1 NORMAL NO INVISIBLE
T1_IDX2 NORMAL YES RANGE GLOBAL VISIBLE
T1_IDX3 NORMAL YES RANGE LOCAL INVISIBLE
T1_IDX4 BITMAP YES RANGE LOCAL INVISIBLE
SQL> SET AUTOTRACE TRACE EXPLAIN
SQL> SELECT *
FROM t1
WHERE created_date = TO_DATE('01/07/2014', 'DD/MM/YYYY');
Execution Plan
----------------------------------------------------------
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 |
-----------------------------------------------------------------------------------------------------------------------
SQL> SET AUTOTRACE OFF
SQL> ALTER INDEX t1_idx2 INVISIBLE;
Index altered.
SQL> ALTER INDEX t1_idx3 VISIBLE;
Index altered.
SQL> SELECT a.index_name,
a.index_type,
a.partitioned,
b.partitioning_type,
b.locality,
a.visibility
FROM user_indexes a
LEFT OUTER JOIN user_part_indexes b ON a.index_name = b.index_name
ORDER BY index_name;
INDEX_NAME INDEX_TYPE PARTITIONED PARTITION LOCALITY VISIBILITY
---------- ---------- ------------ --------- -------- ----------
T1_IDX1 NORMAL NO INVISIBLE
T1_IDX2 NORMAL YES RANGE GLOBAL INVISIBLE
T1_IDX3 NORMAL YES RANGE LOCAL VISIBLE
T1_IDX4 BITMAP YES RANGE LOCAL INVISIBLE
SQL> SET AUTOTRACE TRACE EXPLAIN
SQL> SELECT *
FROM t1
WHERE created_date = TO_DATE('01/07/2014', 'DD/MM/YYYY');
Execution Plan
----------------------------------------------------------
Plan hash value: 597299548
----------------------------------------------------------------------------------------------------------------------
| 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 LOCAL INDEX ROWID BATCHED| T1 | 1 | 49 | 1 (0)| 00:00:01 | 1 | 1 |
|* 3 | INDEX RANGE SCAN | T1_IDX3 | 1 | | 1 (0)| 00:00:01 | 1 | 1 |
----------------------------------------------------------------------------------------------------------------------
SQL> SET AUTOTRACE OFF
SQL> ALTER INDEX t1_idx3 INVISIBLE;
Index altered.
SQL> ALTER INDEX t1_idx4 VISIBLE;
Index altered.
SQL> SELECT a.index_name,
a.index_type,
a.partitioned,
b.partitioning_type,
b.locality,
a.visibility
FROM user_indexes a
LEFT OUTER JOIN user_part_indexes b ON a.index_name = b.index_name
ORDER BY index_name;
INDEX_NAME INDEX_TYPE PARTITIONED PARTITION LOCALITY VISIBILITY
---------- ---------- ------------ --------- -------- ----------
T1_IDX1 NORMAL NO INVISIBLE
T1_IDX2 NORMAL YES RANGE GLOBAL INVISIBLE
T1_IDX3 NORMAL YES RANGE LOCAL INVISIBLE
T1_IDX4 BITMAP YES RANGE LOCAL VISIBLE
SQL> SET AUTOTRACE TRACE EXPLAIN
SQL> SELECT *
FROM t1
WHERE created_date = TO_DATE('01/07/2014', 'DD/MM/YYYY');
Execution Plan
----------------------------------------------------------
Plan hash value: 2425632905
----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 49 | 15 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE SINGLE | | 1 | 49 | 15 (0)| 00:00:01 | 1 | 1 |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| T1 | 1 | 49 | 15 (0)| 00:00:01 | 1 | 1 |
| 3 | BITMAP CONVERSION TO ROWIDS | | | | | | | |
|* 4 | BITMAP INDEX SINGLE VALUE | T1_IDX4 | | | | | 1 | 1 |
----------------------------------------------------------------------------------------------------------------------
SQL> SET AUTOTRACE OFF
3 OPTIMIZER_USE_INVISIBLE_INDEXES
与任何可用的不可见的索引一样，将优化的use_invisible_indexes参数设置为TRUE，就可以为优化器提供这些参数。
SQL> SELECT a.index_name,
a.index_type,
a.partitioned,
b.partitioning_type,
b.locality,
a.visibility
FROM user_indexes a
LEFT OUTER JOIN user_part_indexes b ON a.index_name = b.index_name
ORDER BY index_name;
INDEX_NAME INDEX_TYPE PARTITIONED PARTITION LOCALITY VISIBILITY
---------- ---------- ------------ --------- -------- ----------
T1_IDX1 NORMAL NO INVISIBLE
T1_IDX2 NORMAL YES RANGE GLOBAL INVISIBLE
T1_IDX3 NORMAL YES RANGE LOCAL INVISIBLE
T1_IDX4 BITMAP YES RANGE LOCAL VISIBLE
SQL> ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES = TRUE;
Session altered.
SQL> SET AUTOTRACE TRACE EXPLAIN
SQL>
SELECT *
FROM t1
WHERE created_date = TO_DATE('01/07/2014', 'DD/MM/YYYY');SQL> 2 3
Execution Plan
----------------------------------------------------------
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 | | |
----------------------------------------------------------------------------------------------------------------------
SQL> SET AUTOTRACE OFF
我们可以看到优化器选择使用T1_IDX1索引，即使它是不可见的，而且在同一列列表上有一个可见的索引(T1_IDX4)。
参考文档
http://docs.oracle.com/database/121/NEWFT/chapter12101.htm#NEWFT002
http://docs.oracle.com/database/121/ADMIN/indexes.htm#GUID-05F3C3B0-DB80-489C-B749-01653FDE8E09
http://docs.oracle.com/database/121/ADMIN/indexes.htm#ADMIN13279
版权声明：本文为博主原创文章，未经博主允许不得转载。
