hit不符预期

使用了use_nl 没有用 MySQL [test]> EXPLAIN SELECT /*+USE_NL(t1, t2)*/ t1.c2 + t2.d2 FROM t1, t2 → WHERE c2 = d2\G; *************************** 1. row *************************** Query Plan: ======================================== |ID|OPERATOR |NAME|EST. ROWS|COST | ---------------------------------------- |0 |HASH JOIN | |98010000 |58394053| |1 | TABLE SCAN|t1 |100000 |61860 | |2 | TABLE SCAN|t2 |100000 |61860 | ======================================== Outputs & filters: ------------------------------------- 0 - output([t1.c2 + t2.d2]), filter(nil), equal_conds([t1.c2 = t2.d2]), other_conds(nil) 1 - output([t1.c2]), filter(nil), access([t1.c2]), partitions(p0) 2 - output([t2.d2]), filter(nil), access([t2.d2]), partitions(p0) 1 row in set (0.01 sec)

MySQL [test]> EXPLAIN extended SELECT  t1.c2 + t2.d2 FROM t1, t2        WHERE c1 = d1\G
*************************** 1. row ***************************
Query Plan: ======================================
|ID|OPERATOR   |NAME|EST. ROWS|COST  |
--------------------------------------
|0 |HASH JOIN  |    |99000    |345403|
|1 | TABLE SCAN|t1  |100000   |64066 |
|2 | TABLE SCAN|t2  |100000   |61860 |
======================================


Outputs & filters:
-------------------------------------
  0 - output([t1.c2(0x7f63f78941f0) + t2.d2(0x7f63f7894480)(0x7f63f7893b70)]), filter(nil),
      equal_conds([t1.c1(0x7f63f783fab0) = t2.d1(0x7f63f783fd40)(0x7f63f783f430)]), other_conds(nil)
  1 - output([t1.c1(0x7f63f783fab0)], [t1.c2(0x7f63f78941f0)]), filter(nil),
      access([t1.c1(0x7f63f783fab0)], [t1.c2(0x7f63f78941f0)]), partitions(p0),
      is_index_back=false,
      range_key([t1.__pk_increment(0x7f63f78a9fe0)]), range(MIN ; MAX)always true
  2 - output([t2.d1(0x7f63f783fd40)], [t2.d2(0x7f63f7894480)]), filter(nil),
      access([t2.d1(0x7f63f783fd40)], [t2.d2(0x7f63f7894480)]), partitions(p0),
      is_index_back=false,
      range_key([t2.d1(0x7f63f783fd40)]), range(MIN ; MAX)always true


Used Hint:
-------------------------------------
  /*+
  */


Outline Data:
-------------------------------------
  /*+
      BEGIN_OUTLINE_DATA
      LEADING(@"SEL$1" ("test.t1"@"SEL$1" "test.t2"@"SEL$1" ))
      USE_HASH(@"SEL$1" ("test.t2"@"SEL$1" ))
      FULL(@"SEL$1" "test.t1"@"SEL$1")
      FULL(@"SEL$1" "test.t2"@"SEL$1")
      END_OUTLINE_DATA
  */


Plan Type:
-------------------------------------
LOCAL


Optimization Info:
-------------------------------------




t1:table_rows:100000, physical_range_rows:100000, logical_range_rows:100000, index_back_rows:0, output_rows:100000, est_method:basic_stat, optimization_method=cost_based, avaiable_index_name[t1]


t2:table_rows:100000, physical_range_rows:100000, logical_range_rows:100000, index_back_rows:0, output_rows:100000, est_method:basic_stat, optimization_method=cost_based, avaiable_index_name[t2]
Parameters
-------------------------------------


1 row in set (0.00 sec)

同问,我也遇到这个问题了

推测是 obclient连接时没有使用参数 -c