OceanBase管理数据库Optimizer Hint（三）

Hint 使用规则

Hint 的一般使用规则如下：

• 对于没有指定 Query Block 的 Hint 表明作用于本 Query Block。

  示例 1：由于t2 表在 Query Block2，并且无法通过改写提升到 Query Block1，所以 Hint 无法生效。

  obclient> EXPLAIN SELECT /*+INDEX(t2 t2_c1)*/ * 
          FROM t1 , (SELECT * FROM t2 WHERE c2 = 1 LIMIT 5)
          WHERE t1.c1 = 1\G
  *************************** 1. row ***************************
  
  Query Plan:
  =========================================================
  |ID|OPERATOR                  |NAME     |EST. ROWS|COST |
  ---------------------------------------------------------
  |0 |NESTED-LOOP JOIN CARTESIAN|         |4950     |28686|
  |1 | TABLE SCAN               |t1(t1_c1)|990      |26669|
  |2 | MATERIAL                 |         |5        |207  |
  |3 |  SUBPLAN SCAN            |         |5        |207  |
  |4 |   TABLE SCAN             |t2       |5        |207  |
  =========================================================
  
  
  Outputs & filters: 
  -------------------------------------
    0 - output([t1.c1], [t1.c2], [.c1], [.c2]), filter(nil), 
        conds(nil), nl_params_(nil)
    1 - output([t1.c1], [t1.c2]), filter(nil), 
        access([t1.c1], [t1.c2]), partitions(p0)
    2 - output([.c1], [.c2]), filter(nil)
    3 - output([.c1], [.c2]), filter(nil), 
        access([.c1], [.c2])
    4 - output([t2.c1], [t2.c2]), filter([t2.c2 = 1]), 
        access([t2.c2], [t2.c1]), partitions(p0), 
        limit(5), offset(nil)
  

  示例 2：SQL 可以通过改写将 t1 表提升到 SEL$1，则 Hint 生效。

  obclient> EXPLAIN SELECT /*+INDEX(t2 t2_c1)*/ * 
          FROM t1 , (SELECT * FROM t2 WHERE c2 = 1)
          WHERE t1.c1 = 1\G
  *************************** 1. row ***************************
  Query Plan:
  ==========================================================
  |ID|OPERATOR                  |NAME     |EST. ROWS|COST  |
  ----------------------------------------------------------
  |0 |NESTED-LOOP JOIN CARTESIAN|         |980100   |772448|
  |1 | TABLE SCAN               |t1(t1_c1)|990      |26669 |
  |2 | MATERIAL                 |         |990      |387237|
  |3 |  TABLE SCAN              |t2(t2_c1)|990      |387227|
  ==========================================================
  
  
  Outputs & filters: 
  -------------------------------------
    0 - output([t1.c1], [t1.c2], [t2.c1], [t2.c2]), filter(nil), 
        conds(nil), nl_params_(nil)
    1 - output([t1.c1], [t1.c2]), filter(nil), 
        access([t1.c1], [t1.c2]), partitions(p0)
    2 - output([t2.c1], [t2.c2]), filter(nil)
    3 - output([t2.c2], [t2.c1]), filter([t2.c2 = 1]), 
        access([t2.c2], [t2.c1]), partitions(p0)
  

• 如果指定表行为，但在本 Query Block 中没有找到该表，或者发生冲突，那么 Hint 无效。

  对于没有找到表的情况可以参考第一条规则中的示例 1。如下示例为同时找到两个冲突的情况。

  obclient> EXPLAIN SELECT /*+INDEX(t1 PRIMARY)*/ * 
          FROM t1 , (SELECT * FROM t1 WHERE c1 = 1)
          WHERE t1.c1 = 1\G
  *************************** 1. row ***************************
  Query Plan:
  ==========================================================
  |ID|OPERATOR                  |NAME     |EST. ROWS|COST  |
  ----------------------------------------------------------
  |0 |NESTED-LOOP JOIN CARTESIAN|         |980100   |411890|
  |1 | TABLE SCAN               |t1(t1_c1)|990      |26669 |
  |2 | MATERIAL                 |         |990      |26679 |
  |3 |  TABLE SCAN              |t1(t1_c1)|990      |26669 |
  ==========================================================
  
  Outputs & filters: 
  -------------------------------------
    0 - output([t1.c1], [t1.c2], [t1.c1], [t1.c2]), filter(nil), 
        conds(nil), nl_params_(nil)
    1 - output([t1.c1], [t1.c2]), filter(nil), 
        access([t1.c1], [t1.c2]), partitions(p0)
    2 - output([t1.c1], [t1.c2]), filter(nil)
    3 - output([t1.c1], [t1.c2]), filter(nil), 
        access([t1.c1], [t1.c2]), partitions(p0)
  

• 联接方法的 Hint 中指定的表如果找不到，则忽略该表，其他的指定依然生效；如果优化器不能生成指定的联接方法，就会选择其他方法，Hint 无效。

• 联接顺序的 Hint 中如果存在表无法找到的情况，则该 Hint 完全失效。