派生表oracle,派生表让执行计划从NESTED LOOPS改变为HASH JOIN,为什么？

该楼层疑似违规已被系统折叠 隐藏此楼查看此楼

请教高手们，下面两条语句的执行计划为啥不同？HINT能够改变它么？

(t_menu表只有151行，表函数tf_merge_auth_func_user只有一个menu_id列，也只返回几十行)

SELECT a.menu_id,

a.parent_menu_id,

a.menu_name,a.serial_no,

CASE WHEN b.menu_id > 0 THEN 1 ELSE 0 END AS is_enabled,

CASE WHEN c.menu_id > 0 THEN 1 ELSE 0 END AS is_inherit,

a.depends

FROM t_menu a

LEFT JOIN TABLE(tf_merge_auth_func_user(1, 2, 2)) b

ON b.menu_id = a.menu_id

LEFT JOIN TABLE(tf_merge_auth_func_user(1001, 12, 1)) c

ON c.menu_id = a.menu_id;

执行计划

----------------------------------------------------------

Plan hash value: 477037710

----------------------------------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

----------------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1015K| 51M| 310K (4)| 01:02:07 |

| 1 | NESTED LOOPS OUTER | | 1015K| 51M| 310K (4)| 01:02:07 |

| 2 | NESTED LOOPS OUTER | | 12382 | 483K| 3744 (4)| 00:00:45 |

| 3 | TABLE ACCESS FULL | T_MENU | 151 | 4077 | 3 (0)| 00:00:01 |

| 4 | VIEW | | 82 | 1066 | 25 (4)| 00:00:01 |

|* 5 | COLLECTION ITERATOR PICKLER FETCH| TF_MERGE_AUTH_FUNC_USER | | | | |

| 6 | VIEW | | 82 | 1066 | 25 (4)| 00:00:01 |

|* 7 | COLLECTION ITERATOR PICKLER FETCH | TF_MERGE_AUTH_FUNC_USER | | | | |

----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

5 - filter("A"."MENU_ID"=VALUE(KOKBF$))

7 - filter("A"."MENU_ID"=VALUE(KOKBF$))

统计信息

----------------------------------------------------------

52235 recursive calls

62934 db block gets

31085 consistent gets

0 physical reads

10677764 redo size

5107 bytes sent via SQL*Net to client

456 bytes received via SQL*Net from client

12 SQL*Net roundtrips to/from client

22952 sorts (memory)

0 sorts (disk)

151 rows processed

SQL>

这条语句执行效率非常差。

改成下面的形式：

SELECT a.menu_id,

a.parent_menu_id,

a.menu_name,a.serial_no,

CASE WHEN b.menu_id > 0 THEN 1 ELSE 0 END AS is_enabled,

CASE WHEN c.menu_id > 0 THEN 1 ELSE 0 END AS is_inherit,

a.depends

FROM t_menu a

LEFT JOIN (SELECT menu_id FROM TABLE(tf_merge_auth_func_user(1, 2, 2))) b

ON b.menu_id = a.menu_id

LEFT JOIN (SELECT menu_id FROM TABLE(tf_merge_auth_func_user(1001, 12, 1))) c

ON c.menu_id = a.menu_id;

执行计划

----------------------------------------------------------

Plan hash value: 45898858

---------------------------------------------------------------------------------------------------------------

| Id | Operation | Name | Rows |Bytes | Cost (%CPU)| Time |

---------------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 441K| 13M| 61 (17)| 00:00:01 |

|* 1 | HASH JOIN RIGHT OUTER | | 441K| 13M| 61 (17)| 00:00:01 |

| 2 | COLLECTION ITERATOR PICKLER FETCH | TF_MERGE_AUTH_FUNC_USER | | | | |

|* 3 | HASH JOIN OUTER | | 8168 | 231K| 28 (4)| 00:00:01 |

| 4 | TABLE ACCESS FULL | T_MENU | 151 | 4077 | 3 (0)| 00:00:01 |

| 5 | COLLECTION ITERATOR PICKLER FETCH| TF_MERGE_AUTH_FUNC_USER | |

---------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - access("A"."MENU_ID"=VALUE(KOKBF$))

3 - access("A"."MENU_ID"=VALUE(KOKBF$))

统计信息

----------------------------------------------------------

381 recursive calls

108 db block gets

900 consistent gets

0 physical reads

17632 redo size

5089 bytes sent via SQL*Net to client

456 bytes received via SQL*Net from client

12 SQL*Net roundtrips to/from client

152 sorts (memory)

0 sorts (disk)

151 rows processed

SQL>

效果好了很多。为啥呢？我仅仅是把表函数变成了派生表啊。

既然HASH JOIN 效率高，我是否可以不用派生表，采用HINT来改变执行计划？

我用了/*+ USE_HASH(a,b,c)*/ 或者 /*+ USE_HASH(a,b)*/没有生效

看起来Oracle拒绝改变计划。

希望得到高手指点。谢谢！