ContextQMD
Back to Tidb

View Hint Design Doc

docs/design/2022-11-22-view-hint.md

26.3.08.0 KB
Original Source

View Hint Design Doc

Background

Hints that specify a table generally refer to tables in the DELETE, SELECT, or UPDATE query block in which the hint occurs, not to tables inside any views referenced by the statement. So we introduce the view hint to specify the table in view instead of embedding the hint in the view.

In Oracle, there are three ways to use the global hint. (Node: the {} part is only used for explanation)

SQL
CREATE OR REPLACE VIEW v AS
SELECT {SEL$2} * from e1 join (select {SEL$3} * from e3) e2 on e1.a = e2.a;

SELECT {SEL$1} * FROM v;

A. SELECT /*+ INDEX(v.e2.e3 idx) */  * FROM v; // /*+ INDEX(@SEL$1 v.e2.e3 idx) */

B. SELECT /*+ INDEX(@SEL$2 e2.e3 idx) */ * FROM v;

C. SELECT /*+ INDEX(@SEL$3 e3 idx) */ * FROM v;

Compared with TiDB, Oracle has two differences:

  1. Oracle can use alias.table to represent in subquery, such as e2.e3. Besides, TiDB can use db.table to represent a table.
  2. The count for query block number treats view like a subquery, which means the select parts in view are counted.

Based on the difference, there are some reasons why TiDB can not just use the grammar from Oracle:

  1. Compatibility
    1. Grammar.
      1. We can not use the alias.table to represent in subquery, such as e2.e3.
      2. We can use db.table to represent a table. So if we want to use the view.table to represent a table in view, we should change the grammar or it will conflict with db.table.
    2. The count for the query block.
      1. Previously, the query block in view would not be counted. But now, if we take the view into consideration, it will change the origin count. For example, in the following part. The count of the query block for the select a from t1 will be changed from @SEL_2 to @SEL_3. So if we use the query block related hints for this part, it will be invalid or represent the content in the view.
SQL
CREATE OR REPLACE VIEW v AS
SELECT {SEL$2} * FROM t;

SELECT {SEL$1} * FROM v JOIN (select {SEL$3} a from t1) t2 on v.a = t2.a;

So based on the above reasons, we should introduce another way to let hint take effect in the view.

Detailed Design

How does origin hint framework work?

  1. Parser: parse the sql text and get the basic information about the hint. Handle hint syntax error in this phase.
  2. Optimizer:
    1. Divide and mark the query block. Besides, group the hints in the same query blocks.
    2. In the plan builder phase, when we try to build select. We will handle the hints in the current query block. Including doing some simple checks and building the hints structure which can be used by planner.
    3. When we build some logical operators, we will use the hints which belongs to the current query block. And tt will use the table in hint to match the table in the plan node. For example, when we build the DataSource operator, it will generate the possible access path based on the index hints. When we build the Aggregation operator, it will set the aggregation algorithm based on the agg hints. And for the Join operator, it will store the hint in the join node and use the hint information in the physical optimization phase. The warning about which table is not used in the hint will be recorded in this phase.
    4. Use the hint information in the physical optimization phase to determine which physical algorithm should be used. And if the hint can not take effect, it will report warning. For example, if the join can not use the index join, but we set the index join hint in the sql text. It will report related warnings.

View Hint Design

Based on the goal and current infrastructure for hint. I extend the current usage of the qb_name hint to a bigger scope to support the view hint.

An example to show the usage of the current qb_name hint.

SQL
select /*+ stream_agg(@qb) merge_join(t1@qb)*/ * from (select /*+ qb_name(qb) */ count(*) from t1 join t2 on t1.a = t2.a) tt;
  1. First, we define the name for some query blocks.
  2. Then we can use the query block name to represent the query block.

