当前位置： 首页 > article >正文

Oracle 12c中在同一组列上创建多个索引

article 2025/2/27 4:18:07

在数据库管理与优化领域，索引是提高查询性能的关键工具之一。然而，在某些情况下，单一类型的索引可能不足以满足所有查询的需求。Oracle 12c引入了一个强大的新特性：允许在同一组列上创建多个索引，但仅一个可见，并且每个索引需具有不同的属性。

本文将详细介绍如何利用这一特性进行更高效的数据库管理和优化。

一、非分区表

首先，我们从创建一个简单的非分区表开始：

DROP TABLE t1 PURGE;

CREATE TABLE t1 (
id NUMBER,
description VARCHAR2(50),
created_date DATE
);

INSERT INTO t1 VALUES (1, 't1 ONE', TO_DATE('01/07/2025', 'DD/MM/YYYY'));
INSERT INTO t1 VALUES (2, 't1 TWO', TO_DATE('01/07/2025', 'DD/MM/YYYY'));
INSERT INTO t1 VALUES (3, 't1 THREE', TO_DATE('01/07/2026', 'DD/MM/YYYY'));

COMMIT;

接着，尝试创建两个索引来展示此功能：

  • 创建一个普通可见索引：
CREATE INDEX t1_idx1 ON t1(created_date) VISIBLE;
  • 尝试在同一列上创建一个不可见索引会导致错误（ORA-01408: such column list already indexed）。
CREATE INDEX t1_idx2 ON t1(created_date) INVISIBLE;
                           *
ERROR at line 1:
ORA-01408: such column list already indexed

SQL>
  • 除非索引类型不同，如创建一个位图索引：
CREATE BITMAP INDEX t1_idx3 ON t1(created_date) INVISIBLE;

二、分区表示例

接下来，我们将探讨分区表的应用场景。首先创建一个基于created_date列的范围分区表：

DROP TABLE t1 PURGE;
CREATE TABLE t1 (
id NUMBER,
description VARCHAR2(50),
created_date DATE
)
PARTITION BY RANGE (created_date) (
PARTITION part_2024 VALUES LESS THAN (TO_DATE('01/01/2025', 'DD/MM/YYYY')) TABLESPACE users,
PARTITION part_2025 VALUES LESS THAN (TO_DATE('01/01/2026', 'DD/MM/YYYY')) TABLESPACE users,
PARTITION part_2026 VALUES LESS THAN (TO_DATE('01/01/2027', 'DD/MM/YYYY')) TABLESPACE users
);

INSERT INTO t1 VALUES (1, 't1 ONE', TO_DATE('01/07/2024', 'DD/MM/YYYY'));
INSERT INTO t1 VALUES (2, 't1 TWO', TO_DATE('01/07/2025', 'DD/MM/YYYY'));
INSERT INTO t1 VALUES (3, 't1 THREE', TO_DATE('01/07/2026', 'DD/MM/YYYY'));
COMMIT;

然后，可以创建不同类型和配置的索引：

  • 全局可见索引：
CREATE INDEX t1_idx1 ON t1(created_date) GLOBAL VISIBLE;
  • 带有不同分区方案的全局不可见索引：
CREATE INDEX t1_idx2 ON t1(created_date) GLOBAL
PARTITION BY RANGE (created_date) (
PARTITION t1_p1 VALUES LESS THAN (TO_DATE('01/01/2025', 'DD/MM/YYYY')) TABLESPACE users,
PARTITION t1_p2 VALUES LESS THAN (TO_DATE('01/01/2026', 'DD/MM/YYYY')) TABLESPACE users,
PARTITION t1_p3 VALUES LESS THAN (MAXVALUE) TABLESPACE users
)
INVISIBLE;
  • 局部不可见索引：
CREATE INDEX t1_idx3 ON t1(created_date) LOCAL INVISIBLE;

以上方法均可以在created_date列成功创建索引，但类型必须为INVISIBLE。

三、为什么使用多个索引？

即使不可见，索引仍然会被维护。因此，在同一组列上拥有多个索引使您可以快速切换它们，从而更快地测试各种索引的影响。请记住，过多的索引会对表的DML性能产生影响，因此不建议在同一个列上创建多个索引。

下面的例子使用了上述创建的分区表和相关索引来检查索引的可见性：

-- 检查索引的可见性 .
COLUMN index_name FORMAT A10
COLUMN index_type FORMAT A10
COLUMN partitioned FORMAT A12
COLUMN locality FORMAT A8
COLUMN visibility FORMAT A10

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

SELECT *
FROM   t1
WHERE created_date = TO_DATE('01/07/2024', 'DD/MM/YYYY');

