如何在数据库中找到最新的 SQL 语句？

Question

RBarryYoung

Asked: 2017-03-31 13:29:51 +0800 CST2017-03-31 13:29:51 +0800 CST 2017-03-31 13:29:51 +0800 CST

外部联接抑制索引使用？

772

我有一个客户端程序正在执行一个针对将一个表外部连接到另一个表的视图的查询。性能很差，我一直在尝试通过添加正确的索引来调整它。有问题的查询实际上只使用第二个表，所以我一直在直接针对该表进行测试。

我发现（几个）索引可以很好地用于针对表的查询，但是当我将其切换为使用视图时，它们停止使用任何索引，而是对两个表进行了全面扫描。由于这些表很大（每个表 2-3 百万行），因此速度非常慢。

为了简单地测试，我更改了查询以绕过并将外部联接合并到查询本身中。这成功地重现了问题，但留下了为什么外连接不使用索引的谜团。

这是表格，包含我在测试时添加的所有索引：

  CREATE TABLE TEST_DATA 
   (ID NUMBER(11,0)  PRIMARY KEY, 
    FORMATTED_RESULT VARCHAR2(255 BYTE), 
    F_RESULT NUMBER, 
    IDNUM NUMBER(11,0), 
    IDNUM_DESCRIPTION VARCHAR2(128 BYTE), 
    LAB_NUMBER NUMBER(11,0), 
    SEQ_NUMBER NUMBER(11,0),
    ORDERNO NUMBER(11,0),
    SUPPL_FORMATTED_RESULT VARCHAR2(255 BYTE), 
    SUPPL_IDNUM NUMBER(11,0), 
    SUPPL_IDNUM_DESCRIPTION VARCHAR2(128 BYTE), 
    SUPPL_UNIT VARCHAR2(16 BYTE)
   ) ;

  CREATE UNIQUE INDEX TEST_LN_SQN_ORDER ON TEST_DATA (LAB_NUMBER, SEQ_NUMBER, ORDERNO) ;
  CREATE INDEX TEST_LN_SQN ON TEST_DATA (LAB_NUMBER, SEQ_NUMBER) ;
  CREATE INDEX TD_CUIDD_CUFR ON TEST_DATA (UPPER(COALESCE(SUPPL_IDNUM_DESCRIPTION,IDNUM_DESCRIPTION)), UPPER(COALESCE(SUPPL_FORMATTED_RESULT,FORMATTED_RESULT))) ;
  CREATE INDEX TD_UFR_IDN ON TEST_DATA (UPPER(FORMATTED_RESULT), IDNUM) ;
  CREATE INDEX TD_UIDD_UFR ON TEST_DATA (UPPER(IDNUM_DESCRIPTION), UPPER(FORMATTED_RESULT)) ;
  CREATE INDEX TD_CUFR_CIDN_SN_LN ON TEST_DATA (UPPER(COALESCE(SUPPL_FORMATTED_RESULT,FORMATTED_RESULT)), COALESCE(SUPPL_IDNUM,IDNUM), SEQ_NUMBER, LAB_NUMBER) ;
  CREATE INDEX TD_SN_LN_CUFR_CIDN ON TEST_DATA (SEQ_NUMBER, LAB_NUMBER, UPPER(COALESCE(SUPPL_FORMATTED_RESULT,FORMATTED_RESULT)), COALESCE(SUPPL_IDNUM,IDNUM)) ;
  CREATE INDEX TD_CUFR_CIDN ON TEST_DATA (UPPER(COALESCE(SUPPL_FORMATTED_RESULT,FORMATTED_RESULT)), COALESCE(SUPPL_IDNUM,IDNUM)) ;

这是另一个表（我们并没有真正用于此查询的表）

  CREATE TABLE REQUEST_INFO 
   (NUMBER(11,0) PRIMARY KEY, 
    CHARGE_CODE VARCHAR2(32 BYTE), 
    LAB_NUMBER NUMBER(11,0), 
    SEQ_NUMBER NUMBER(11,0)
   ) ;

  CREATE INDEX RI_LN_SN ON REQUEST_INFO (LAB_NUMBER, SEQ_NUMBER) ;
  CREATE INDEX RI_SN_LN ON REQUEST_INFO (SEQ_NUMBER, LAB_NUMBER) ;

因此，首先，这是直接针对单个表的查询，它成功使用了其中一个索引。

-- GOOD, Uses index : TD_CUFR_CIDN_SN_LN
select td.LAB_NUMBER 
from test_DATA td 
where UPPER(COALESCE(SUPPL_FORMATTED_RESULT,FORMATTED_RESULT))='491(10)376'
  and COALESCE(TD.SUPPL_IDNUM, TD.IDNUM)=40549 
;

现在这是使用带有内部连接的两个表的查询。这也使用了索引并且运行速度很快。

