DA Morgan wrote:
> 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. ;-)
No - it's a SQL Server or Sybase background...
--
Regards,
Frank van Bortel
Top-posting is one way to shut me up...
Received on Tue Nov 22 2005 - 13:50:01 CST