----------------------------------------------------------------------------------------------------------------------
| 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 |       |       |
----------------------------------------------------------------------------------------------------------------------

SET AUTOTRACE OFF

切换索引可见性，然后再次测试。

-- 切换索引.
ALTER INDEX t1_idx1 INVISIBLE;
ALTER INDEX t1_idx2 VISIBLE;


-- 检查索引的可见性.
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     VISIBLE
T1_IDX3    NORMAL     YES          RANGE     LOCAL      INVISIBLE
T1_IDX4    BITMAP     YES          RANGE     LOCAL      INVISIBLE

SQL>


-- 测试索引的可用性.
SET AUTOTRACE TRACE EXPLAIN

SELECT *
FROM   t1
WHERE created_date = TO_DATE('01/07/2024', 'DD/MM/YYYY');

-----------------------------------------------------------------------------------------------------------------------
| 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 |
-----------------------------------------------------------------------------------------------------------------------

SET AUTOTRACE OFF

再次切换索引可见性并测试。

-- 切换索引.
ALTER INDEX t1_idx2 INVISIBLE;
ALTER INDEX t1_idx3 VISIBLE;


-- 检查索引的可见性.
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

SELECT *
FROM   t1
WHERE created_date = TO_DATE('01/07/2024', 'DD/MM/YYYY');

----------------------------------------------------------------------------------------------------------------------
| 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 |
----------------------------------------------------------------------------------------------------------------------

SET AUTOTRACE OFF

再次切换索引可见性并测试。

-- 切换索引.
ALTER INDEX t1_idx3 INVISIBLE;
ALTER INDEX t1_idx4 VISIBLE;


-- 检查索引的可见性.
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

SELECT *
FROM   t1
WHERE created_date = TO_DATE('01/07/2024', 'DD/MM/YYYY');

----------------------------------------------------------------------------------------------------------------------
| 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 |
----------------------------------------------------------------------------------------------------------------------

SET AUTOTRACE OFF

通过以上的测试流程，我们可以得到同一个列上不同类型的索引的执行计划以及消耗情况，这样可以筛选出最为适合的索引创建方案。

四、使用不可见索引

通过设置参数OPTIMIZER_USE_INVISIBLE_INDEXES为TRUE，可以启用对不可见索引的支持，从而测试不同索引策略的效果：

ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES = TRUE;

这使得即使存在可见索引，也可以选择使用不可见索引，为数据库管理员提供了更大的灵活性来优化查询性能。

-- 检查索引的可见性 .
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
---------- ---------- ------------ --------- -------- ----------
PK_EMP     NORMAL     NO                              VISIBLE
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;


SET AUTOTRACE TRACE EXPLAIN

SELECT *
FROM   t1
WHERE created_date = TO_DATE('01/07/2024', 'DD/MM/YYYY');

----------------------------------------------------------------------------------------------------------------------
| 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 |       |       |
----------------------------------------------------------------------------------------------------------------------

SET AUTOTRACE OFF

五、结论

Oracle 12c提供的在同一组列上创建多个索引的功能极大地增强了数据库管理员调整和优化数据库的能力。通过合理配置不同类型的索引，可以有效地提升查询效率，同时保持系统的灵活性和可维护性。


http://www.kler.cn/a/550760.html

相关文章：

  • 利用亚马逊AI代码助手生成、构建和编译一个游戏应用（下）
  • 基于springboot社会志愿者服务管理平台(源码+lw+部署文档+讲解)，源码可白嫖!
  • 抖去推与超级编导矩阵系统技术深度对比，矩阵系统支持OEM
  • Ubuntu20.04安装IsaacSim4.5与IsaacLab2.0
  • 机器学习-02-机器学习算法思想以及在各行各业的应用
  • 成熟开发者需具备的能力
  • 【react8】如何在网页中直接引入react进行demo开发
  • OS-Genesis：基于逆向任务合成的 GUI 代理轨迹自动化生成
  • 若依Flowable工作流版本监听器使用方法
  • JSX和JS有什么区别
  • 从入门到精通：Postman 实用指南
  • SpringBoot3.x整合WebSocket
  • ES常用查询
  • 基于Dify、Ollama与DeepSeek的私有化AI知识库构建：工具作用、场景与优势分析
  • pythonrsa加密与sha256加密
  • 【亚马逊开发者账号02】终审问题SA+review_Pre-review+Doc.xlsx
  • 串的基本操作--数据结构
  • Copilot基于企业PPT模板生成演示文稿
  • AI前端开发赋能自主创业：ScriptEcho助你乘风破浪
  • 【kafka系列】Kafka如何保证消息不丢失？