Re: 8i - Joining with Temporary Tables

From: Thomas J. Kyte <tkyte_at_us.oracle.com>
Date: 2000/07/02
Message-ID: <8job7c$a3b$1@nnrp1.deja.com>#1/1

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:

ops$tkyte_at_8i>
ops$tkyte_at_8i> create global temporary table temptable ( x int, y char (2000) );

Table created.

ops$tkyte_at_8i>
ops$tkyte_at_8i> create index temptable_idx on temptable( x );

Index created.

ops$tkyte_at_8i>
ops$tkyte_at_8i>
ops$tkyte_at_8i> set autotrace on explain
ops$tkyte_at_8i> select x from temptable where x is not null order by x;

no rows selected

Execution Plan

---

   0 SELECT STATEMENT Optimizer=CHOOSE    1 0 SORT (ORDER BY)
   2 1 TABLE ACCESS (FULL) OF 'TEMPTABLE'

ops$tkyte_at_8i> set autotrace off
ops$tkyte_at_8i>
ops$tkyte_at_8i> begin
  2      dbms_stats.set_table_stats( user,
  3                                 'TEMPTABLE',
  4                                  NULL,
  5                                  NULL,
  6                                  NULL,
  7                                  1000000,
  8                                  1000000/3,
  9                                  2004,
 10                                  NULL,
 11                                  NULL );

 12 end;
 13 /

PL/SQL procedure successfully completed.

ops$tkyte_at_8i> set autotrace on explain
ops$tkyte_at_8i> select x from Temptable where x is not null order by x;

no rows selected

Execution Plan

---

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=26 Card=50000 Bytes=
          1300000)

   1    0   INDEX (FULL SCAN) OF 'TEMPTABLE_IDX' (NON-UNIQUE) (Cost=26
           Card=50000 Bytes=1300000)

Seems to indicate the stats will appear to be present to the optimizer since the same query generates a plan that is only possible with the CBO after forcing stats onto the temp table.....

Perhaps Dylan (i've cc'ed him) can try this out and let us know. He can set the number of rows to what he believes is the correct amount using dbms_stats after creating the tempporary table and the CBO should have a better go of it.

> --
>
> Jonathan Lewis
> Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk
>
> Martin Haltmayer wrote in message <395F3946.D06F02F3_at_0800-
 einwahl.de>...
> >Of course you can analyze them without loosing their contents if you
 created
> >them with "on commit preserve rows":
> >
> >
> >Jonathan Lewis wrote:
> >>
> >> Unfortunately you have hit the very point
> >> that makes temporary tables a bit of a pain.
> >> You cannot analyze them, so Oracle can
> >> generate some totally atrocious plans when
> >> you mix temporary and permanent tables.
>
>

--
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.

Received on Sun Jul 02 2000 - 00:00:00 CDT