优化Oracle中的联接查询

Question

我有一个查询需要17秒才能执行完。我已经在FIPS、STR_DT和END_DT上应用了索引，但仍然需要很长时间。您有什么建议可以提高性能吗？

我的查询：

SELECT /*+ALL_ROWS*/ K_LF_SVA_VA.NEXTVAL VAL_REC_ID,       a.REC_ID,
   b.VID,
   1 VA_SEQ,
   51 VA_VALUE_DATATYPE,
   b.VALUE VAL_NUM,
   SYSDATE CREATED_DATE,
   SYSDATE UPDATED_DATE
   FROM CTY_REC  a JOIN FIPS_CONS b
 ON a.FIPS=b.FIPS AND a.STR_DT=b.STR_DT AND a.END_DT=b.END_DT;


 DESC CTY_REC;

 Name                Null Type          
------------------- ---- ------------- 
REC_ID                   NUMBER(38)    
DATA_SOURCE_DATE         DATE          
STR_DT                   DATE          
END_DT                   DATE          
VID_RECSET_ID            NUMBER        
VID_VALSET_ID            NUMBER        
FIPS                     VARCHAR2(255)


DESC FIPS_CONS;

Name          Null     Type          
------------- -------- ------------- 
STR_DT                 DATE          
END_DT                 DATE          
FIPS                   VARCHAR2(255) 
VARIABLE               VARCHAR2(515) 
VALUE                  NUMBER        
VID         NOT NULL   NUMBER        

执行计划：

Plan hash value: 919279614

--------------------------------------------------------------
| Id  | Operation           | Name                           |
--------------------------------------------------------------
|   0 | SELECT STATEMENT    |                                |
|   1 |  SEQUENCE           | K_VAL                          |
|   2 |   HASH JOIN         |                                |
|   3 |    TABLE ACCESS FULL| CTY_REC                        |
|   4 |    TABLE ACCESS FULL| FIPS_CONS                      |
--------------------------------------------------------------

我已经为我的查询添加了表描述和执行计划。

- dang

---

表中有多少行？查询中有多少行被投影出来？与其他表中的行不匹配的行占比是多少？序列的缓存值是多少？ - David Aldridge

你能展示完整的执行计划吗？包括行估计和成本列（也许还有TmpSpc）。 - user330315

Answer 1

乍一看，如果没有关于您使用的序列配置、每个表中的行数以及从查询中预计的总行数的信息，执行计划您所拥有的可能是返回所有行最有效的计划。

优化器明显认为索引对性能没有好处，这种情况通常更容易发生在优化所有行而不是第一行的情况下。基于索引的访问是单块和逐行进行的，因此在按块多次进行全面扫描时可能本质上比其慢。

Oracle正在使用的哈希连接是连接数据集的极其有效的方法。除非散列表太大而溢出到磁盘，否则总成本仅略高于两个表的完全扫描。我们需要更详细的执行统计信息才能确定散列表是否溢出到磁盘，如果确实是，则解决方案可能只需修改内存管理而不是索引。

你的SQL执行可能也会延迟调用该序列，如果序列的缓存值非常低而记录数很高。需要有关此方面的更多信息 - 如果您需要为每行生成连续标识符，则可以使用 ROWNUM。

- David Aldridge

---

我喜欢这个答案。你是对的，全表扫描本身并不是坏事。有时它们是访问数据最有效的方式。再次肯定，哈希连接是连接两个表的好技术。 - Thorsten Kettner

Answer 2

这个表至少应该有以下两个索引：

• CTY_REC(FIPS, STR_DT, END_DT)
• FIPS_CONS(FIPS, STR_DT, END_DT)

如果使用覆盖索引，可以进一步提高速度：

• CTY_REC(FIPS, STR_DT, END_DT, REC_ID)
• FIPS_CONS(FIPS, STR_DT, END_DT, VALUE, VID)

- Thorsten Kettner

---

当包括所需的行ID和空白空间时，这些索引可能会比表格更大。 - David Aldridge

@David Aldridge：是的，有时索引会变得很大，但我不认为这是不使用它们的理由。 - Thorsten Kettner

Answer 3

如果您想驱动优化器使用索引，
请将 /*+ all_rows */ 替换为 /*+ first_rows */

- David דודו Markovitz

Answer 4

这基本上就是你的查询：

SELECT . . .
FROM CTY_REC a JOIN
     FIPS_CONS b
     ON a.FIPS = b.FIPS AND a.STR_DT = b.STR_DT AND a.END_DT = b.END_DT;

你想在 (FIPS, STR_DT, END_DT) 上创建一个组合索引，可能是在两个表上：

create index idx_cty_rec_3 on cty_rec(FIPS, STR_DT, END_DT);
create index idx_fipx_con_3 on cty_rec(FIPS, STR_DT, END_DT);

实际上，可能只需要一个，但同时拥有两个可以让优化器在改进查询时有更多的选择。

- Gordon Linoff