New custom validator to check hints #31
Description
@ChrisAntognini recommended to check the validity of hints via Twitter.
I think this is a good idea and could be implemented with a new custom validator. Similar as the custom validator for SQLInjection this validator should work standalone and should be included in TrivadisGuidelines3Plus.
The following rules should be implemented as part of this issue (initial set of guidelines checks regarding hints):
| Guideline | Message | Notes |
|---|---|---|
| G-9601 | Never use unknown hints. | Check if the hint name is known (without checking parameters and their correctness). throw this warning when the hint is not known. Consider also undocumented hints like CARDINALITY. Use the result of SELECT NAME FROM v$sql_hint ORDER BY name; for the list of hints to include. |
| G-9602 | Always use the alias name instead of the table name. | Check if the referenced table(s) have an alias. If yes, throw a warning. Documented hints to consider are: CACHE, CHANGE_DUPKEY_ERROR_INDEX, CLUSTER, DRIVING_SITE, DYNAMIC_SAMPLING, FACT, FULL, HASH, INDEX, INDEX_ASC, INDEX_COMBINE, INDEX_DESC, INDEX_FFS, INDEX_JOIN, INDEX_SS , INDEX_SS_ASC , INDEX_SS_DESC, INMEMORY, INMEMORY_PRUNING, LEADING, MERGE, NOCACHE, NO_FACT, NO_INDEX, NO_INDEX_FFS, NO_INDEX_SS, NO_INMEMORY, NO_INMEMORY_PRUNING, NO_MERGE, NO_PARALLEL, NO_PARALLEL_INDEX, NO_PQ_SKEW, NO_PUSH_PRED, NO_USE_BAND, NO_USE_CUBE, NO_USE_HASH, NO_USE_MERGE, NO_USE_NL, NO_ZONEMAP, PARALLEL, PARALLEL_INDEX, PQ_DISTRIBUTE, PQ_SKEW, PUSH_PRED, USE_BAND, USE_CUBE, USE_HASH, USE_MERGE, USE_NL, USE_NL_WITH_INDEX. These hints can be grouped by similar syntax to keep the code base small. |
| G-9603 | Never reference an unknown table/alias. | Check the same hints as for G-9602. Throw a warning when then referenced table is neither a table name nor an alias. |
Consider the grammar according the SQL Reference manual for Oracle Database 19c.
The grammar for undocumented hints is not defined. Therefore we do not consider these hints to check G-9602 and G-9603. For chosen hints such a check may be added in the future.
Additional checks are possible. However, for those checks dedicated issues have to be created.