Oracle联合索引使用情况测试报告

Posted on 2026-03-19 21:12 a-speed 阅读(4) 评论(0) 收藏举报

Oracle联合索引使用情况测试报告

1. 测试目的

验证当WHERE条件不包含联合索引首列时，Oracle数据库的索引使用策略，具体包括：

是否会导致全索引扫描
是否会导致索引跳跃扫描
不同查询条件下的执行计划差异

2. 测试环境

数据库版本: Oracle AI Database 26ai Enterprise Edition Release 23.26.1.0.0
连接用户: test01
连接字符串: sqlplus test01/test01@redhat8/PDBTEST
环境变量: source /home/ora23/.bash_profile

3. 测试表结构设计

3.1 创建测试表

-- 如果表存在先删除
DROP TABLE bb CASCADE CONSTRAINTS;
DROP TABLE aa CASCADE CONSTRAINTS;

-- 创建测试表aa
CREATE TABLE aa (
    A NUMBER(10),
    B VARCHAR2(50),
    C VARCHAR2(50),
    D VARCHAR2(100)
);

-- 创建测试表bb
CREATE TABLE bb (
    A NUMBER(10),
    E VARCHAR2(50),
    F VARCHAR2(50)
);

3.2 创建索引

-- 创建联合索引ABC
CREATE INDEX idx_aa_abc ON aa(A, B, C);

-- 给bb表也创建索引
CREATE INDEX idx_bb_aef ON bb(A, E, F);

3.3 插入测试数据

-- 插入测试数据
INSERT INTO aa VALUES (1, 'B1', 'C1', 'D1');
INSERT INTO aa VALUES (2, 'B2', 'C2', 'D2');
INSERT INTO aa VALUES (3, 'B1', 'C1', 'D3');
INSERT INTO aa VALUES (4, 'B2', 'C3', 'D4');
INSERT INTO aa VALUES (5, 'B1', 'C2', 'D5');

INSERT INTO bb VALUES (1, 'E1', 'F1');
INSERT INTO bb VALUES (2, 'E2', 'F2');
INSERT INTO bb VALUES (3, 'E3', 'F3');
INSERT INTO bb VALUES (4, 'E4', 'F4');
INSERT INTO bb VALUES (5, 'E5', 'F5');

COMMIT;

3.4 收集统计信息

-- 收集统计信息
EXEC DBMS_STATS.GATHER_TABLE_STATS('TEST01', 'AA');
EXEC DBMS_STATS.GATHER_TABLE_STATS('TEST01', 'BB');

4. 测试场景及结果

4.1 测试场景1：WHERE条件不包含索引首列A

SQL查询语句：

SELECT aa.*, bb.*
FROM aa
LEFT JOIN bb ON aa.A = bb.A
WHERE aa.B = 'B1' AND aa.C = 'C1';

执行计划：

Plan hash value: 957339319

----------------------------------------------------------------------------
| Id  | Operation          | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |            |     1 |    21 |     3   (0)| 00:00:01 |
|   1 |  NESTED LOOPS OUTER|            |     1 |    21 |     3   (0)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| AA         |     1 |    12 |     2   (0)| 00:00:01 |
|*  3 |   INDEX RANGE SCAN | IDX_BB_AEF |     1 |     9 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("AA"."C"='C1' AND "AA"."B"='B1')
   3 - access("AA"."A"="BB"."A"(+))

Note
-----
   - this is an adaptive plan

分析结果：

AA表使用全表扫描（TABLE ACCESS FULL）
BB表使用索引范围扫描（INDEX RANGE SCAN）
原因：查询需要返回D列（不在索引中），且WHERE条件不包含索引首列A

4.2 测试场景2：WHERE条件包含索引首列A

SQL查询语句：

SELECT aa.*, bb.*
FROM aa
LEFT JOIN bb ON aa.A = bb.A
WHERE aa.A = 1 AND aa.B = 'B1' AND aa.C = 'C1';

执行计划：

Plan hash value: 3425590676

----------------------------------------------------------------------------
| Id  | Operation          | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |            |     1 |    21 |     3   (0)| 00:00:01 |
|   1 |  MERGE JOIN OUTER  |            |     1 |    21 |     3   (0)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| AA         |     1 |    12 |     2   (0)| 00:00:01 |
|   3 |   BUFFER SORT      |            |     1 |     9 |     1   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN| IDX_BB_AEF |     1 |     9 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("AA"."A"=1 AND "AA"."C"='C1' AND "AA"."B"='B1')
   4 - access("BB"."A"(+)=1)

分析结果：

即使WHERE条件包含A字段，AA表仍然使用全表扫描
BB表仍然使用索引范围扫描
原因：表数据量较小（仅5行），全表扫描成本低于索引扫描+回表查询

4.3 测试场景3：只查询索引覆盖的列

SQL查询语句：

SELECT aa.A, aa.B, aa.C
FROM aa
LEFT JOIN bb ON aa.A = bb.A
WHERE aa.B = 'B1' AND aa.C = 'C1';

