RE: ** ordered use_nl question
- From: A Joshi <ajoshi977@xxxxxxxxx>
- To: jonathan@xxxxxxxxxxxxxxxxxx, oracle-l@xxxxxxxxxxxxx
- Date: Tue, 20 Jun 2006 14:36:36 -0700 (PDT)
Gints,
Thanks for your help. I used leading hint and it is working. I assume there
is no problem with using it. Thanks again. It was of great help.
Jonathan/Wolfgang,
Yes. Thanks for your help. The hint is working same for me with or wihout
the comma. I have four tables. I would like one of them to be the beginning
table. I was able to do it using leading hint as follows. I will try implement
histogram. Thanks
/*+ leading (b) use_nl(a) */
It is much faster with this. I am still doing full table scan on table b. I
assume histogram will take care of it since earlier it was doing index scan on
less selective index.
Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx> wrote:
Wolfgang,
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
use_nl(a)
use_nl(b)
use_nl(c)
use_nl(d)
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
/*+
ordered
use_nl(t1, t4, t2, t3)
*/
if its use is valid.
See you in Zurich this evening.
Regards
Jonathan Lewis
http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
Date: Mon, 19 Jun 2006 12:03:15 -0700
From: Wolfgang Breitling
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.
--
//www.freelists.org/webpage/oracle-l
---------------------------------
Yahoo! Groups gets better. Check out the new email design. Plus there?s much
more to come.
Other related posts:
