在oracle中使用提示

发布于 2024-12-12 06:04:29 字数 675 浏览 0 评论 0原文

我正在尝试将提示应用于我的查询，但解释计划不会更改为正在使用的提示。

我的查询

select/*+ USE_HASH(master_flight)*/ bid, b.fno, seat, flight_date from
master_booking b, master_flight f where b.fno = f.fno and rownum <
120000

解释计划

119999 COUNT STOPKEY (cr=11336 pr=446 pw=0 time=240292 us)
119999 NESTED LOOPS (cr=11336 pr=446 pw=0 time=120236 us)
800 TABLE ACCESS FULL ASS2MASTER_FLIGHT (cr=936 pr=441 pw=0 time=22455 us)
119999 TABLE ACCESS CLUSTER ASS2MASTER_BOOKING (cr=10400 pr=5 pw=0 time=6858 us)
800 INDEX UNIQUE SCAN FNO_INDEX (cr=1600 pr=5 pw=0 time=4717 us)(object id 332468)

如您所见，我强制集群使用散列连接而不是嵌套循环。但解释计划仍然显示它正在使用嵌套循环。

原文

I am trying to apply hints to my query but the explain plan does not change to the hint being used.

my query

select/*+ USE_HASH(master_flight)*/ bid, b.fno, seat, flight_date from
master_booking b, master_flight f where b.fno = f.fno and rownum <
120000

explain plan

119999 COUNT STOPKEY (cr=11336 pr=446 pw=0 time=240292 us)
119999 NESTED LOOPS (cr=11336 pr=446 pw=0 time=120236 us)
800 TABLE ACCESS FULL ASS2MASTER_FLIGHT (cr=936 pr=441 pw=0 time=22455 us)
119999 TABLE ACCESS CLUSTER ASS2MASTER_BOOKING (cr=10400 pr=5 pw=0 time=6858 us)
800 INDEX UNIQUE SCAN FNO_INDEX (cr=1600 pr=5 pw=0 time=4717 us)(object id 332468)

as you can see i am forcing the cluster to use hash join instead of nested loop. but the explain plan still shows that it is using nested loop.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

轻拂→两袖风尘 2024-12-19 06:04:29

一般来说，如果您使用提示，则需要引用别名，而不是表名。而USE_HASH需要两个表名。因此，您需要类似的内容

SELECT /*+ use_hash(b f) */ 
       bid, b.fno, seat, flight_date
  FROM master_booking b,
       master_flight  f
 WHERE b.fno = f.fno
   AND rownum < 120000

当然，如果您发现自己需要提示查询，这通常意味着您的统计数据不正确。通常，您最好解决统计数据中遇到的任何问题，以便优化器自行选择更有效的计划。

In general, if you're using a hint, you need to reference the aliases, not the table name. And USE_HASH requires two table names. So you'd need something like

SELECT /*+ use_hash(b f) */ 
       bid, b.fno, seat, flight_date
  FROM master_booking b,
       master_flight  f
 WHERE b.fno = f.fno
   AND rownum < 120000

Of course, if you find yourself needing to hint a query, that generally implies that your statistics are incorrect. And you're generally better off fixing whatever problem you have with the statistics so that the optimizer chooses the more efficient plan on its own.

回复收藏 0 原文

~没有更多了~