Notes in-line
--
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
April 2004 Iceland
June 2004 UK - Optimising Oracle Seminar
"Yong Huang" <yong321_at_yahoo.com> wrote in message
news:b3cb12d6.0403170306.2cd1a323_at_posting.google.com...
> I actually looked at execution plans for various forms of the queries.
> Also traced the sessions. Tried x$ktfthc and ts$ in place of
> v$temp_space_header, and tried more than a dozen hints. The first few
> hints I tried are ORDERED, USE_NL (because they appear in the view
> definition for v$temp_space_header), and UNNEST and NO_UNNEST (because
> these seem to be your favorite hints!, and for other reason). I have a
> feeling that if Oracle can finish querying one view completely before
> it joins with the other, then it works. Maybe that's why using an
> in-line view works. BTW, in all cases, changing orders in the FROM
> clause makes no difference.
>
Take a closer look at the gv$temp_space_header definition, and
note the hint /*+ ordered use_nl(hc) */ and the join condition into
the X$ aliased by hc. Then try querying that x$ all by itself.
I think the only was into the x$ is through a known temp file number
or temp tablespace number. So ANY join involving that x$ has to
supply one of those two values by driving a nested loop into the x$.
>
> I would consider this as a bug. I wouldn't, if *most* other v$ views
> had this problem.
>
I think "bug" is too severe a word - you are using a view defined by
Oracle for the benefit of Oracle in your application, and getting a
surprise result. End-users (in the role of developers) can create
views which when abused produce the wrong results - where's the
bug ?
Received on Wed Mar 17 2004 - 05:26:27 CST
