Add a new-style hint to control Rowid Filters.

/* +ROWID_FILTER( [table_name [index_name [ ...] ]]) */

/* +NO_ROWID_FILTER([table_name [index_name [ ... ] ]] ) */

Hint applicability

Like NO_RANGE_OPTIMIZATION or MRR, the hint can apply to

• Query block - NO_ROWID_FILTER()
• Table - NO_ROWID_FILTER(table_name)
• Specific index(es) - NO_ROWID_FILTER(table_name index1 index2 ...

Semantics

Semantics of NO_ROWID_FILTER

Do not consider Rowid Filter for the scope of the hint (all tables in the query block, specific table, specific index(es)).

Semantics of ROWID_FILTER

This should do something like "force use of ROWID_FILTER for the table.index it targets" but there are some issues:

Possible semantics #1

At query block or table level it makes sense to have the meaning just "enable use of ROWID_FILTER". Assuming it is disabled globally.

Possible semantics #2

At index level, it would be nice to force use of indexes regardless of the costs.
That is,

SELECT /*+ ROWID_FILTER(t1 idx1) */ 

would force the use of Rowid Filter made from t1.idx1 if the picked plan allows it, regardless of the costs.

A problem?

Suppose we have implemented the logic:

ROWID_FILTER(t1 idx1) makes the optimizer to use RowidFilter(idx1) if the access method to t1 allows it.

Then we run some query Q without any hints.
Suppose the optimizer's choice is to use index idx2 for accessing table t1.
Then we add the ROWID_FILTER(t1 idx1) to the Q and run it.
When the optimizer looks for access method to t1, it picks idx2 and adds rowid filter(t1) .
This makes the const higher than just using idx2.
Use of idx2+RowidFilter(idx1) may become more expensive than e.g. full table scan.
Or the join order may change.

That is, the effect of the ROWID FILTER(t1 idx1) hint may be that some totally different plan will be used.

...

TODO:

• When creating potential Rowid filters, only consider the specified indexes.

• the ROWID_FILTER hint doesn't affect the choice of access method.
  If the optimizer picks an index for which this rowid filter can be applied, good. If not, not our problem.

• When considering whether to use the rowid filter together with an index,
  make a cost-based choice between specified filters.

There are some ideas about specifying the buffer size available for the rowid filter but they are outside of the scope of this MDEV.