sql - 如何优化 Oracle SQL 中列名作为函数参数出现的查询？

Question

我的任务是优化 Oracle SQL 中的查询，其中使用有关其列之一中已解析的 varchar 数据片段的条件将表与其自身连接。据我了解，Oracle 不会使用索引，因为 ON 子句中的列名仅作为函数的参数出现。并且查询几乎需要永远完成。创建一个包含已处理 REF 数据的表(见下文)可以解决该问题，但由于其他原因这是不可能的。

我已经准备了问题的简化版本以供说明(我相当确定这是线索，因此我提取了更复杂查询的相关部分)。 “交易”表包含以下列:

• TRAN——一个 10 位数字，作为交易代码，
• STORE - 进行交易的商店的代码，
• DATE -- 交易日期，

• REF——不同交易的引用代码(在返回等情况下)。此代码的格式为:[商店代码] * [交易年份的最后两位数字] * [TRAN 的最后 7 位数字，不带左侧零]，因此它可能如下所示:“142*09*3234”。基本上，REF 指向表中的其他行，但在使用之前必须进行一些处理。

  SELECT *
  FROM transactions t1
      JOIN transactions t2
      ON ( t2.store = substr(t1.REF, 1, instr(t1.REF, '*') - 1)
          AND to_char(t2.DATE, 'yy') = substr(t1.REF, instr(t1.REF, '*', 1, 1) + 1), instr(t1.REF, '*', 1, 2) - 1)
          AND to_number(substr(to_char(t2.TRAN), -7)) = to_number(substr(t1.REF, instr(t1.REF, '*', 1, 2) + 1))
         )
  

我没有处理 SQL 优化的经验，所以我很感激任何好的建议。

Answer 1

您可以在 Oracle 中创建“基于函数的索引”。试试这个:

CREATE INDEX ind_1 ON transactions (SUBSTR(REF, 1, INSTR(REF, '*') - 1));
CREATE INDEX ind_2 ON transactions (SUBSTR(REF, INSTR(REF, '*', 1, 1) + 1), INSTR(REF, '*', 1, 2) - 1));
CREATE INDEX ind_3 ON transactions (TO_NUMBER(SUBSTR(TO_CHAR(TRAN), -7)));
CREATE INDEX ind_4 ON transactions (TO_NUMBER(SUBSTR(REF, INSTR(REF, '*', 1, 2) + 1)));
CREATE INDEX ind_5 ON transactions (TO_CHAR(DATE, 'yy'));

但是，您应该检查解释计划并删除那些未使用的索引。您还可以创建虚拟列并在那里创建索引。