New-style optimizer hints: milestone 3.5

Idea
Every view, CTE and derived table participating in a query gets an implicit query block name based on its name or alias. Optimizer hints can be applied to internal objects of view/derived tables/CTE by referencing this implicit query block name.

Benefits
Objects inside derived tables and views can be referenced by optimizer hints without explicit addition of the `QB_NAME()` hint

Example

select * from (select * from t1 where a > 10) as DT;   -- `DT` gets an implicit query block name `dt`

-- referencing a table inside a DT by implicit QB name:

select /*+ no_index(t1@dt) */ * from (select * from t1 where a > 10) as DT; 

-- this is an equivalent to:

select /*+ no_index(t1@dt) */ * from (select /*+ qb_name(dt)*/ * from t1 where a > 10) as DT; 

create view v1 as select * from t1 where a > 10 and b > 100;

select * from v1 as vvv, t2 where vvv.a = t2.a;  -- view `vvv` gets an implicit query block name `vvv`

-- referencing a table inside a view by implicit QB name:

select /*+ index_merge(t1@vvv idx_a, idx_b) */ * from v1 as vvv, t2 where vvv.a = t2.a;

with bless100 as (select a from t1 where b <100)

  select * from bless100;    -- The CTE gets an implicit query block name 'bless100'

-- referencing a table inside a CTE by implicit QB name:

with bless100 as (select a from t1 where b <100)

  select /*+ index(t1@bless100) */ * from bless100;

Limitations

1. If there is UNION/EXCEPT/INTERSECT inside the implicit QB, it's unclear to which select of the unit a hint should be applied. Such hints are not currenyly supported, and the warning is generated.
2. If there is an intersection with explicit QB names, the implicit name is not assigned, for example:

   select /*+ index(t2@DT) */ * from t1,

   (select a from t2 where a < 10) DT,

   (select /*+ qb_name(DT)*/ a from t2 where a > 100) as DT_big ...

   the hint index(t2@DT) is applied to `t2` from `DT_big` despite there is another derived table named `DT`.

3. If a view, derived table or CTE is referenced more than once with the same alias, the hint cannot be applied. For example,

   select /*+ index(t2@v1) */ * from v1,

   (select a from v1 where b >5) DT ...

TiDB-inspired syntax suggestion

Lexical Select Numbering

Let's define "Lexical Select Numbering" as follows:

• Every VIEW has its own local numbering.
• Every CTE definition has its own local numbering.
• Other than that, SELECTs are numbered "in the order their SELECT keyword occurs in the query text".

This numeration is similar to select_lex->select_number numeration for basic cases. In complex cases it is apparently different.
It is close to TiDB's SEL_n numbering. The only difference I'm aware of is handling of CTEs.

Locator path

Now, let's describe a way how to address any select in the query.

We can't address selects inside VIEWs or CTEs by their either implicit or explicit qb_name.

We need to assign them a name using "locator QB_NAME" hint, first.
Locator QB_NAME hint looks similar to regular QB_NAME(new_name) hint but it has multiple arguments.

It specifies a "path" to a select and assigns that select a name:

  QB_NAME(new_name , path)

The path consists of components:

  ViewCteName1@QbLexNumber1 [ '.' ViewCteName2@QbLexNumber2 ...]  '.'  @QbLexNumberN

All QbLexNumberN must be in SEL_n form.

We interpret the path as follows:

Start with the root of the query.

Locate the SELECT within the query that has Lexical Select Number QbLexNumber1.

Within that SELECT, locate the TABLE_LIST $TL with ViewCteName1.

(If you are doing this after the derived or semi-join merge has been done, be sure to look at the tables that were originally in that SELECT! There is TABLE_LIST::next_local chain, check if you can use that. Maybe, not)

$TL must be a view or a CTE reference.
Enter that CTE or VIEW.
Repeat the above process with QbLexNumber2: locate a SELECT with that Lexical Select Number.
In the SELECT, locate the TABLE_LIST with ViewCteName2.

Eventually, you reach the last "@QbLexNumberN" component of the path.
Again, locate the SELECT with this Lexical Select Number.

This is the SELECT that we need to assign name new_name to.
After the select is assigned a name, one can use other hints to control things in that select.

Abbreviated path step

A path step can be just .foo, that is, have this form

 ViewCteName

This is equivalent to

 ViewCteName @ SEL_1