Re: Insert into totally slow...

On 2005-11-21, DA Morgan <damorgan_at_psoug.org> wrote:
> 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?

I'd say the OP wants to have distinct values from T_TAB1. In this case,   insert into t_temp(kdnr) select distinct kdnr from t_tab1 would be more obvious.

-- 
  Rene Nyffenegger
  http://www.adp-gmbh.ch/