-- GOOD, Uses indexes : TD_CUFR_CIDN_SN_LN AND RI_SN_LN
select TD.LAB_NUMBER  
from REQUEST_INFO RI 
JOIN TEST_DATA TD ON  TD.LAB_NUMBER = RI.LAB_NUMBER AND TD.SEQ_NUMBER = RI.SEQ_NUMBER 
where UPPER(COALESCE(TD.SUPPL_FORMATTED_RESULT,TD.FORMATTED_RESULT))='491(10)376'
  and COALESCE(TD.SUPPL_IDNUM, TD.IDNUM)=40549

这是与左外连接相同的查询，因为它是在视图中编写的。这不使用任何索引并且运行非常缓慢。

-- BAD, does not use indexes
select TD.LAB_NUMBER 
from REQUEST_INFO RI 
LEFT JOIN TEST_DATA TD ON  TD.LAB_NUMBER = RI.LAB_NUMBER AND TD.SEQ_NUMBER = RI.SEQ_NUMBER 
where UPPER(COALESCE(TD.SUPPL_FORMATTED_RESULT,TD.FORMATTED_RESULT))='491(10)376'
  and COALESCE(TD.SUPPL_IDNUM, TD.IDNUM)=40549 
;

现在在任何人说之前：这个查询实际上在逻辑上与前一个相同。这是因为 WHERE 子句对外部表 (TD) 中的列进行过滤，这有效/逻辑地将外部联接转换为内部联接（这就是为什么在 ON 子句与 WHERE 子句中是否出现条件很重要的原因）。

现在，为了增加怪异，我决定看看如果我让从外到内的强制更加明确会发生什么：

-- GOOD, Uses indexes : TD_CUFR_CIDN_SN_LN AND RI_SN_LN
select TD.LAB_NUMBER 
from REQUEST_INFO RI 
LEFT JOIN TEST_DATA TD ON  TD.LAB_NUMBER = RI.LAB_NUMBER AND TD.SEQ_NUMBER = RI.SEQ_NUMBER 
where UPPER(COALESCE(TD.SUPPL_FORMATTED_RESULT,TD.FORMATTED_RESULT))='491(10)376'
  and COALESCE(TD.SUPPL_IDNUM, TD.IDNUM)=40549 
and TD.LAB_NUMBER IS NOT NULL
;

令人难以置信的是，这奏效了！

所以这里的问题是，1) 为什么 Oracle 不自己解决这个问题？

并且 2) 是否有一些设置或索引等我可以创建，让 Oracle 正确地解决这个问题并使用索引？

其他注意事项：

该视图被各种其他查询和客户端使用，所以我不能只将它更改为这个查询的内部联接。
客户端正在生成查询，因此很难/几乎不可能使用古怪的特殊情况来更改查询，例如：“将此视图用于此数据，除非您只需要此表中的这些列，然后使用不同的查看“，或”当您需要这些列并且只需要该表中的这些列时，然后将“IS NOT NULL”添加到 WHERE 子句“

欢迎任何建议或见解。

更新： 我也刚刚在 Oracle 11g 上尝试过，我在那里得到了完全相同的结果。

根据请求，这里是解释计划输出，首先是好的版本，它使用索引：

Rows      Plan                                       COST    Predicates
        3 SELECT STATEMENT                                 8 
        3  HASH JOIN                                       8 Access:TD.LAB_NUMBER=RI.LAB_NUMBER AND TD.SEQ_NUMBER=RI.SEQ_NUMBER
        3   NESTED LOOPS                                   8 
             STATISTICS COLLECTOR
        3     INDEX RANGE SCAN TD_CUFR_CIDN_SN_LN          4 Access:UPPER(COALESCE(SUPPL_FORMATTED_RESULT,FORMATTED_RESULT))='491(10)376' AND COALESCE(SUPPL_IDNUM,IDNUM)=40549, Filter:TD.LAB_NUMBER IS NOT NULL
        1    INDEX RANGE SCAN RI_SN_LN                     2 Access:TD.SEQ_NUMBER=RI.SEQ_NUMBER AND TD.LAB_NUMBER=RI.LAB_NUMBER
        1   INDEX FAST FULL SCAN RI_SN_LN                  2

现在是坏版本：

Rows      Plan                                       COST    Predicates
 31939030 SELECT STATEMENT                            910972
           FILTER                                             Filter:UPPER(COALESCE(SUPPL_FORMATTED_RESULT,FORMATTED_RESULT))='491(10)376' AND COALESCE(SUPPL_IDNUM,IDNUM)=40549
 31939030   HASH JOIN OUTER                           910972 Access:TD.LAB_NUMBER(+)=RI.LAB_NUMBER AND TD.SEQ_NUMBER(+)=RI.SEQ_NUMBER
  6213479    TABLE ACCESS FULL REQUEST_INFO            58276
 56276228    TABLE ACCESS FULL TEST_DATA              409612