Based on the meaning of qb_name hint now, we can expand it to support the view. The basic idea is the same here. We define the query block name in the view first. And then we can use the query block name to represent the contents in the view. Now the grammar is expanded from qb_name(name) in the query block which you want to rename To qb_name(name, viewName@queryBlockNum . {viewName}@queryBlockNum . ...) in the first query block to represent any query block. Besides, we will reset the count for query block in every view. It means, for every view, it always counts from 1 and it will not effect the outer part. For example:

SQL
create table t(a int, b int);
create table t1(a int, b int);
create table t2(a int, b int);

create view v as select {@SEL_1}{5} t.a, t.b from t join (select {@SEL_2}{6} t1.a from t1 join t2 on t1.b=t2.b) tt on t.a = tt.a;

create view v1 as select {@SEL_1}{3} t.a, t.b from t join (select {@SEL_2}{4} from t1 join v on t1.b=v.b) tt on t.a = tt.a;

create view v2 as select {@SEL_1}{1} t.a, t.b from t join (select {@SEL_2}{2} t1.a from t1 join v1 join v3 on t1.b=v1.b) tt on t.a = tt.a;

select {@SEL_1} * from v2;

/* We can use the following part to represent the {1} - {6} */
1: qb_name(v2_sel1, v2@sel_1 . @sel_1)
2: qb_name(v2_sel2, v2@sel_1 . @sel_2)
3: qb_name(v1_sel1, v2@sel_1 . v1@sel_2 . @sel_1)
4: qb_name(v1_sel2, v2@sel_1 . v1@sel_2 . @sel_2)
5: qb_name(v_sel1, v2@sel_1 . v1@sel_2 . v@sel_2 . @sel_1)
6: qb_name(v_sel2, v2@sel_1 . v1@sel_2 . v@sel_2 . @sel_2)

Take the previous as example:

SQL
CREATE OR REPLACE VIEW v AS
SELECT * from e1 join (select count(*) from e3) e2 on e1.a = e2.a;


/* In Oracle */
A1. SELECT /*+ INDEX(v.e2.e3 idx) */  * FROM v;

A2. SELECT /*+ INDEX(@SEL$1 v.e2.e3 idx) */ * FROM v;

B. SELECT /*+ INDEX(@SEL$2 e2.e3 idx) */ * FROM v;

C. SELECT /*+ INDEX(@SEL$3 e3 idx) */ * FROM v;

/* In TiDB */
SELECT /*+ qb_name(viewSub, v@sel_1 . @sel_2) use_index(e3@viewSub, idx) hash_agg(viewSub) */ * FROM v;

Implementation

Parser part is easy to implement. Just to expand the origin qb_name hint grammar. The only problem maybe is how to express the nested view(use dot or blank or something else).

For the planner part:

  1. At the beginning of the optimization, we should handle the query block name hint for view and the other method hints for view. And group these hints based on the query block name.
  2. When we try to build the data source from the view, we have to traverse all of the query blocks for views. Check whether the view name in hint can match the data source or not. If there are some hints that can match, we pass it to the buildDataSourceFromView.
  3. When we try to build the view plan, we first handle the hints which are passed by the caller. Distinguish which hints belong to the current view and which belongs to the nested view. If the hint belongs to the current view, we transform the hint to the normal hint. If the hints belong to the nested view. Then we will do the same thing, like step2.

Besides the planner part, we need support to show the query block for a sql to increase usability. The user can copy the result and use it in hint directly.

Support Scope

  1. We can support almost all physical algorithm's hints. Like join hints/ agg hints/ index etc.
  2. Do not support the leading hints which may be across the view. But we can support the leading hint in the same view.

Pros and Cons

Pros:

  1. No compatibility problems. Just expand the usage of the existing hint.
  2. It is easier to implement. It can use the origin hints' infrastructure as much as possible.
  3. It can support almost all the hints which can take effect in the query block. Oracle can only support the join order, join method and access path hints.

Cons:

  1. It may not be easy to write the query block name hint for a view.
  2. The user should define the query block name hint first.

Reference

Oracle Global Hint