Re: 8i - Joining with Temporary Tables

In article <962576014.29047.0.nnrp-13.9e984b29_at_news.demon.co.uk>,   "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote:
>
> Very cute idea !
>
> It would be interesting to set up a
> few tests to see what happens if
> two users are using the same
> temporary table though -
> last one in wins ?
> cursor invalidation ?
> any chance of deadlock ?
>

My thought was to once and for all put a good "guestimate" in there -- not to be too finicky about it (you know, pick some sizes for small, medium and large temp tables and use those).

In that fashion, you should be able to avoid the heuristics (guesses with static numbers) and let us know if you expect 1, 100, 1000 or 1,000,000 rows to be there (and go further with index stats I guess if you wanted).

I wouldn't do it each time -- probably be too expensive (it's recursive sql so you are right, last one in wins) and you'd be flushing the cursor cache lots with all of the different stats that would be going up there. Wouldn't want that at all -- parse times would go through the roof. Would just put the "best" guess in there.

> --
>
> Jonathan Lewis
> Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk
>
> Thomas J. Kyte wrote in message <8job7c$a3b$1_at_nnrp1.deja.com>...
> >In article <962567053.10947.0.nnrp-14.9e984b29_at_news.demon.co.uk>,
> > "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote:
> >>
> >> Whoops, lack of precision in what I said.
> >> Let me rephrase my comment:
> >> You can run the analyze command against
> >> temporary tables, but it doesn't actually
> >> do anything (unless you have used the
> >> extensibility feature to add your own
> >> indexing methods).
> >>
> >
> >You just gave me an idea. what about dbms_stats? we can analyze
 the
> >table (but that doesn't have any effect -- it'll come back with NULL
> >num_rows and such) but we can write the stats we believe will be true
> >into the DD using dbms_stats. The following:
> >
>
>

--
Thomas Kyte (tkyte_at_us.oracle.com) Oracle Service Industries
Howtos and such: http://osi.oracle.com/~tkyte/index.html
Oracle Magazine: http://www.oracle.com/oramag
Opinions are mine and do not necessarily reflect those of Oracle Corp


Sent via Deja.com http://www.deja.com/
Before you buy.