Re: Insert into totally slow...

Florian Melcher wrote:
> Hello,
>
> I have 2 tables (T_TAB1, T_TEMP). T_TAB1 has approximately 30 columns
> and 2.6 Mio. records (rows). I put an index on KDNR (NONUNIQUE).
>
> For both tables I created statistics, as well as for the index
> (calculator symbol in TOAD 7.4).
>
> My SQL-statement is the following:
>
> INSERT INTO T_TEMP (KDNR)
> SELECT KDNR
> FROM T_TAB1 X
> GROUP BY KDNR
>
> The explain plan is:
>
> SELECT STATEMENT Hint=CHOOSE (Rows: 874K, Bytes: , Cost: 6800)
> - SORT GROUP BY NOSORT (Rows: 874K, Bytes: 5M , Cost: 6800)
> INDEX FULL SCAN (Rows: 2M, Bytes: 15M , Cost: 6800)
>
> The query needs about 1 minute.
>
> Is this a normal execution time? I would assume only a few seconds, but
> I am a newbie in Oracle.
>
> How can I increase the performance of the query?
>
> Thanks
>
> Florian

Your statement is:

INSERT INTO T_TEMP (KDNR)
SELECT KDNR
FROM T_TAB1 X
GROUP BY KDNR What is the point of the GROUP BY?

Also ... your EXPLAIN PLAN isn't unless you are using a Neolithic version of Oracle which you didn't identify.

My recommendations would be (1) drop the index. (2) Use the APPEND hint. (3) If you post here again state Oracle version and provide an explain plan built with DBMS_XPLAN.

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)