执行计划：

Plan hash value: 1504755246

----------------------------------------------------------------------------
| Id  | Operation          | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |            |     1 |    12 |     2   (0)| 00:00:01 |
|   1 |  NESTED LOOPS OUTER|            |     1 |    12 |     2   (0)| 00:00:01 |
|*  2 |   INDEX SKIP SCAN  | IDX_AA_ABC |     1 |     9 |     1   (0)| 00:00:01 |
|*  3 |   INDEX RANGE SCAN | IDX_BB_AEF |     1 |     3 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("AA"."B"='B1' AND "AA"."C"='C1')
       filter("AA"."C"='C1' AND "AA"."B"='B1')
   3 - access("AA"."A"="BB"."A"(+))

Note
-----
   - this is an adaptive plan

分析结果：

AA表使用了索引跳跃扫描（INDEX SKIP SCAN）
BB表使用索引范围扫描
原因：查询只需要索引中的列（A, B, C），不需要回表查询，优化器选择了索引跳跃扫描

5. 测试结论

5.1 索引使用策略

全表扫描：
- 场景：需要查询索引外的列且WHERE条件不包含索引首列
- 示例：SELECT aa.* ... WHERE aa.B = 'B1' AND aa.C = 'C1'
索引跳跃扫描（INDEX SKIP SCAN）：
- 场景：只查询索引覆盖的列且WHERE条件不包含索引首列
- 示例：SELECT aa.A, aa.B, aa.C ... WHERE aa.B = 'B1' AND aa.C = 'C1'
索引范围扫描（INDEX RANGE SCAN）：
- 场景：WHERE条件包含索引首列或用于连接条件
- 示例：连接条件 aa.A = bb.A 使得bb表的索引被有效利用

5.2 关键发现

联合索引的前缀原则：
- 当WHERE条件不包含联合索引首列时，索引无法通过范围扫描高效使用
- 但在特定条件下（索引覆盖查询）可以使用索引跳跃扫描
索引覆盖的重要性：
- 当查询只需要索引中的列时，即使不包含索引首列，也能使用索引跳跃扫描
- 这避免了全表扫描，提高了查询效率
小表的优化策略：
- 对于数据量较小的表，优化器可能选择全表扫描而不是索引扫描
- 这是因为全表扫描的成本可能低于索引扫描+回表查询的成本

6. 优化建议

6.1 针对当前场景的优化

创建合适的索引：
- 如果频繁查询B、C字段且不包含A字段，建议创建单独的索引：
```
CREATE INDEX idx_aa_bc ON aa(B, C);
```
- 或者创建以B、C开头的联合索引：
```
CREATE INDEX idx_aa_bca ON aa(B, C, A);
```
调整查询语句：
- 如果只需要A、B、C字段，保持当前查询方式（索引跳跃扫描）
- 如果需要D字段，考虑在WHERE条件中包含A字段或创建新索引
收集统计信息：
- 确保统计信息及时更新，帮助优化器做出正确决策：
```
EXEC DBMS_STATS.GATHER_TABLE_STATS('TEST01', 'AA');
```

6.2 索引设计最佳实践

遵循前缀原则：
- 联合索引应将最常用的查询条件列放在前面
- 确保查询条件包含索引的前缀列
考虑索引覆盖：
- 如果查询只需要索引中的列，可以避免回表查询
- 这是提高查询效率的重要手段
定期维护索引：
- 重建或重新组织索引以提高性能
- 监控索引使用情况，删除未使用的索引

7. 测试结论总结

本次测试验证了Oracle数据库在不同查询条件下的索引使用策略：

原始问题答案：
- 当WHERE条件不包含联合索引首列且需要查询索引外的列时，会导致全表扫描
- 当只查询索引覆盖的列时，会使用索引跳跃扫描（INDEX SKIP SCAN）
核心发现：
- 联合索引的使用遵循"前缀原则"
- 索引跳跃扫描是Oracle优化器的重要特性，可以在特定条件下有效使用非前缀列查询
- 查询所需的列是否都在索引中是决定是否使用索引的关键因素
优化方向：
- 根据实际查询模式创建合适的索引
- 考虑索引覆盖的可能性
- 定期维护和监控索引使用情况

通过本次测试，我们深入了解了Oracle联合索引的使用机制，为数据库性能优化提供了重要依据。

刷新页面返回顶部

A-speed

Oracle联合索引使用情况测试报告

Oracle联合索引使用情况测试报告

1. 测试目的

2. 测试环境

3. 测试表结构设计

3.1 创建测试表

3.2 创建索引

3.3 插入测试数据

3.4 收集统计信息

4. 测试场景及结果

4.1 测试场景1：WHERE条件不包含索引首列A

4.2 测试场景2：WHERE条件包含索引首列A

4.3 测试场景3：只查询索引覆盖的列

5. 测试结论

5.1 索引使用策略

5.2 关键发现

6. 优化建议

6.1 针对当前场景的优化

6.2 索引设计最佳实践

7. 测试结论总结