Re: Tuning: PLSQL/INSERT INTO statement

Micheal,

Here is the explain plan:

INSERT STATEMENT Cost = 422
  NESTED LOOPS
    NESTED LOOPS

      HASH JOIN
        VIEW
          SORT GROUP BY
            HASH JOIN
              TABLE ACCESS FULL OAS_DOCLINE
              TABLE ACCESS FULL OAS_DOCHEAD
        VIEW
          SORT GROUP BY
            HASH JOIN
              TABLE ACCESS FULL OAS_DOCHEAD
              TABLE ACCESS FULL OAS_DOCLINE
      TABLE ACCESS BY ROWID OAS_DOCLINE
        INDEX RANGE SCAN OAS_DOCLINE_IND2
    TABLE ACCESS BY ROWID OAS_DOCHEAD
      INDEX UNIQUE SCAN OAS_DOCHEAD_INDEX1

18 rows selected.

Why would this use so much temp table space?

<michael_bialik_at_my-deja.com> wrote in message news:850ac3$f2g$1_at_nnrp1.deja.com...
> Hi.
>
> Is it possible to post TKPROF or at least the EXPLAIN?
>
> Michael.
>
>
> In article <84uejd$ev3$1_at_ssauraac-i-1.production.compuserve.com>,
> "Kevin Burton" <pdsinc_at_compuserve.com> wrote:
> > I have a select query that I execute and it takes several minutes to
> get the
> > results back. Now I want to put those results into a table so I do:
> INSERT
> > INTO <TABLE>
> > SELECT Query.
> > Now the process takes hours to complete. The table has no
> constraints or
> > triggers. I did notice the temp table space keeps getting filled
> when I run
> > the query with the INSERT INTO clause but not when I run just the
> SELECT
> > statement. WHY?
> >
> > The query looke something like this:
> > SELECT A1,
> > A2,
> > A3,
> > A4,
> > A5,
> > FROM ( SELECT A1
> > A2
> > A3
> > sum(A4)
> > FROM ( SELECT A1,
> > A2,
> > min(A3)
> > FROM table A, Table B
> > WHERE A1 = B1 and
> > A2 = B2
> > GROUP BY A1,A2) A , TABLE B
> > WHERE A1 = B1 and
> > A2 = B2
> > GROUP BY A1,A2,A3) AA
> > WHERE AA.A1 = VALUE
> > AA.A2 = VALUE
> >
> > Any help would be appreciated. I just don't understand why it would
> take any
> > more Temp table space do the insert on this query as it would to just
> > execute the SELECT statement alone.
> >
> > - Kevin Burton
> >
> >
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Thu Jan 06 2000 - 22:42:08 CST