<svein.erling.tysvaer@...> wrote:
>
> >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 ?.
Jörn
