@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.
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.
@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):
CARDINALITY. Use the result ofSELECT NAME FROM v$sql_hint ORDER BY name;for the list of hints to include.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.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.