Using hints in oracle

Question

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.

Answer 1

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.