在 DML 语句中，每一个 Query Block 都会有一个 QB_NAME（query block name），可以用户指定，也可以系统自动生成。在用户没有用 Hint 指定的 QB_NAME 的时候，系统会按照 SEL$1、SEL$2、UPD$1、DEL$1 方式从左到右（实际也是 resolver 解析顺序）依次生成。
有了 QB_NAME，可以精确的定位每一个 table，也可以在一处地方指定任意 Query Block 的行为。在 TBL_NAME 中的 QB_NAME 用于定位 table，在 hint 中最前面的 qb_name 用于定位 hint 作用于哪一个 Query Block。
Query Block 含义。
Query Block 中的 Query Block 是指一个语义上完整的查询语句。
简单来看就是把 SQL 按 select、delete 这些词提取出来并结构化之后，按从左到右的方式标号。比如以下 SQL。
select * from view1 inner join view2其中 view1 定义为：
select * from t1,(select * from t2)view2 定义为：
select * from t1每个 Query Block 含义如下。
SEL$1：最外层 select * from view1 inner join view2 SEL$2：view1 select * from t1,(select * from t2) SEL$3：view1中 select * from t2 SEL$4：view2 select * from t1验证总共 4 个 query block。
obclient> select /*+NO_MERGE(@"SEL$1")*/ count(*) from v1 inner join v2 on v1.T1C1=v2.C1; +----------+ | COUNT(*) | +----------+ | 18 | +----------+ 1 row in set (0.05 sec) obclient> select /*+NO_MERGE(@"SEL$2")*/ count(*) from v1 inner join v2 on v1.T1C1=v2.C1; +----------+ | COUNT(*) | +----------+ | 18 | +----------+ 1 row in set (0.05 sec) obclient> select /*+NO_MERGE(@"SEL$3")*/ count(*) from v1 inner join v2 on v1.T1C1=v2.C1; +----------+ | COUNT(*) | +----------+ | 18 | +----------+ 1 row in set (0.05 sec) obclient> select /*+NO_MERGE(@"SEL$4")*/ count(*) from v1 inner join v2 on v1.T1C1=v2.C1; +----------+ | COUNT(*) | +----------+ | 18 | +----------+ 1 row in set (0.05 sec) obclient> select /*+NO_MERGE(@"SEL$5")*/ count(*) from v1 inner join v2 on v1.T1C1=v2.C1; ORA-00600: internal error code, arguments: -4018, Entry not exist通过执行计划查看 Query Block。
按照默认规则，会为 SEL$1 中的 t 选择 t_c1 路径，为 SEL$2 中的 t 选择 PRIMARY（主表）访问。
可以通过 Outline Data 来查看具体的 Query Block。
explain extended select * from t , (select * from t where c2 = 1) ta where t.c1 = 1\G *************************** 1. row *************************** Query Plan: ============================================================ |ID|OPERATOR |NAME |EST. ROWS|COST| ------------------------------------------------------------ |0 |NESTED-LOOP INNER JOIN CARTESIAN| |1 |1895| |1 | TABLE SCAN |t(t_c1)|1 |472 | |2 | TABLE SCAN |t |1 |1397| ============================================================ Used Hint: ------------------------------------- /*+ */ Outline Data: ------------------------------------- /*+ BEGIN_OUTLINE_DATA USE_NL(@"SEL$1" "test.t"@"SEL$2") LEADING(@"SEL$1" "test.t"@"SEL$1" "test.t"@"SEL$2") INDEX(@"SEL$1" "test.t"@"SEL$1" "t_c1") FULL(@"SEL$2" "test.t"@"SEL$2") END_OUTLINE_DATA */可以更改默认 Query Block 的范围来控制执行计划扫描路径。
上面的 SQL 通过 HINT 来更改 Query Block 指定 SEL$1 的 t 走主表，SEL$2 的走索引，颠倒一下。
注意：这里因为改写后，SEL$2 被提升到 SEL$1 所以这里不用指定 HINT 作用的 query block。
explain select/*+INDEX(t@SEL$1 PRIMARY) INDEX(t@SEL$2 t_c1)*/ * from t , (select * from t where c2 = 1) ta where t.c1 = 1\G *************************** 1. row *************************** Query Plan: ============================================================= |ID|OPERATOR |NAME |EST. ROWS|COST | ------------------------------------------------------------- |0 |NESTED-LOOP INNER JOIN CARTESIAN| |1 |16166| |1 | TABLE SCAN |t |1 |1397 | |2 | TABLE SCAN |t(t_c1)|1 |14743| ============================================================= Outputs & filters: ------------------------------------- 0 - output([t.c1], [t.c2], [t.c1], [t.c2]), filter(nil), conds(nil), nl_params_(nil) 1 - output([t.c1], [t.c2]), filter([t.c1 = 1]), access([t.c1], [t.c2]), partitions(p0) 2 - output([t.c2], [t.c1]), filter([t.c2 = 1]), access([t.c2], [t.c1]), partitions(p0)上面的 SQL 也可以写成如下方式。
select/*+INDEX(t@SEL$1 PRIMARY) INDEX(@SEL$2 t@SEL$2 t_c1)*/ * from t , (select * from t where c2 = 1) ta where t.c1 = 1\G <==> select/*+INDEX(t@SEL$1 PRIMARY)*/ * from t , (select/*+index(t@SEL$2 t_c1)*/ * from t where c2 = 1) ta where t.c1 = 1\G <==> select/*+INDEX(@SEL$1 t@SEL$1 PRIMARY) INDEX(@SEL$2 t@SEL$2 t_c1)*/ * from t , (select * from t where c2 = 1) ta where t.c1 = 1\G自定义 Query Block 示例。
select /*+ full(@sub_t2 t2)*/ * from t1 where id in (select /*+ qb_name(sub_t2) */ id from t2 where id = 2);确认是否生效查看执行计划 HINT 部分。
obclient [db_mysql]> explain extended select /*+ full(@sub_t2 t2)*/ * from t1 where id in (select /*+ qb_name(sub_t2) */ id from t2 where id = 2); +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Query Plan | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | =================================================== |ID|OPERATOR |NAME|EST. ROWS|COST| --------------------------------------------------- |0 |NESTED-LOOP JOIN CARTESIAN| |1 |93 | |1 | TABLE GET |t1 |1 |46 | |2 | TABLE GET |t2 |1 |46 | =================================================== Outputs & filters: ------------------------------------- 0 - output([t1.id(0x7fe7fe5f66e0)], [t1.log_date(0x7fe7fe5f6a90)]), filter(nil), conds(nil), nl_params_(nil), batch_join=false 1 - output([t1.id(0x7fe7fe5f66e0)], [t1.log_date(0x7fe7fe5f6a90)]), filter(nil), access([t1.id(0x7fe7fe5f66e0)], [t1.log_date(0x7fe7fe5f6a90)]), partitions(p0), is_index_back=false, range_key([t1.id(0x7fe7fe5f66e0)]), range[2 ; 2], range_cond([t1.id(0x7fe7fe5f66e0) = 2(0x7fe7fe6c4840)]) 2 - output([remove_const(1)(0x7fe7fe603490)]), filter(nil), access([t2.id(0x7fe7fe5f1e30)]), partitions(p0), is_index_back=false, range_key([t2.id(0x7fe7fe5f1e30)]), range[2 ; 2], range_cond([t2.id(0x7fe7fe5f1e30) = 2(0x7fe7fe5f1710)]) Used Hint: ------------------------------------- /*+ FULL(@"SEL$1" "db_mysql.t2"@"SEL$1") QB_NAME(sub_t2) */ Outline Data: ------------------------------------- /*+ BEGIN_OUTLINE_DATA LEADING(@"SEL$1" ("db_mysql.t1"@"SEL$1" "db_mysql.t2"@"SEL$1" )) USE_NL(@"SEL$1" ("db_mysql.t2"@"SEL$1" )) PQ_DISTRIBUTE(@"SEL$1" ("db_mysql.t2"@"SEL$1" ) LOCAL LOCAL) NO_USE_NL_MATERIALIZATION(@"SEL$1" ("db_mysql.t2"@"SEL$1" )) FULL(@"SEL$1" "db_mysql.t1"@"SEL$1") FULL(@"SEL$1" "db_mysql.t2"@"SEL$1") END_OUTLINE_DATA */ Plan Type: ------------------------------------- LOCAL Optimization Info: ------------------------------------- t1:table_rows:3, physical_range_rows:1, logical_range_rows:1, index_back_rows:0, output_rows:1, est_method:local_storage, optimization_method=rule_based, heuristic_rule=unique_index_without_indexback t2:table_rows:100000, physical_range_rows:1, logical_range_rows:1, index_back_rows:0, output_rows:1, est_method:default_stat, optimization_method=rule_based, heuristic_rule=unique_index_without_indexback Parameters ------------------------------------- | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.021 sec)
适用版本
OceanBase 数据库 V2.x 和 V3.x 版本
