Re: SQL Tuning challenge

From: Gints Plivna <gints.plivna_at_gmail.com>
Date: Tue, 26 Apr 2005 18:56:33 +0300
Message-ID: <6e49b6d005042608561ab5a17@mail.gmail.com>

Yea and it seems, that you use following piece of code 3 times:

(select free_sp from TBLSPACE_GROWTH a

        where a.dbname =3D3D3D b.dbname
        and a.name =3D3D3D b.name
        and a.cdate =3D3D3D (select max(trunc(c.cdate)) from =3D

TBLSPACE_GROWTH
c

                       where a.dbname =3D3D3D c.dbname
                         and a.name =3D3D3D c.name
                         and c.CDATE >=3D3D3D trunc(sysdate -90)))

You may check WITH keyword for SELECT statements that allows Oracle execute some subselects once and then use it many times. I'v used WITH subselects with rather great success for some reports that should display almost the same results but in different carvings (is this the right term?? not sure..) in the same report. For my reports WITH subselect makes some preaggregation and then SELECT does final computation with this preaggregation, not with initial data set.

On 4/26/05, Goulet, Dick <DGoulet_at_vicr.com> wrote:
> You might want to look into some of the analytics included in the rdbms,
> like lead & lag. I use them to calculate a daily usage from stored
> facts & then average that.=3D20
>

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Apr 26 2005 - 12:01:26 CDT