Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2003
    Posts
    2,296

    Unanswered: max function tanking my query

    I need some advice.
    Is there any way to get the max(date) without getting this many buffer gets?

    Without the max function the query runs great. Once I put an order by or max in there, as you see, it tanks.

    thanks.
    PHP Code:
    SELECT /*+ index (b MTR_RDNG_RDNG_DT_PK) */
                
    rdng_dt
                    FROM METER_READING b
                    WHERE b
    .org_id 'ME'
                    
    AND b.device_id IN (SELECT device_id FROM ACTIVE_DEVICE_MV a WHERE
                                                       b
    .org_id a.org_id AND
                                                       
    b.device_id a.device_id)
                    AND 
    b.rdng_dt SYSDATE-1

    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        1      0.01       0.00          0          0          0           0
    Execute      1      0.00       0.00          0          0          0           0
    Fetch        1      0.00       0.00          0          4          0          25
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total        3      0.01       0.01          0          4          0          25

    Misses in library cache during parse
    1
    Optimizer goal
    CHOOSE
    Parsing user id
    45  (PROD_COPY)

    Rows     Row Source Operation
    -------  ---------------------------------------------------
         
    25  MERGE JOIN SEMI (cr=4 r=0 w=0 time=1503 us)
         
    25   INDEX RANGE SCAN MTR_RDNG_RDNG_DT_PK (cr=3 r=0 w=0 time=326 us)(object id 168
    142
    )
         
    25   SORT UNIQUE (cr=1 r=0 w=0 time=853 us)
         
    55    INDEX RANGE SCAN ACTIVE_DEVICE_MV3_IX (cr=1 r=0 w=0 time=219 us)(object id 1
    68334
    )


    Rows     Execution Plan
    -------  ---------------------------------------------------
          
    0  SELECT STATEMENT   GOALCHOOSE
         25   NESTED LOOPS
         25    SORT 
    (UNIQUE)
         
    25     INDEX   GOALANALYZED (RANGE SCANOF
                    
    'ACTIVE_DEVICE_MV3_IX' (NON-UNIQUE)
         
    55    INDEX (RANGE SCANOF 'MTR_RDNG_RDNG_DT_PK' (UNIQUE)

    ********************************************************************************

    SELECT /*+ index (b MTR_RDNG_RDNG_DT_PK) */
                
    max(rdng_dt)
                    
    FROM METER_READING b
                    WHERE b
    .org_id 'ME'
                    
    AND b.device_id IN (SELECT device_id FROM ACTIVE_DEVICE_MV a WHERE
                                                       b
    .org_id a.org_id AND
                                                       
    b.device_id a.device_id)
                    AND 
    b.rdng_dt SYSDATE-1

    call     count       cpu    elapsed       disk      query    current        rows
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    Parse        1      0.00       0.00          0          0          0           0
    Execute      1      0.01       0.00          0          0          0           0
    Fetch        1      7.65      10.34          0       2111          0           1
    ------- ------  -------- ---------- ---------- ---------- ----------  ----------
    total        3      7.66      10.35          0       2111          0           1

    Misses in library cache during parse
    1
    Optimizer goal
    CHOOSE
    Parsing user id
    45  (PROD_COPY)

    Rows     Row Source Operation
    -------  ---------------------------------------------------
          
    1  SORT AGGREGATE (cr=2111 r=0 w=0 time=10344832 us)
     
    468491   HASH JOIN  (cr=2111 r=0 w=0 time=8994030 us)
         
    55    INDEX RANGE SCAN ACTIVE_DEVICE_MV3_IX (cr=1 r=0 w=0 time=275 us)(object id 168334)
     
    486900    INDEX RANGE SCAN MTR_RDNG_RDNG_DT_PK (cr=2110 r=0 w=0 time=3977740 us)(object id 168142)


    Rows     Execution Plan
    -------  ---------------------------------------------------
          
    0  SELECT STATEMENT   GOALCHOOSE
          1   SORT 
    (AGGREGATE)
     
    468491    NESTED LOOPS
         55     INDEX   GOAL
    ANALYZED (RANGE SCANOF
                    
    'ACTIVE_DEVICE_MV3_IX' (NON-UNIQUE)
     
    486900     INDEX (RANGE SCANOF 'MTR_RDNG_RDNG_DT_PK' (UNIQUE)

    ******************************************************************************** 
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  2. #2
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    I tend to avoid correlated subqueries (i.e.subqueries which are executed for each row in the driving table i.e. b) if possible. This may or may not make a difference. The other thing is when doing the max it seems to be trying to go through the whole of the meter reading index first (?) and then joining to the other index. It could be you need to remove the hint or try some other hints so it doesnt drive of the b index first.

    SELECT /*+ index (a ACTIVE_DEVICE_MV3_IX) */
    max(rdng_dt)
    FROM METER_READING b, ACTIVE_DEVICE_MV a
    WHERE b.org_id = 'ME'
    AND b.device_id = a.device_id
    AND b.org_id = a.org_id
    AND b.device_id = a.device_id
    AND b.rdng_dt < SYSDATE-1

    if it doesnt make a diff try

    select max(x) from
    (
    SELECT /*+ index (b MTR_RDNG_RDNG_DT_PK) */
    rdng_dt x
    FROM METER_READING b, ACTIVE_DEVICE_MV a
    WHERE b.org_id = 'ME'
    AND b.device_id = a.device_id
    AND b.org_id = a.org_id
    AND b.device_id = a.device_id
    AND b.rdng_dt < SYSDATE-1
    )

    Alan

  3. #3
    Join Date
    Jul 2003
    Posts
    2,296
    I think I screwed something up.

    the query should not be like that, but it is similar.
    I'll post the correct version later.
    sorry.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •