>>
>> >SELECT r.LINENR, r.COMPONENTNR, MAX(r.SUMWEIGHT)-MIN(r.SUMWEIGHT) as
>> >sumweightdiff FROM GRAVCOMPDATA_LONG r where r.GRAVTIMESTAMP Between
>> >CAST('NOW' AS TIMESTAMP) - 60/1440e0 AND CAST('NOW' AS TIMESTAMP)
>> >group by r.LINENR, r.COMPONENTNR
>>
>> You forgot to mention the main part of any optimization task, the chosen PLAN. This answer is
>> therefore based on expecting indexes not to be optimized for this query yet.
>>
>> Is there any GRAVTIMESTAMP > 'NOW'? If not, then add a DESCENDING INDEX for GRAVTIMESTAMP and
>> change your query to:
>>
>> SELECT r.LINENR, r.COMPONENTNR, MAX(r.SUMWEIGHT)-MIN(r.SUMWEIGHT) as
>> sumweightdiff FROM GRAVCOMPDATA_LONG r where r.GRAVTIMESTAMP >=
>> CAST('NOW' AS TIMESTAMP) - 60/1440e0 group by r.LINENR, r.COMPONENTNR
>>
>> Hopefully, this will change the PLAN and the execution time improve.
>>
>> HTH,
>> Set

>Thanks a lot for this idea. I could reduce execution time tremendously:
>
>Executing...
>Done.
>11189 fetches, 45 marks, 5624 reads, 30 writes.
>0 inserts, 0 updates, 0 deletes, 1792 index, 0 seq.
>Delta memory: 23356 bytes.
>Total execution time: 0.296s
>Script execution finished.
>
>and yes there is no dedicated plan, and now I know I should give more attention to the
>chapter in hellens book ;-). Do you think that the additional index has an notable effect
>to the insertion time for the records ?.

No, I don't think this index should matter much for the insertion time, Jörn, timestamps tend to be quite selective. As for PLANs, you wouldn't normally write any PLAN, but the optimizer (which is part of executing your query) always generate PLANs for SELECTs and they are vital when it comes to understanding why a particular query is slow. Without the PLAN, guessing is often the best we can do (provided the query itself is the problem), with the PLAN, our guessing improves a lot.

Set