如何优化Oracle查询？

Question

我收到了一条SQL查询，要求我对其进行优化。

我发现了explain plan。因此，在SQL开发人员中，我运行了解释计划。

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

我该如何优化查询？我应该寻找哪些元素？成本高的元素吗？

我对数据库有点新，如果您需要更多信息，请告诉我，我会尽力获取它。

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

相关查询：

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' 

- roymustang86

---

我们可以带领您完成这个过程，但我们需要看到查询。每个查询都是不同的，但通常情况下，您要尽可能避免表/聚集索引扫描。阅读查询计划更多地是一种艺术而非科学 :) - Eric

我已经更新了问题并附上了查询。 - roymustang86

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足够聪明，会将其作为内部连接处理，但您需要明确指出，以免意外遗漏某些内容。

其次，您的“不是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列上放置单列索引。

- Eric

---

你可以直接使用r.birth_dt = '1960-01-02'。这将默认将字符串转换为日期，基于NLS_DATA_FORMAT设置的值。因此，它是否有效取决于会话之间可能不同的设置。你也可以使用date关键字来创建日期常量，例如 r.birth_dt = date '1960-01-02' 或使用显式格式的to_date函数，例如 r.birth_dt = to_date('1960-01-02', 'YYYY-MM-DD')。 - Shannon Severance

@ShannonSeverance - 谢谢！我已经有一段时间没有在Oracle上工作了。感谢您的纠正。我已经编辑了帖子。今天我学到了新东西 :) - Eric

通常，评估器将在PK评估器ID上进行聚集，而信用请求将具有非聚集索引。您是在谈论聚集索引吗？如果是这样，那是SQL Server术语。Oracle聚集表完全不同，并且不是默认设置，在我的经验中也不是典型的。 （编辑以包括所需的否定以正确） - Shannon Severance

@ShannonSeverance - 再次感谢 - 已经有2-3年没在Oracle上工作了。最近一直在做SQL Server。 - Eric

Answer 2

在重构查询后，索引变得更加重要，接下来是@eric的帖子:

credit_request:
你正在将其与requestor连接，连接条件为request_id，我希望这是唯一的。在where子句中，您还具有evaluator_id条件，并在查询中选择client_app_id和personal_flg。因此，您可能需要一个独特的索引，在credit_request上使用(request_id, evaulator_id, client_app_id, personal_flg)。

通过将所选列放入索引中，您可以避免by index rowid，这意味着您已从索引中选择了值，然后重新输入表以获取更多信息。如果此信息已经在索引中，则没有必要进行此操作。

你正在将它连接到evaluator，连接条件为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)，因为or更加复杂，所以您应该尽可能地在多个条件上建立索引。您还选择了requestor_type_iud。

在这种情况下，为避免使用许多列的函数索引，如果有空间、时间和倾向，则可以在(request_id, soc_sec_num_text, birth_dt )上建立索引，然后将lower(first_name_txt)... etc添加到其中，这样可以根据列的选择性来改善速度。这意味着如果例如，first_name_txt中的值比birth_dt中的值多得多，那么您最好将其放在索引的前面，这样如果它是非唯一索引，您的查询就不需要扫描太多内容。

请注意，我没有将所选列添加到此索引中，因为您已经必须进入表中，所以添加它没有任何好处。

evaluator:
这只是基于evaluator_id连接，因此您需要一个唯一的（如果可能）索引在此列上。

- Ben