Rene Nyffenegger wrote:
> 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.
Or maybe it is just needless overhead. That is why I asked. ;-)
--
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)
Received on Mon Nov 21 2005 - 20:03:34 CST
