Re: SQL, count and group by

Hi Peter
English is not my native tongue.

I think the problem of Conan's query is access meter tow time.

in previous plan
 . inlin-view : join sample and meter and group by.  . main join qry : view result and meter table join.

So the next query handle meter only one time and unique scan pk_meter only(not full scan_.

select

    o.meter_recnum,
    nvl(i.samples,0)
from

    meter o,
    (select count(meter_recnum) samples, meter_recnum

       from sample s
      where s.sampletime between :sampletime_start and :sampletime_stop
      group by meter_recnum) i
where o.meter_recnum = i.meter_recnum (+)
      and o.checkconsistent =1

inline view is created. view-meter is nested looping.

"Peter Laursen" < ptl_at_edbgruppen.dk> wrote in message news:01c04004$3a227f00$2c289a0a_at_apollo...
> Hi Conan,
>
> Works like a charm. This is only some 15% slower than my original query.
>
> Thanks :-)
>
>
> real: 168542
>
> Udf?elsesplan
> ----------------------------------------------------------
>
> 0 SELECT STATEMENT Optimizer=RULE
>
> 1 0 MERGE JOIN (OUTER)
>
> 2 1 SORT (JOIN)
>
> 3 2 TABLE ACCESS (FULL) OF 'METER'
>
> 4 1 SORT (JOIN)
>
> 5 4 VIEW
>
> 6 5 SORT (GROUP BY)
>
> 7 6 NESTED LOOPS
>
> 8 7 TABLE ACCESS (BY INDEX ROWID) OF 'SAMPLE'
>
> 9 8 INDEX (RANGE SCAN) OF 'SAMPLE_IDX_SAMPLETIME'
>
> (NON-UNIQUE)
>
>
>
> 10 7 TABLE ACCESS (BY INDEX ROWID) OF 'METER'
>
> 11 10 INDEX (UNIQUE SCAN) OF 'PK_METER' (UNIQUE)
>
>
> Statistik
> ----------------------------------------------------------
>
> 0 recursive calls
>
> 3 db block gets
>
> 1191869 consistent gets
>
> 11809 physical reads
>
> 293496 redo size
>
> 1212 bytes sent via SQL*Net to client
>
> 1227 bytes received via SQL*Net from client
>
> 5 SQL*Net roundtrips to/from client
>
> 4 sorts (memory)
>
> 0 sorts (disk)
>
> 27 rows processed
>
>
> Peter Laursen
>
> Conan <conan@(no-spam)as-if.com> skrev i artiklen
> <OJbK5.10995$Bw1.6380_at_news.indigo.ie>...
> > Hi Peter Try this
> >
> > select
> > o.meter_recnum,
> > nvl(i.samples,0)
> > from
> > meter o,
> > (select count(s.sample_recnum) samples, m.meter_recnum
> > from sample s, meter m
> > where s.meter_recnum = m.meter_recnum
> > and m.checkconsistent = 1
> > and s.sampletime between :sampletime_start and :sampletime_stop
> > group by m.meter_recnum) i
> > where o.meter_recnum = i.meter_recnum (+)
> > and o.checkconsistent =1
> > /
> >
> > it should'nt be much slower than your original query, but I'm sure
 explain
> > plan will give you the details
> >
> > HTH
> > Conan
> >
> >
> > Peter Laursen wrote in message <01c03fef$f870f5a0$2c289a0a_at_apollo>...
> > >Small addition:
> > >Table SAMPLE has a colum meter_recnum, which is a foreign key to table
> > >METER. In SAMPLE (meter_recnum, sampletime) is unique.
> > >>
> > >SAMPLE (a few million rows)
> > >sample_recnum number primary key
> > >value number
> > >sampletime date not null (has index)
> > >meter_recnum number not null references METER(meter_recnum)
> > >
> > >unique(meter_recnum, sampletime)
> > >
> > >> Thanks
> > >> Peter Laursen
> > >>
> >
> >
> >
Received on Wed Nov 01 2000 - 03:30:02 CST