Re: Help explain plan

It looks like FK1_SAMPLE is not being used. In order to use FK1_SAMPLE to retrieve a row, Oracle must at least have a METER_RECNUM value, because that is the leading column in the index. In order to get a METER_RECNUM value, it has to have already read the METER table and the only possible access path starting with the METER table is a full table scan of METER.

So, Oracle must use a full table scan to access either METER or LASTSAMPLE (a view, apparently). All things being equal, the rule based optimize will start with the *last* table in your from clause, which is why is scans SAMPLE and then joins to METER via the index PK_METER.

If you recreate FK1_SAMPLE so that SAMPLETIME is the first column, it should be used.

Good luck,
Matt

Peter Laursen wrote in message <01beb1a5$113070a0$2c289a0a_at_apollo>...
>Could someone help me understand this execution plan?
>Is the index FK1_SAMPLE actually used?
>What could I do to optimize this query?
>
>
>SELECT SUM(DECODE(M.METERACTION,1,S.VALUE,-S.VALUE)) VALUE, S.SAMPLETIME
>FROM METER M, LASTSAMPLE S
>WHERE M.METEREDI_RECNUM=:HOST1 AND M.METERACTION<>0 AND
>M.METER_RECNUM=S.METER_RECNUM
>AND S.SAMPLETIME BETWEEN :HOST2 AND :HOST3
>GROUP BY S.SAMPLETIME
>
>CREATE INDEX FK1_SAMPLE ON
> SAMPLE(METER_RECNUM, SAMPLETIME) ...
>
>SELECT STATEMENT Optimizer=RULE
> SORT (GROUP BY)
> MERGE JOIN (OUTER)
> SORT (JOIN)
> MERGE JOIN (OUTER)
> SORT (JOIN)
> NESTED LOOPS
> TABLE ACCESS (FULL) OF SAMPLE
> TABLE ACCESS (BY INDEX ROWID) OF METER
> INDEX (UNIQUE SCAN) OF PK_METER (UNIQUE)
> SORT (JOIN)
> TABLE ACCESS (FULL) OF SAMPLE_QUALITY
> SORT (JOIN)
> TABLE ACCESS (FULL) OF SAMPLE_USER
>
>Thanks
>Peter
Received on Tue Jun 08 1999 - 18:15:22 CDT