使用了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