Re: SQL, count and group by

Hi Conan,

Works like a charm. This is only some 15% slower than my original query.

Thanks :-)

 real: 168542

Udførelsesplan

                        
   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 Fri Oct 27 2000 - 05:53:43 CDT