Anais SQL tuning assistant provides comments and suggestions to improve SQL query performances.
Score #
The given score is the estimated number of rows that could be avoided if Anais suggestion is applied.
The higher its value, the more effect the associated remark or suggestion should have on performance.
For more accuracy, the number of executions of the query is taken into account:
total score = unit score * number of executions
The greatest score is highlighted, to focus on high value-added changes.
Index suggestion #
An index can be suggested when the cardinality of the operation is low or decreases from previous operation in execution plan.
This low cardinality reflects the fact that selective predicates are involved in the operation.
Based on operation predicates, involved columns can be suggested to be part of the index, as they seem to be selective enough.
Here are the different kinds of columns that can be mentioned:
Interesting columns #
are columns with good or high selectivity.
If involved in an equality, they should be part of the suggested index.
If involved in an inequality, the resulting cardinality must first be checked before deciding if a column can be included in the index.
Example:
“WHERE ID>1000“
can lead to million rows, whereas ID is highly selective (even unique).
Low selectivity columns #
are columns with low distinct keys, but since they are part of selective predicates, they might be interesting.
Example:
“WHERE STATUS=0“
can match very few rows in the table, even if STATUS can equals 0 or 1 (only 2 distinct keys).
If value 1 is not selective while value 0 is, then even low selectivity columns must be checked with their complete predicate.
Excluded columns #
are mentioned because they can have a good or high selectivity, but an operation avoids the column to be used in an index.
Maybe the predicate can be rewritten to allow potential index usage if relevant. This is why these columns are displayed anyway.
Example:
“WHERE STATUS<>1“
can be rewritten to
“WHERE STATUS=0“
in order to retrieve very few rows with an index.
Function applied #
In addition, columns in all these categories (“Interesting”, “Low selectivity” or “Excluded” columns) can be affected by a function.
A note can also mention: “Column potentially subject to data type implicit conversion”.
Check if the function is mandatory or if a function-based index could be used, if predicate can’t be rewritten.
Example:
“WHERE TO_CHAR(INDEXED_DATE)=:1“
should probably be rewritten to
“WHERE INDEXED_DATE=TO_DATE(:1)“,
allowing index usage.
