Oracle数据库SQL性能优化：索引未生效及全表扫描问题求助
2026-6-16
Oracle SQL查询性能优化问题：索引未生效导致全表扫描耗时过长
我正在优化Oracle数据库的SQL查询性能，尝试为相关列创建索引，但索引未按预期生效，查询仍执行全表扫描。从执行计划来看，大量连接操作采用全表扫描，严重影响性能，最近一次执行耗时长达一小时。
查询语句
INSERT INTO VM_PEDIDO SELECT * FROM V_PEDIDO_V1 WHERE CD_TIPONOTA NOT IN ('190','308','6090');
执行计划
| Id | Operation | Name | Rows | Bytes | Cost | Time | ------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 22760 | 24M | 231K | 00:00:10| | 1 | HASH JOIN RIGHT OUTER | | 22760 | 24M | 231K | 00:00:10| | 2 | VIEW | V_ITEM_MASCARA | 5534 | 1432K | 41 | 00:00:01| | 4 | TABLE ACCESS BY INDEX ROWID BATCHED | ETL_V_ITEM_MASCARA_CONS | 3697 | 108K | 27 | 00:00:01| | 5 | INDEX RANGE SCAN | ETL_V_ITEM_MASCARA_CONS_INDEX1 | 3697 | | 7 | 00:00:01| | 6 | TABLE ACCESS BY INDEX ROWID BATCHED | ETL_V_ITEM_MASCARA_BASE | 1837 | 58784 | 14 | 00:00:01| | 7 | INDEX RANGE SCAN | ETL_V_ITEM_MASCARA_BASE_INDEX2 | 1837 | | 4 | 00:00:01| | 8 | HASH JOIN RIGHT OUTER | | 9990 | 8253K | 231K | 00:00:10| | 9 | TABLE ACCESS FULL | TAUX_TAB_ICMS | 11760 | 149K | 17 | 00:00:01| | 10 | HASH JOIN RIGHT OUTER | | 9990 | 8126K | 231K | 00:00:10| | 11 | TABLE ACCESS FULL | ETL_V_ITEM_PESO_CONS | 3728 | 22368 | 5 | 00:00:01| | 12 | HASH JOIN RIGHT OUTER | | 9990 | 8068K | 231K | 00:00:10| | 13 | TABLE ACCESS FULL | TAUX_PRODUTO | 347 | 3123 | 3 | 00:00:01| | 14 | HASH JOIN RIGHT OUTER | | 9990 | 7980K | 231K | 00:00:10| | 15 | TABLE ACCESS FULL | TAUX_PRODSEMFARDO | 29 | 203 | 3 | 00:00:01| | 16 | HASH JOIN | | 9990 | 7912K | 231K | 00:00:10| | 17 | TABLE ACCESS FULL | TAUX_PRODUTOS | 13294 | 363K | 25 | 00:00:01| | 18 | HASH JOIN | | 9990 | 7638K | 231K | 00:00:10| | 19 | HASH JOIN RIGHT OUTER | | 66 | 34386 | 227K | 00:00:09| | 20 | TABLE ACCESS FULL | TAUX_FORMA_PGTO | 825 | 9075 | 4 | 00:00:01| | 21 | HASH JOIN OUTER | | 66 | 33660 | 227K | 00:00:09| | 22 | HASH JOIN OUTER | | 6 | 3024 | 227K | 00:00:09| | 23 | HASH JOIN OUTER | | 6 | 2844 | 227K | 00:00:09| | 24 | HASH JOIN OUTER | | 6 | 2772 | 227K | 00:00:09| | 25 | HASH JOIN OUTER | | 5 | 2250 | 227K | 00:00:09| | 26 | JOIN FILTER CREATE | :BF0000 | 5 | 795 | 227K | 00:00:09| | 27 | MERGE JOIN OUTER | | 5 | 795 | 227K | 00:00:09| | 28 | SORT JOIN | | 5 | 735 | 227K | 00:00:09| | 29 | VIEW | V_PEDIDO | 5 | 735 | 227K | 00:00:09| | 30 | UNION-ALL | | | | | | | 31 | TABLE ACCESS FULL | ETL_V_PEDIDO_CONS | 4 | 324 | 216K | 00:00:09| | 32 | TABLE ACCESS FULL | ETL_V_PEDIDO_BASE | 1 | 77 | 11034 | 00:00:01| | 33 | FILTER | | | | | | | 34 | SORT JOIN | | 1 | 12 | 4 | 00:00:01| | 35 | TABLE ACCESS FULL | VM_CAD_PEDI_JUROS | 1 | 12 | 3 | 00:00:01| | 36 | VIEW | | 46 | 13386 | 7 | 00:00:01| | 37 | HASH GROUP BY | | 46 | 736 | 7 | 00:00:01| | 38 | VIEW | V_BLOQ_CREDITO | 46 | 736 | 6 | 00:00:01| | 39 | JOIN FILTER USE | :BF0000 | | | | | | 40 | UNION-ALL | | | | | | | 41 | TABLE ACCESS FULL | ETL_V_BLOQ_CREDITO_CONS | 42 | 630 | 3 | 00:00:01| | 42 | TABLE ACCESS FULL | ETL_V_BLOQ_CREDITO_BASE | 4 | 60 | 3 | 00:00:01| | 43 | TABLE ACCESS FULL | TAUX_DESCONTO_CLIENTE | 624 | 7488 | 3 | 00:00:01| | 44 | TABLE ACCESS FULL | TAUX_GRUPOCLI | 2602 | 31224 | 5 | 00:00:01| | 45 | TABLE ACCESS FULL | TAUX_CLIENTES2 | 33325 | 976K | 77 | 00:00:01| | 46 | TABLE ACCESS FULL | TAUX_TIPOFRETE | 52 | 312 | 3 | 00:00:01| | 47 | VIEW | V_ITEM_PEDIDO_V2 | 1524K | 380M | 4629 | 00:00:01| | 48 | UNION-ALL | | | | | | | 49 | TABLE ACCESS FULL | ETL_V_ITEM_PEDIDO_V2_CONS | 14 | 149 | 5 | 00:00:01| | 50 | TABLE ACCESS FULL | ETL_V_ITEM_PEDIDO_V2_BASE | 655 | 6332 | 16 | 00:00:01|
已尝试的索引方案
- 针对视图
V_PEDIDO_V1创建的函数索引：
CREATE INDEX idx_cd_tiponota_check ON V_PEDIDO_V1 ( CASE WHEN CD_TIPONOTA IN ('190', '308', '6090') THEN NULL ELSE CD_TIPONOTA END );
- 针对表
ETL_V_PEDIDO_BASE创建的单列索引：
CREATE INDEX idx_cd_tiponota_check ON ETL_V_PEDIDO_BASE(CODTIPONOTA)
成本分析推测
我推测主要查询成本来自执行计划的第31行（TABLE ACCESS FULL ETL_V_PEDIDO_CONS），且认为成本最高的部分如下：
内容的提问来源于stack exchange，提问作者Thobias Gonçalves
相关产品推荐
相关产品推荐
