sql - 如何着手优化 Oracle 查询？

Question

有人给我一个 SQL 查询，说我必须优化这个查询。

我遇到了解释计划。因此，在 SQL Developer 中，我运行了 explain plan for ,

它将查询分成不同的部分并显示每个部分的成本。

我该如何着手优化查询？我在寻找什么？成本高的元素？

我对 DB 有点陌生，所以如果您需要更多信息，请问我，我会尽力获取。

我试图了解这个过程，而不是仅仅发布查询本身并获得答案。

有问题的查询:

SELECT cr.client_app_id,
  cr.personal_flg,
  r.requestor_type_id 
FROM credit_request cr,
  requestor r,
  evaluator e 
WHERE cr.evaluator_id = 96 AND
  cr.request_id = r.request_id AND
  cr.evaluator_id = e.evaluator_id AND
  cr.request_id != 143462 AND
  ((r.soc_sec_num_txt = 'xxxxxxxxx' AND         
  r.soc_sec_num_txt IS NOT NULL) OR
  (lower(r.first_name_txt) = 'test' AND
  lower(r.last_name_txt) = 'newprogram' AND
  to_char(r.birth_dt, 'MM/DD/YYYY') = '01/02/1960' AND
  r.last_name_txt IS NOT NULL AND
  r.first_name_txt IS NOT NULL AND
  r.birth_dt IS NOT NULL))

在运行解释计划时，我正在尝试上传屏幕截图。

OPERATION    OBJECT_NAME     OPTIONS     COST 
 SELECT STATEMENT                        15 
 NESTED LOOPS              
 NESTED LOOPS                            15 
 HASH JOIN                               12 
 Access Predicates 
 CR.EVALUATOR_ID=E.EVALUATOR_ID 
 INDEX  EVALUATOR_PK     UNIQUE SCAN     0 
 Access Predicates 
 E.EVALUATOR_ID=96 
 TABLE ACCESS  CREDIT_REQUEST    BY INDEX ROWID      11 
 INDEX  CRDRQ_DONE_EVAL_TASK_REQ_NDX     SKIP SCAN   10 
 Access Predicates 
 CR.EVALUATOR_ID=96 
 Filter Predicates 
 AND 
 CR.EVALUATOR_ID=96 
 CR.REQUEST_ID<>143462 
 INDEX  REQUESTOR_PK     RANGE SCAN      1 
 Access Predicates 
 CR.REQUEST_ID=R.REQUEST_ID 
 Filter Predicates 
 R.REQUEST_ID<>143462 
 TABLE ACCESS  REQUESTOR     BY INDEX ROWID      3 
 Filter Predicates 
 OR 
 R.SOC_SEC_NUM_TXT='XXXXXXXX' 
 AND 
 R.BIRTH_DT IS NOT NULL 
 R.LAST_NAME_TXT IS NOT NULL 
 R.FIRST_NAME_TXT IS NOT NULL 
 LOWER(R.FIRST_NAME_TXT)='test' 
 LOWER(R.LAST_NAME_TXT)='newprogram' 
 TO_CHAR(INTERNAL_FUNCTION(R.BIRTH_DT),'MM/DD/YYYY')='01/02/1960' 

Answer 1

作为对查询的快速更新，您需要将其重构为如下形式:

SELECT
    cr.client_app_id,
    cr.personal_flg,
    r.requestor_type_id 
FROM 
    credit_request cr
    inner join requestor r on
        cr.request_id = r.request_id
    inner join evaluator e on
        cr.evaluator_id = e.evaluator_id
WHERE 
    cr.evaluator_id = 96
    and cr.request_id != 143462
    and (r.soc_sec_num_txt = 'xxxxxxxxx' 
        or (
            lower(r.first_name_txt) = 'test'
            and lower(r.last_name_txt) = 'newprogram'
            and r.birth_dt = date '1960-01-02'
        )
    )

首先，用逗号连接会产生交叉连接，这是您要避免的。幸运的是，Oracle 足够聪明，可以将其作为内部联接来执行，因为您指定了联接条件，但您希望明确说明，以免意外遗漏某些内容。

其次，您的 is not null 检查毫无意义——如果列为 null，并且 = 检查将返回 false那一行。事实上，任何与 null 列的比较，甚至 null = null 都会返回 false。您可以尝试使用 select 1 where null = null 和 select 1 where null is null。只有第二个返回。

第三，Oracle 足够智能，可以将日期与 ISO 格式进行比较(至少我上次使用它时是这样)。您可以只执行 r.birth_dt = date '1960-01-02' 并避免在该列上执行字符串格式。

话虽这么说，但就严重的性能错误而言，您的查询并没有写得很差。您要查找的是索引。 evaluator 是否在 evaluator_id 上有一个？ credit_request 吗？它们是什么类型？通常，evaluator 将在 PK evaluator_id 上有一个，而 credit_request 也将有一个用于该列。 requestor 和 request_id 列也是如此。

您可能要考虑的其他索引是您用于过滤的所有字段。在本例中，soc_sec_num_txt、first_name_txt、last_name_txt、birth_dt。考虑在后三列上放置一个多列索引，在 soc_sec_num_txt 列上放置一个单列索引。