如何优化 Oracle 查询？

Question

我收到一个 SQL 查询，说我必须优化这个查询。

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

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

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

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

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

有问题的查询：

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 列上放置单列索引。

Answer 2

重构查询后出现索引，因此遵循 @eric 的帖子：

credit_request：
您正在将其加入 request_id 上的 requestor，我希望它是唯一的。然后，在 where 子句中，您可以在 evaluator_id 上设置条件，并在查询中选择 client_app_id 和 personal_flg。因此，您可能需要在 credit_request 的 (request_id, evaulator_id, client_app_id, individual_flg) 上建立一个唯一索引。

通过将您选择的列放入索引中，您可以避免 < 已在索引中，则无需。

code>by index rowid，这意味着您已从索引中选择了值，然后重新输入表以获取更多信息，如果此信息 将其加入到 evaluator_id 上的 evaluator 上，该 ID 包含在第一个索引中

requestor：
。
这是在 request_id 上加入的，您的 where 子句包括 soc_sec_num_text、lower(first_name_txt)、lower(last_name_txt) 和birth_dt。因此，如果可能的话，您需要在 (request_id, soc_sec_num_text) 上建立一个唯一的索引，因为这会更加复杂，因为您实际上应该在尽可能多的条件上建立索引。您还选择了requestor_type_iud。

在这种情况下，为了避免使用具有许多列的功能索引，如果您有空间、时间和意愿，我会在 (request_id, soc_sec_num_text,birth_dt ) 上建立索引，然后添加 lower(first_name_txt) ...等等这可能会提高速度，具体取决于列的选择性。这意味着，如果 first_name_txt 中的值远多于 birth_dt，那么最好将其放在 birth_dt 的前面索引，这样如果它是非唯一索引，您的查询就需要扫描更少的内容。

您注意到我没有将选定的列添加到该索引中，因为您已经必须进入表中，因此添加它不会获得任何好处。

评估者：
这仅在 evaluator_id 上加入，因此您需要在此列上有一个唯一的索引（如果可能）。