Re: Help explain plan

Hi Peter,

Apparently LASTSAMPLE is a view, so it is difficult to tell what is going on without having ALL the details.
From the plan it looks like the view is optimised away as usually happens using the rule based optimiser.
You need to read this output inside out, so line 9 is the innermost line and that's being processed first, followed by line 8. This means METER is the so called 'driving table'.
And no, the index is not being used, but I can't tell you without looking at LASTSAMPLE.
There's one thing that strikes me
>M.METER_RECNUM=S.METER_RECNUM

The rule based optimiser is extremely critical about the correct sequence. Generally speaking a join condition should be written as FK column = PK column
and not the other way around.
In this case, it may also be the reason why the optimiser doesn't detect meter_recnum is the leading column of a composite index. For an index to be used, the indexed column should appear left in a condition.

Hth,

Sybrand Bakker, Oracle DBA

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

1>  SORT (GROUP BY)
2>    MERGE JOIN (OUTER)
3>      SORT (JOIN)
4>        MERGE JOIN (OUTER)
5>          SORT (JOIN)
6>            NESTED LOOPS
7>              TABLE ACCESS (FULL) OF SAMPLE
8>              TABLE ACCESS (BY INDEX ROWID) OF METER
9>                INDEX (UNIQUE SCAN) OF PK_METER (UNIQUE)
10>          SORT (JOIN)
11>            TABLE ACCESS (FULL) OF SAMPLE_QUALITY
12>      SORT (JOIN)
13>        TABLE ACCESS (FULL) OF SAMPLE_USER