服装网站建设目的作用是什么,免费seo网站推荐一下软件,企业网站推广过程,重庆市造价信息网官网在数据库管理与优化领域#xff0c;索引是提高查询性能的关键工具之一。然而#xff0c;在某些情况下#xff0c;单一类型的索引可能不足以满足所有查询的需求。Oracle 12c引入了一个强大的新特性#xff1a;允许在同一组列上创建多个索引#xff0c;但仅一个可见#xf…在数据库管理与优化领域索引是提高查询性能的关键工具之一。然而在某些情况下单一类型的索引可能不足以满足所有查询的需求。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 indexedSQL
除非索引类型不同如创建一个位图索引
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 A10SELECT a.index_name,a.index_type,a.partitioned,b.partitioning_type,b.locality,a.visibility
FROM user_indexes aLEFT 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 INVISIBLESQL-- 测试索引的可用性.
SET AUTOTRACE TRACE EXPLAINSELECT *
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 aLEFT 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 INVISIBLESQL-- 测试索引的可用性.
SET AUTOTRACE TRACE EXPLAINSELECT *
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 aLEFT 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 INVISIBLESQL-- 测试索引的可用性.
SET AUTOTRACE TRACE EXPLAINSELECT *
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 aLEFT 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 VISIBLESQL-- 测试索引的可用性.
SET AUTOTRACE TRACE EXPLAINSELECT *
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 aLEFT 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 VISIBLESQL-- 允许优化器使用不可见索引.
ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES TRUE;SET AUTOTRACE TRACE EXPLAINSELECT *
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提供的在同一组列上创建多个索引的功能极大地增强了数据库管理员调整和优化数据库的能力。通过合理配置不同类型的索引可以有效地提升查询效率同时保持系统的灵活性和可维护性。
