LEADING Hint
LEADING Hint 可以指定表的联接顺序，语法如下：
/*+ LEADING(table_name_list)*/
在
table_name_list 中可以使用
() 表示内部各表的联接优先级，指定复杂联接。示例如下：
obclient> EXPLAIN BASIC SELECT /*+LEADING(d c b a)*/ * FROM t1 a, t1 b, t1 c, t1 d; +---------------------------------------------------------------------------------------------------------------------------------------+ | Query Plan | +---------------------------------------------------------------------------------------------------------------------------------------+ | ========================================= || |ID|OPERATOR |NAME| || ---------------------------------------- || |0 |NESTED-LOOP JOIN CARTESIAN | | || |1 |├─NESTED-LOOP JOIN CARTESIAN | | || |2 |│ ├─NESTED-LOOP JOIN CARTESIAN | | || |3 |│ │ ├─TABLE FULL SCAN |d | || |4 |│ │ └─MATERIAL | | || |5 |│ │ └─TABLE FULL SCAN |c | || |6 |│ └─MATERIAL | | || |7 |│ └─TABLE FULL SCAN |b | || |8 |└─MATERIAL | | || |9 | └─TABLE FULL SCAN |a | || ========================================= || Outputs & filters: || ------------------------------------- || 0 - output([a.c1], [a.c2], [a.c3], [b.c1], [b.c2], [b.c3], [c.c1], [c.c2], [c.c3], [d.c1], [d.c2], [d.c3]), filter(nil), rowset=256 || conds(nil), nl_params_(nil), use_batch=false || 1 - output([b.c1], [b.c2], [b.c3], [c.c1], [c.c2], [c.c3], [d.c1], [d.c2], [d.c3]), filter(nil), rowset=256 || conds(nil), nl_params_(nil), use_batch=false || 2 - output([c.c1], [c.c2], [c.c3], [d.c1], [d.c2], [d.c3]), filter(nil), rowset=256 || conds(nil), nl_params_(nil), use_batch=false || 3 - output([d.c1], [d.c2], [d.c3]), filter(nil), rowset=256 || access([d.c1], [d.c2], [d.c3]), partitions(p0) || is_index_back=false, is_global_index=false, || range_key([d.__pk_increment]), range(MIN ; MAX)always true || 4 - output([c.c1], [c.c2], [c.c3]), filter(nil), rowset=256 || 5 - output([c.c1], [c.c2], [c.c3]), filter(nil), rowset=256 || access([c.c1], [c.c2], [c.c3]), partitions(p0) || is_index_back=false, is_global_index=false, || range_key([c.__pk_increment]), range(MIN ; MAX)always true || 6 - output([b.c1], [b.c2], [b.c3]), filter(nil), rowset=256 || 7 - output([b.c1], [b.c2], [b.c3]), filter(nil), rowset=256 || access([b.c1], [b.c2], [b.c3]), partitions(p0) || is_index_back=false, is_global_index=false, || range_key([b.__pk_increment]), range(MIN ; MAX)always true || 8 - output([a.c1], [a.c2], [a.c3]), filter(nil), rowset=256 || 9 - output([a.c1], [a.c2], [a.c3]), filter(nil), rowset=256 || access([a.c1], [a.c2], [a.c3]), partitions(p0) || is_index_back=false, is_global_index=false, || range_key([a.__pk_increment]), range(MIN ; MAX)always true | +--------------------------------------------------------------------------------------------------------------------------------------- +41 rows in set obclient> EXPLAIN BASIC SELECT /*+LEADING((d c) (b a))*/ * FROM t1 a, t1 b, t1 c, t1 d; +---------------------------------------------------------------------------------------------------------------------------------------+| Query Plan | +---------------------------------------------------------------------------------------------------------------------------------------+| ========================================= || |ID|OPERATOR |NAME| || ----------------------------------------- || |0 |NESTED-LOOP JOIN CARTESIAN | | || |1 |├─NESTED-LOOP JOIN CARTESIAN | | || |2 |│ ├─TABLE FULL SCAN |d | || |3 |│ └─MATERIAL | | || |4 |│ └─TABLE FULL SCAN |c | || |5 |└─MATERIAL | | || |6 | └─NESTED-LOOP JOIN CARTESIAN | | || |7 | ├─TABLE FULL SCAN |b | || |8 | └─MATERIAL | | || |9 | └─TABLE FULL SCAN |a | || ========================================= || Outputs & filters: || ------------------------------------- || 0 - output([a.c1], [a.c2], [a.c3], [b.c1], [b.c2], [b.c3], [c.c1], [c.c2], [c.c3], [d.c1], [d.c2], [d.c3]), filter(nil), rowset=256 || conds(nil), nl_params_(nil), use_batch=false || 1 - output([c.c1], [c.c2], [c.c3], [d.c1], [d.c2], [d.c3]), filter(nil), rowset=256 || conds(nil), nl_params_(nil), use_batch=false || 2 - output([d.c1], [d.c2], [d.c3]), filter(nil), rowset=256 || access([d.c1], [d.c2], [d.c3]), partitions(p0) || is_index_back=false, is_global_index=false, || range_key([d.__pk_increment]), range(MIN ; MAX)always true || 3 - output([c.c1], [c.c2], [c.c3]), filter(nil), rowset=256 || 4 - output([c.c1], [c.c2], [c.c3]), filter(nil), rowset=256 || access([c.c1], [c.c2], [c.c3]), partitions(p0) || is_index_back=false, is_global_index=false, || range_key([c.__pk_increment]), range(MIN ; MAX)always true || 5 - output([a.c1], [a.c2], [a.c3], [b.c1], [b.c2], [b.c3]), filter(nil), rowset=256 || 6 - output([a.c1], [a.c2], [a.c3], [b.c1], [b.c2], [b.c3]), filter(nil), rowset=256 || conds(nil), nl_params_(nil), use_batch=false || 7 - output([b.c1], [b.c2], [b.c3]), filter(nil), rowset=256 || access([b.c1], [b.c2], [b.c3]), partitions(p0) || is_index_back=false, is_global_index=false, || range_key([b.__pk_increment]), range(MIN ; MAX)always true || 8 - output([a.c1], [a.c2], [a.c3]), filter(nil), rowset=256 || 9 - output([a.c1], [a.c2], [a.c3]), filter(nil), rowset=256 || access([a.c1], [a.c2], [a.c3]), partitions(p0) || is_index_back=false, is_global_index=false, || range_key([a.__pk_increment]), range(MIN ; MAX)always true | +---------------------------------------------------------------------------------------------------------------------------------------+ 41 rows in set obclient> EXPLAIN BASIC SELECT /*+LEADING((d c b) a))*/ * FROM t1 a, t1 b, t1 c, t1 d; +---------------------------------------------------------------------------------------------------------------------------------------+ | Query Plan |+ ---------------------------------------------------------------------------------------------------------------------------------------+| ========================================= || |ID|OPERATOR |NAME| || ----------------------------------------- || |0 |NESTED-LOOP JOIN CARTESIAN | | || |1 |├─NESTED-LOOP JOIN CARTESIAN | | || |2 |│ ├─NESTED-LOOP JOIN CARTESIAN | | || |3 |│ │ ├─TABLE FULL SCAN |d | || |4 |│ │ └─MATERIAL | | || |5 |│ │ └─TABLE FULL SCAN |c | || |6 |│ └─MATERIAL | | || |7 |│ └─TABLE FULL SCAN |b | || |8 |└─MATERIAL | | || |9 | └─TABLE FULL SCAN |a | || ========================================= || Outputs & filters: || ------------------------------------- || 0 - output([a.c1], [a.c2], [a.c3], [b.c1], [b.c2], [b.c3], [c.c1], [c.c2], [c.c3], [d.c1], [d.c2], [d.c3]), filter(nil), rowset=256 || conds(nil), nl_params_(nil), use_batch=false || 1 - output([b.c1], [b.c2], [b.c3], [c.c1], [c.c2], [c.c3], [d.c1], [d.c2], [d.c3]), filter(nil), rowset=256 || conds(nil), nl_params_(nil), use_batch=false || 2 - output([c.c1], [c.c2], [c.c3], [d.c1], [d.c2], [d.c3]), filter(nil), rowset=256 || conds(nil), nl_params_(nil), use_batch=false || 3 - output([d.c1], [d.c2], [d.c3]), filter(nil), rowset=256 || access([d.c1], [d.c2], [d.c3]), partitions(p0) || is_index_back=false, is_global_index=false, || range_key([d.__pk_increment]), range(MIN ; MAX)always true || 4 - output([c.c1], [c.c2], [c.c3]), filter(nil), rowset=256 || 5 - output([c.c1], [c.c2], [c.c3]), filter(nil), rowset=256 || access([c.c1], [c.c2], [c.c3]), partitions(p0) || is_index_back=false, is_global_index=false, || range_key([c.__pk_increment]), range(MIN ; MAX)always true || 6 - output([b.c1], [b.c2], [b.c3]), filter(nil), rowset=256 || 7 - output([b.c1], [b.c2], [b.c3]), filter(nil), rowset=256 || access([b.c1], [b.c2], [b.c3]), partitions(p0) || is_index_back=false, is_global_index=false, || range_key([b.__pk_increment]), range(MIN ; MAX)always true || 8 - output([a.c1], [a.c2], [a.c3]), filter(nil), rowset=256 || 9 - output([a.c1], [a.c2], [a.c3]), filter(nil), rowset=256 || access([a.c1], [a.c2], [a.c3]), partitions(p0) || is_index_back=false, is_global_index=false, || range_key([a.__pk_increment]), range(MIN ; MAX)always true |+---------------------------------------------------------------------------------------------------------------------------------------+ 41 rows in set
为确保按照用户指定的顺序进行联接，
LEADING Hint 的检查比较严格，如果发现 Hint 指定的
table_name 不存在，
LEADING Hint 失效；如果发现 Hint 中存在重复表，
LEADING Hint 失效。如果在 Optimizer 联接期间，按
table_id 无法在 From Items 中找到对应的表，则可能发生改写，那么该表及后面的表指定的
JOIN 顺序失效，该表前面的
JOIN 顺序依然有效。
USE_NL Hint
和 join 相关的 Hint 的基本结构如下：
join_hint_name ( @ qb_name table_name_list) 基本语义为，当联接的右表匹配 table_name_list 时，按照 Hint 语义生成计划。一般需要使用
LEADING Hint 指定联接顺序，使 table_name_list 中的表为右表，否则 Hint 会随着联接顺序变化而失效。
其中 table_name_list 可有以下形式：
- 单表 use_nl ( t1 ): 以 t1 为右表时使用 Nested Loop Join。
- 多个单表 use_nl ( t1 t2 ... ): 以 t1 或 t2 等为右表时使用 Nested Loop Join。
- 多表 use_nl ( (t1 t2) ): 以 t1 join t2 为右表时使用 Nested Loop Join，忽略 t1/t2 连接顺序和方法。
- 多组表 use_nl ( t1 (t2 t3) (t4 t5 t6) ... ): 以 t1 / t2 join t3 / t4 join t5 join t6 等表为右表时使用 Nested Loop Join。
USE_NL 指定表作为右表，在联接的时候使用 Nested Loop Join 算法，语法如下：
/*+ USE_NL(table_name_list) */
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/543979/viewspace-3042943/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
