Re: How can that be (SQL, v$temp_space_header and dba_tablespaces)

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 others. I have a feeling that if Oracle can finish querying one view completely and then join 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.

I would consider this as a bug. I wouldn't, if *most* other v$ views had this problem.

Yong Huang

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:<c35d4v$k99$1_at_titan.btinternet.com>...
> Some of the 'dynamic views' are actually calls
> to functions which return row sets. Some of the
> functions require specific inputs - such as a table
> name.
>
> If a join goes in the wrong order (for example
> the dynamic view becomes the driving table
> in a join) then the required input may not exist,
> so the function is called with a null input, and
> returns no rows. This may be relevant in the
> current case - you might try checking the
> execution plan.
>
>
> --
> 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.0403121426.bc02864_at_posting.google.com...
> > What does the doc say exactly? In Metalink Note:225566.995, it appears
> > that the sentence "You cannot join dynamic views" is not part of the
> > documentation, but the rest is. It's that added sentence that doesn't
> > sound right. Otherwise so many scripts we've been using all these
> > years are questionable. The known fact that queries on v$ views are
> > not always read consistent does not explain this buggy join on
> > v$temp_space_header.
> >
> > In addition to using the join with ORDERED hint, you can of course
> > select * from dba_tablespaces where tablespace_name = (select
> > tablespace_name from v$temp_space_header where tablespace_name =
> > 'TEMP'). That means the string 'TEMP' *is* the same in both views but
> > the join between the views does not work.
> >
> > Alternatively, change a query on v$temp_space_header into a "table":
> >
> > select th.TABLESPACE_NAME, status from (select tablespace_name from
> > v$temp_space_header) th, dba_tablespaces t where th.table
> > space_name=t.tablespace_name;
> >
> > Again, we have to think about an explanation.
> >
> > Yong Huang
Received on Wed Mar 17 2004 - 02:06:10 CST