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

基本上，您是因为设计不当而感到困惑，欢迎来到我的世界=）

无论如何，当我查看它时，您只有两个字段可用于将事务联接回其引用的事务：STORE和DATE（尽管后者相当“粗糙”）。由于他们决定只存储交易的最后7位数字，因此加快此速度的唯一方法是添加一个存储这7位数字的新字段。但是，如果您走这条路，将整个REF语法简单地存储在一个新的（计算的）字段中会更有意义。

无疑，这将是解决该问题的最轻松的方法，因为您可以在该字段上添加索引，然后将查询更改为

SELECT *
  FROM transactions t1
  JOIN transactions t2
    ON t2.TRAN_AS_REF = t1.REF

但是，据我了解，您将无法/不允许在表中添加一个额外的（计算出的）字段？添加另一个包含此信息的表以使您可以使用它来链接信息也是一种解决方案，尽管这样做会增加一些复杂性以确保数据始终是最新的！但是，从我的理解来看，这不是这里的选择。另一个解决方法可能是创建一个视图，以重新调整TRAN及其等效于REF的视图。您可以实现所说的观点，并将其用作联接中的链接。就像计算出的现场方法一样，这将具有始终保持最新状态的好处，而无需额外的逻辑和/或对已经存在的逻辑进行更改。

或者最后，根据Wernfrieds的建议，也许可以创建将TRAN作为REF语法编制索引的索引？我对此没有经验，但是听起来这将是一个选择。

然后，索引将类似于

CREATE INDEX ind_ref ON transactions ( STORE + '*' + to_char(DATE, 'yy') + '*' + substr(to_char(TRAN), -7)) )

而您的查询将变成这样：

SELECT *
FROM transactions t1
    JOIN transactions t2
    ON ( (t2.STORE + '*' + to_char(t2.DATE, 'yy') + '*' + substr(to_char(t2.TRAN), -7)) = t1.REF )

并希望服务器随后可以选择要在正确的t2记录之后执行的索引。但是就像我说的那样，我没有经验，但是值得一试。

无论如何，如果所有这些都是一个nono，并且您只能优化查询，那么我建议通过充分利用STORE和DATE信息来充分利用它，例如：

 SELECT *
  FROM transactions t1
  JOIN transactions t2
    ON (    
            t2.store = substr(t1.REF, 1, instr(t1.REF, '*') - 1)
        AND t2.DATE BETWEEN to_date('0101' + substr(t1.REF, instr(t1.REF, '*', 1, 1) + 1), instr(t1.REF, '*', 1, 2) - 1)
                        AND to_date('3112' + substr(t1.REF, instr(t1.REF, '*', 1, 1) + 1), instr(t1.REF, '*', 1, 2) - 1)
        AND t2.TRAN % 10000000 = to_number(substr(t1.REF, instr(t1.REF, '*', 1, 2) + 1))
       )

考虑一下，如果您能够添加此索引

CREATE INDEX ind_tst ON transactions (STORE, DATE, TRAN % 10000000)

那么很可能上面的查询已经对您现有的内容进行了很大的改进。大声思考这意味着您也可以尝试以下操作：

CREATE INDEX ind_tst ON transactions (STORE, to_char(DATE, 'yy'), TRAN % 10000000)

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 t2.TRAN % 10000000 = to_number(substr(t1.REF, instr(t1.REF, '*', 1, 2) + 1))
       )

希望这会有所帮助。由于我没有使用Oracle的经验，您可能需要在此处和此处修复语法，对此感到抱歉...总之，祝您好运！