2 个回答

Voted

Joe Obbish · Answer 1 · 2017-04-04T18:05:44+08:00

这主要是对第 1 部分的部分回答，并带有一些猜测。你我都知道以下查询：

select TD.LAB_NUMBER 
from REQUEST_INFO RI 
LEFT JOIN TEST_DATA TD ON  TD.LAB_NUMBER = RI.LAB_NUMBER AND TD.SEQ_NUMBER = RI.SEQ_NUMBER 
where UPPER(COALESCE(TD.SUPPL_FORMATTED_RESULT,TD.FORMATTED_RESULT))='491(10)376'
  and COALESCE(TD.SUPPL_IDNUM, TD.IDNUM)=40549;

相当于这个查询：

select TD.LAB_NUMBER 
from REQUEST_INFO RI 
INNER JOIN TEST_DATA TD ON 
TD.LAB_NUMBER = RI.LAB_NUMBER 
AND TD.SEQ_NUMBER = RI.SEQ_NUMBER 
AND UPPER(COALESCE(TD.SUPPL_FORMATTED_RESULT,TD.FORMATTED_RESULT))='491(10)376'
and COALESCE(TD.SUPPL_IDNUM, TD.IDNUM)=40549;

但是，这并不意味着 Oracle 知道这两个查询是等价的。Oracle 需要两个查询等价才能使用TD_CUFR_CIDN_SN_LN索引。我们在这里希望的是OUTER JOIN转换INNER JOIN。我没有很幸运地找到关于这个的好信息，所以让我们看看解释计划：

添加TD.LAB_NUMBER IS NOT NULL到WHERE子句是让 Oracle 知道可以OUTER JOIN进行INNER JOIN转换的一种非常直接的方法。通过查看突出显示的行，我们可以看到它发生了。我认为几乎任何列都允许转换，尽管选择错误的列可能会改变查询结果。

如果我们尝试稍微复杂一些的过滤器，例如(TD.LAB_NUMBER IS NOT NULL OR TD.SEQ_NUMBER IS NOT NULL)连接转换就不会发生：

我们可以推断出这OUTER JOIN确实是一个，INNER JOIN但查询优化器可能没有被编程来做到这一点。在原始查询中，您的COALESCE()表达式可能过于复杂，查询优化器无法应用查询转换。

这是一些示例的数据库小提琴。

对于第二个问题，我想不出办法解决这个问题。您可以尝试利用表消除。正如您所说，此查询甚至不需要该REQUEST_INFO表。但是，有一些限制：

目前有一些表格消除的限制：

不支持多列主键-外键约束。

在查询的其他地方引用连接键将阻止表消除。对于内部连接，连接两侧的连接键是等效的，但如果查询包含对表中连接键的其他引用，否则这些引用可能会被消除，这会阻止消除。一种解决方法是重写查询以引用另一个表中的连接键（我们意识到这并不总是可能的）。

也许有一种方法可以解决这个问题，但我无法解决这些限制。

Oracle Panda · Answer 2 · 2019-04-03T05:59:42+08:00

Oracle Panda

2019-04-03T05:59:42+08:002019-04-03T05:59:42+08:00

将 Coalesce 语句替换为OR语句，因为在 Where 子句的左侧添加函数将不会使用索引，除非左侧函数使用基于函数的索引进行索引，因此请按如下方式更改查询。SUPPL_FORMATTED_RESULT和上的单独索引FORMATTED_RESULT应该具有upper以下查询使用索引访问的功能。

注意：如果数据存在偏差，并且值“491(10)376”和 40549 的记录数更多，oracle 将跳过索引并使用全表扫描。

select TD.LAB_NUMBER 
from REQUEST_INFO RI 
LEFT JOIN TEST_DATA TD ON  TD.LAB_NUMBER = RI.LAB_NUMBER AND TD.SEQ_NUMBER = RI.SEQ_NUMBER 
where (UPPER(TD.SUPPL_FORMATTED_RESULT) ='491(10)376' or   
       UPPER(TD.FORMATTED_RESULT)='491(10)376')
  and  (TD.SUPPL_IDNUM =40549 or TD.IDNUM=40549);

-1

外部联接抑制索引使用？

连接到 PostgreSQL 服务器：致命：主机没有 pg_hba.conf 条目

如何让sqlplus的输出出现在一行中？

选择具有最大日期或最晚日期的日期

如何列出 PostgreSQL 中的所有模式？

列出指定表的所有列

如何在不修改我自己的 tnsnames.ora 的情况下使用 sqlplus 连接到位于另一台主机上的 Oracle 数据库

你如何mysqldump特定的表？

使用 psql 列出数据库权限

如何从 PostgreSQL 中的选择查询中将值插入表中？

如何使用 psql 列出所有数据库和表？

外部联接抑制索引使用？

2 个回答

相关问题