RE: ** ordered use_nl question

  • From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
  • To: <oracle-l@xxxxxxxxxxxxx>
  • Date: Tue, 20 Jun 2006 07:41:32 +0100


The comma in use_nl (a,b,c,d) is allowed
(at least in some versions of Oracle) even
though it may not be expected.

The interpretation of the hint is that it is
simply a shorthand for

I think it would be quite helpful if the OP
posted the original SQL, showing us the
actual use of hint. There is no reason for
Oracle to ignore something like
     use_nl(t1, t4, t2, t3)
if its use is valid.

See you in Zurich this evening.


Jonathan Lewis

The Co-operative Oracle Users' FAQ

Cost Based Oracle: Fundamentals

Date: Mon, 19 Jun 2006 12:03:15 -0700
From: Wolfgang Breitling <breitliw@xxxxxxxxxxxxx>
Subject: RE: ** ordered use_nl question

The syntax is still not right. Read the documentation (as I did): No comma in
the list within the use_nl hint. Also, according to the documentation, the table
(s) listed in the hint "is the name or alias of a table to be used as the inner
table of a nested loops join". I know that a list is legal, I just don't know
what that is supposed to mean in terms of being the inner table in an NL join.
I always use a single alias in the use_nl hint, except I hardly use such hints
at all. If the selectivity of the predicate used to select the driving table in
the current plan, maybe a better approach would be to collect a histogram so
that the optimizer "knows" that.


Other related posts: