如何为Oracle方言添加optimizer hint

[tzhao11]

Oracle的optimizer hint语法如下：

SELECT /*+ LEADING(e2 e1) USE_NL(e1) INDEX(e1 emp_emp_id_pk) 
           USE_MERGE(j) FULL(j) */
    e1.first_name, e1.last_name, j.job_id, sum(e2.salary) total_sal
  FROM employees e1, employees e2, job_history j
  WHERE e1.employee_id = e2.manager_id
    AND e1.employee_id = j.employee_id
    AND e1.hire_date = j.start_date
  GROUP BY e1.first_name, e1.last_name, j.job_id
  ORDER BY total_sal;

需要在select和字段列表间添加一段特定格式的注释。目前我是通过重写OracleFormatter中的visitSelect方法实现的：

const val OPTIMIZER_HINTS_KEY = "optimizerHints"

class ExtendedOracleFormatter(database: Database, beautifySql: Boolean, indentSize: Int) :
    OracleFormatter(database, beautifySql, indentSize) {
    // ⚠️注意：ktorm版本更新时，一定要同步更新该方法
    override fun visitSelect(expr: SelectExpression): SelectExpression {
        val optimizerHints: List<String>? = (expr.extraProperties[OPTIMIZER_HINTS_KEY] as? List<*>)?.mapNotNull { it as? String }
        if (optimizerHints.isNullOrEmpty()) {
            return super.visitSelect(expr)
        }

        // 以下为super.visitSelect(expr)代码，在select关键字后插入了optimizer hint
        writeKeyword("select ")

        // 插入optimizer hint
        write("/*+ ${optimizerHints.joinToString(" ")} */ ")
        // 插入optimizer hint END

        if (expr.isDistinct) {
            writeKeyword("distinct ")
        }

        // 剩余的super.visitSelect(expr)代码，此处省略
        ...
    }
}

fun SelectExpression.withOptimizerHints(hints: List<String>): SelectExpression {
    return this.copy(
        extraProperties = extraProperties.toMutableMap().apply { put(OPTIMIZER_HINTS_KEY, hints) }
    )
}

fun Query.withOptimizerHints(vararg hints: String): Query = withOptimizerHints(hints.toList())
fun Query.withOptimizerHints(hints: List<String>): Query {
    return this.withExpression(
        when (val expression = this.expression) {
            is SelectExpression -> expression.withOptimizerHints(hints)
            is UnionExpression -> throw IllegalStateException("Optimizer hints are not supported in a union expression.")
        }
    )
}

fun <E : Any, T : BaseTable<E>> EntitySequence<E, T>.withOptimizerHints(vararg hints: String): EntitySequence<E, T> = withOptimizerHints(hints.toList())
fun <E : Any, T : BaseTable<E>> EntitySequence<E, T>.withOptimizerHints(hints: List<String>): EntitySequence<E, T> {
    return this.withExpression(expression.withOptimizerHints(hints))
}

optimizer hint保存在extraProperties中，用法如下：

database.exampleEntities.withOptimizerHints("index(example_table EXAMPLE_IDX1)", "hint2", "hint3")
    .filter { it.employee_id eq employee_id }
    .toList()

这样写虽然能实现功能，但是重写的visitSelect方法其实是复制了父类的代码后插入了自己的逻辑，如果ktorm版本更新后父类有变动，必须同步更新。请问有没有更好的方法可以实现这一功能并不受ktorm版本更新影响呢？

[vincentlauvlwj]

可以考虑写成嵌套查询

const val OPTIMIZER_HINTS_KEY: String = "optimizerHints"

class ExtendedOracleFormatter(
    database: Database, beautifySql: Boolean, indentSize: Int
) : OracleFormatter(database, beautifySql, indentSize) {

    @Suppress("UNCHECKED_CAST")
    override fun visitSelect(expr: SelectExpression): SelectExpression {
        val hints = expr.extraProperties[OPTIMIZER_HINTS_KEY] as? List<String>
        if (hints.isNullOrEmpty()) {
            return super.visitSelect(expr)
        }
        
        writeKeyword("select * ")
        write("/*+ ${hints.joinToString(" ")} */ ")
        newLine(Indentation.SAME)
        writeKeyword("from ")
        visitQuerySource(expr)
        newLine(Indentation.SAME)
        return expr
    }
}

[tzhao11]

感谢回答！我这边试了一下，hint必须加在原始查询里，嵌套查询会造成hint失效。

hint加在原始查询里：

select /*+ index(TEST1 TEST1_IDX14) */ *
from TEST1

生成的执行计划：

--------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |    36M|    10G|  8955K  (1)|
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST1       |    36M|    10G|  8955K  (1)|
|   2 |   INDEX FULL SCAN           | TEST1_IDX14 |    36M|       |   191K  (1)|
--------------------------------------------------------------------------------

使用嵌套查询：

select /*+ index(TEST1 TEST1_IDX14) */ *
from (select * from TEST1)

生成的执行计划：

----------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)|
----------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |    36M|    10G|   462K  (2)|
|   1 |  TABLE ACCESS FULL| TEST1 |    36M|    10G|   462K  (2)|
----------------------------------------------------------------

[vincentlauvlwj]

那就只能把 visitSelect 里面的代码复制一遍了，这段代码比较稳定，改动概率低，问题不大

[tzhao11]

好的，我们暂时先采用这个方案了，谢谢解答