Hints: Add test cases for table_stats, index_stats and column_status

[PhilippSalvisberg]

Jonathan Lewis added this comment on my blog post about regarding Finding Wrong Hints:

A little detail that’s not commonly known – there are some hints that require you to use the table name (even the schema.table) rather than the alias.

One I know of (thanks to an article by Roger MacNicol: http://orasql.org/2019/04/16/correct-syntax-for-the-table_stats-hint/ ) is the table_stats() hint. Two more, I assume, but haven’t tested in detail yet, would be the index_stats() and column_stats() hints.

It makes sense for these hints because if a table appears several times in a query you’d expect the optimizer to insist that the statistics on that table (and its indexes and columns) were consistent for the whole query.

(You can also see in v$sql_hints that they’re statement-level hints, so you couldn’t make them vary for different query blocks – and in fact the hints would be ignored if you included the @query_block part that can be used in so many other hints.)

The following hints are only part of the ALL_HINTS hash set:

• TABLE_STATS
• INDEX_STATS
• COLUMN_STATS

As a result they should be checked only for violations of G-9601: Never use unknown hints only.

The check regarding G-9602/G-9603 is done only for some chosen hints (via checkTabspec call):

• LEADING and alike
• IGNORE_ROW_ON_DUPKEY_INDEX and alike
• DYNAMIC_SAMPLING
• INDEX and alike
• MERGE and alike
• PARALLEL

However, it would make sense to define some test cases for TABLE_STATS, INDEX_STATS and COLUMN_STATS hints.