This discussion is archived
1 2 3 Previous Next 34 Replies Latest reply: Jul 3, 2012 2:53 PM by user503699 Go to original post RSS
  • 15. Re: UPDATE with ROWNUM (or TOP-N UPDATE)
    user503699 Expert
    Currently Being Moderated
    user503699 wrote:
    But this definitely looks like something I will try to use if it can reduce the resource consumption and post back my findings.
    Just wanted to post follow-up.
    I have managed to use Charles' suggestion above and initial tests suggest a BIG improvement in reducing the I/Os. We are still in the process of doing a full QA but I believe the idea of creating additional index along with hinting the UPDATE statement has improved the efficiency of the execution a lot.

    Charles,

    BIG BIG THANK YOU for your patience and help on this. Lesson for me in this process is "DO NOT ASSUME, TEST IT"

    Just to give an idea of how things look, following are the stats of different approaches.
    Current UPDATE (without any hints or new index):
    ----------------------------------------------------------------------------------------------------------------
    | Id  | Operation                   | Name            | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
    ----------------------------------------------------------------------------------------------------------------
    |   0 | UPDATE STATEMENT              |                 |   100 |  4400 |       |  7058      (1)| 00:01:25 |
    |   1 |  UPDATE                    | XXXXXXXXXXXXX    |       |       |       |         |            |
    |   2 |   NESTED LOOPS                   |                 |   100 |  4400 |       |  7058      (1)| 00:01:25 |
    |   3 |    VIEW                    | VW_NSO_1            |   100 |  1300 |       |  6857      (1)| 00:01:23 |
    |   4 |     SORT UNIQUE               |                 |   100 |  1300 |       |         |            |
    |*  5 |      COUNT STOPKEY              |                 |       |       |       |         |            |
    |   6 |       VIEW                   |                 | 14417 |   183K|       |  6857      (1)| 00:01:23 |
    |*  7 |        SORT ORDER BY STOPKEY         |                 | 14417 |   760K|  1032K|  6857      (1)| 00:01:23 |
    |*  8 |      FILTER                   |                 |       |       |       |         |            |
    |*  9 |       TABLE ACCESS FULL         | XXXXXXXXXXXXX    | 14417 |   760K|       |  6664      (1)| 00:01:20 |
    |* 10 |       TABLE ACCESS BY INDEX ROWID| YYYYYYYYYY       |     1 |     9 |       |     2      (0)| 00:00:01 |
    |* 11 |        INDEX UNIQUE SCAN         | YYYYYYYYYY_PK    |     1 |       |       |     1      (0)| 00:00:01 |
    |* 12 |    INDEX UNIQUE SCAN              | XXXXXXXXXXXXX_PK |     1 |    31 |       |     1      (0)| 00:00:01 |
    ----------------------------------------------------------------------------------------------------------------
    
    Statistics
    ----------------------------------------------------------
           0  recursive calls
           1314  db block gets
         107230  consistent gets
          79407  physical reads
         121432  redo size
         838  bytes sent via SQL*Net to client
           1178  bytes received via SQL*Net from client
           3  SQL*Net roundtrips to/from client
           3  sorts (memory)
           0  sorts (disk)
         100  rows processed
    Same UPDATE but with hint to use existing index:
    --------------------------------------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                   | Name           | Starts | E-Rows | A-Rows |     A-Time      | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
    --------------------------------------------------------------------------------------------------------------------------------------------------------
    |   0 | UPDATE STATEMENT              |                |     1 |        |       0 |00:00:01.39 |   36381 |      22 |         |         |            |
    |   1 |  UPDATE                    | XXXXXXXXXXXXX      |     1 |        |       0 |00:00:01.39 |   36381 |      22 |         |         |            |
    |   2 |   NESTED LOOPS                   |                |     1 |    100 |     100 |00:00:01.28 |   35067 |      22 |         |         |            |
    |   3 |    VIEW                    | VW_NSO_1           |     1 |    100 |     100 |00:00:01.28 |   34865 |      22 |         |         |            |
    |   4 |     SORT UNIQUE               |                |     1 |    100 |     100 |00:00:01.28 |   34865 |      22 | 73728 | 73728 |            |
    |*  5 |      COUNT STOPKEY              |                |     1 |        |     100 |00:00:01.28 |   34865 |      22 |         |         |            |
    |   6 |       VIEW                   |                |     1 |  14417 |     100 |00:00:01.28 |   34865 |      22 |         |         |            |
    |*  7 |        SORT ORDER BY STOPKEY         |                |     1 |  14417 |     100 |00:00:01.28 |   34865 |      22 | 11264 | 11264 |10240  (0)|
    |*  8 |      FILTER                   |                |     1 |        |     530K|00:00:01.02 |   34865 |      22 |         |         |            |
    |*  9 |       TABLE ACCESS BY INDEX ROWID| XXXXXXXXXXXXX      |     1 |  14417 |     530K|00:00:00.81 |   34853 |      22 |         |         |            |
    |* 10 |        INDEX RANGE SCAN         | XXXXXXXXXXXXX_IXX1 |     1 |  14418 |     530K|00:00:00.29 |    5980 |       6 |         |         |            |
    |* 11 |       TABLE ACCESS BY INDEX ROWID| YYYYYYYYYY      |     4 |      1 |       3 |00:00:00.01 |     12 |       0 |         |         |            |
    |* 12 |        INDEX UNIQUE SCAN         | YYYYYYYYYY_PK      |     4 |      1 |       4 |00:00:00.01 |      8 |       0 |         |         |            |
    |* 13 |    INDEX UNIQUE SCAN              | XXXXXXXXXXXXX_PK      |    100 |      1 |     100 |00:00:00.01 |     202 |       0 |         |         |            |
    --------------------------------------------------------------------------------------------------------------------------------------------------------
    Finally, with new index as well as hints:
    -----------------------------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                   | Name           | Starts | E-Rows | A-Rows |     A-Time      | Buffers |  OMem |  1Mem | Used-Mem |
    -----------------------------------------------------------------------------------------------------------------------------------------------
    |   0 | UPDATE STATEMENT              |                |     1 |        |       0 |00:00:00.01 |    1616 |        |        |           |
    |   1 |  UPDATE                    | XXXXXXXXXXXXX      |     1 |        |       0 |00:00:00.01 |    1616 |        |        |           |
    |   2 |   NESTED LOOPS                   |                |     1 |    100 |     100 |00:00:00.01 |     300 |        |        |           |
    |   3 |    VIEW                    | VW_NSO_1           |     1 |    100 |     100 |00:00:00.01 |     98 |        |        |           |
    |   4 |     SORT UNIQUE               |                |     1 |    100 |     100 |00:00:00.01 |     98 | 73728 | 73728 |           |
    |*  5 |      COUNT STOPKEY              |                |     1 |        |     100 |00:00:00.01 |     98 |        |        |           |
    |   6 |       VIEW                   |                |     1 |  14417 |     100 |00:00:00.01 |     98 |        |        |           |
    |*  7 |        FILTER                   |                |     1 |        |     100 |00:00:00.01 |     98 |        |        |           |
    |   8 |      NESTED LOOPS OUTER         |                |     1 |  14417 |     100 |00:00:00.01 |     98 |        |        |           |
    |   9 |       TABLE ACCESS BY INDEX ROWID| XXXXXXXXXXXXX      |     1 |  14417 |     100 |00:00:00.01 |     98 |        |        |           |
    |* 10 |        INDEX RANGE SCAN         | XXXXXXXXXXXXX_IXX2 |     1 |  14417 |     100 |00:00:00.01 |      4 |        |        |           |
    |  11 |       TABLE ACCESS BY INDEX ROWID| YYYYYYYYYY      |    100 |      1 |       0 |00:00:00.01 |      0 |        |        |           |
    |* 12 |        INDEX UNIQUE SCAN         | YYYYYYYYYY_PK      |    100 |      1 |       0 |00:00:00.01 |      0 |        |        |           |
    |* 13 |    INDEX UNIQUE SCAN              | XXXXXXXXXXXXX_PK      |    100 |      1 |     100 |00:00:00.01 |     202 |        |        |           |
    -----------------------------------------------------------------------------------------------------------------------------------------------
  • 16. Re: UPDATE with ROWNUM (or TOP-N UPDATE)
    CharlesHooper Expert
    Currently Being Moderated
    Narendra,

    It is great that the technique worked for you. Hopefully, the automatic maintenance of the new index will not adversely affect other activities on that table.

    That is a very nice decrease in the number of consistent gets: from 107,230 to 36,381 (with 34,865 of those to determine which rows to update) to 1,616 (with just 98 of those to determine which rows to update).

    The final execution plan showed an index range scan, rather than an index full scan as we saw with the test case. Either way, the idea is that the index full scan or index range scan, because both operations read the index in the order requested by the SQL statement, allow the scan of the index to be short-circuited (aborted) as soon as the required number of rows are passed to the parent operation - by eliminating the UNION ALL from the original query, the rows may be passed to the parent operation immediately.

    A little bit of Oracle Database 11.2.0.2 shooting itself in the foot due to cardinality feedback. The script starts by re-creating my test index that I dropped earlier in this thread, and then executes the modified version of the SQL statement twice:
    CREATE INDEX IND_DTL_TEST ON DTL(DTLID, PROCESS_DATE, PROCESS_IND);
     
    select /*+ GATHER_PLAN_STATISTICS */
      dtlid
    from
      (select
         dtlid
       from
         (select
            dtl.dtlid,
            dtl.process_date
          from
            dtl,
            hdr
          where
            dtl.hdrid = hdr.hdrid(+)
            and dtl.process_date < (sysdate + 30)
            and dtl.process_ind = 'NEW'
            and (
                (hdr_ind in (0, 2) and dtl.hdrid IS NOT NULL)
                OR (dtl.hdrid IS NULL)))
       order by
         dtlid,
         process_date)
    where
      rownum <= 10 ;
     
    SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));
     
    select /*+ GATHER_PLAN_STATISTICS */
      dtlid
    from
      (select
         dtlid
       from
         (select
            dtl.dtlid,
            dtl.process_date
          from
            dtl,
            hdr
          where
            dtl.hdrid = hdr.hdrid(+)
            and dtl.process_date < (sysdate + 30)
            and dtl.process_ind = 'NEW'
            and (
                (hdr_ind in (0, 2) and dtl.hdrid IS NOT NULL)
                OR (dtl.hdrid IS NULL)))
       order by
         dtlid,
         process_date)
    where
      rownum <= 10 ;
      
    SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));
    The first execution plan is the one that I expected to see - notice that the A-Rows column in the execution plan shows the short-circuit of the INDEX FULL SCAN operation, there were 80 consistent gets, and the SQL statement completed in 0.01 (or fewer) seconds:
    SQL_ID  5d8128vjhm1j0, child number 0
    -------------------------------------
    select /*+ GATHER_PLAN_STATISTICS */   dtlid from   (select      dtlid
      from      (select         dtl.dtlid,         dtl.process_date
    from         dtl,         hdr       where         dtl.hdrid =
    hdr.hdrid(+)         and dtl.process_date < (sysdate + 30)         and
    dtl.process_ind = 'NEW'         and (             (hdr_ind in (0, 2)
    and dtl.hdrid IS NOT NULL)             OR (dtl.hdrid IS NULL)))
    order by      dtlid,      process_date) where   rownum <= 10
     
    Plan hash value: 1187173211
     
    ----------------------------------------------------------------------------------------------------------
    | Id  | Operation                       | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
    ----------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                |              |      1 |        |     10 |00:00:00.01 |      80 |
    |*  1 |  COUNT STOPKEY                  |              |      1 |        |     10 |00:00:00.01 |      80 |
    |   2 |   VIEW                          |              |      1 |     11 |     10 |00:00:00.01 |      80 |
    |*  3 |    FILTER                       |              |      1 |        |     10 |00:00:00.01 |      80 |
    |   4 |     NESTED LOOPS OUTER          |              |      1 |     11 |     19 |00:00:00.01 |      80 |
    |   5 |      TABLE ACCESS BY INDEX ROWID| DTL          |      1 |  19635 |     19 |00:00:00.01 |      52 |
    |*  6 |       INDEX FULL SCAN           | IND_DTL_TEST |      1 |     16 |     19 |00:00:00.01 |      49 |
    |   7 |      TABLE ACCESS BY INDEX ROWID| HDR          |     19 |      1 |     18 |00:00:00.01 |      28 |
    |*  8 |       INDEX UNIQUE SCAN         | HDR_PK       |     19 |      1 |     18 |00:00:00.01 |      10 |
    ----------------------------------------------------------------------------------------------------------
     
    Predicate Information (identified by operation id):
    ---------------------------------------------------
       1 - filter(ROWNUM<=10)
       3 - filter(((INTERNAL_FUNCTION("HDR_IND") AND "DTL"."HDRID" IS NOT NULL) OR "DTL"."HDRID" IS
                  NULL))
       6 - access("DTL"."PROCESS_IND"='NEW' AND "DTL"."PROCESS_DATE"<SYSDATE@!+30)
           filter(("DTL"."PROCESS_IND"='NEW' AND "DTL"."PROCESS_DATE"<SYSDATE@!+30))
       8 - access("DTL"."HDRID"="HDR"."HDRID")
    On the second execution, cardinality feedback causes a bit of a problem. The optimizer switched from an index full scan of the IND_DTL_TEST to a full table scan (which in this case does not permit a short-circuit), which then caused 15,115 rows from the DTL table to be passed to the parent nested loops operation, which performed 15,115 probes of the DR_PK index. As a result, the final value in the A-Time column doubled (it could actually be much worse), and the number of consistent gets jumped from 80 to 5,315.
    SQL_ID  5d8128vjhm1j0, child number 1
    -------------------------------------
    select /*+ GATHER_PLAN_STATISTICS */   dtlid from   (select      dtlid
      from      (select         dtl.dtlid,         dtl.process_date
    from         dtl,         hdr       where         dtl.hdrid =
    hdr.hdrid(+)         and dtl.process_date < (sysdate + 30)         and
    dtl.process_ind = 'NEW'         and (             (hdr_ind in (0, 2)
    and dtl.hdrid IS NOT NULL)             OR (dtl.hdrid IS NULL)))
    order by      dtlid,      process_date) where   rownum <= 10
     
    Plan hash value: 672193791
     
    --------------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                        | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
    --------------------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                 |        |      1 |        |     10 |00:00:00.02 |    5315 |       |       |          |
    |*  1 |  COUNT STOPKEY                   |        |      1 |        |     10 |00:00:00.02 |    5315 |       |       |          |
    |   2 |   VIEW                           |        |      1 |      9 |     10 |00:00:00.02 |    5315 |       |       |          |
    |*  3 |    SORT ORDER BY STOPKEY         |        |      1 |      9 |     10 |00:00:00.02 |    5315 |  2048 |  2048 | 2048  (0)|
    |*  4 |     FILTER                       |        |      1 |        |   1374 |00:00:00.02 |    5315 |       |       |          |
    |   5 |      NESTED LOOPS OUTER          |        |      1 |      9 |  15115 |00:00:00.02 |    5315 |       |       |          |
    |*  6 |       TABLE ACCESS FULL          | DTL    |      1 |     19 |  15115 |00:00:00.01 |    1117 |       |       |          |
    |   7 |       TABLE ACCESS BY INDEX ROWID| HDR    |  15115 |      1 |   2853 |00:00:00.01 |    4198 |       |       |          |
    |*  8 |        INDEX UNIQUE SCAN         | HDR_PK |  15115 |      1 |   2853 |00:00:00.01 |    1345 |       |       |          |
    --------------------------------------------------------------------------------------------------------------------------------
     
    Predicate Information (identified by operation id):
    ---------------------------------------------------
       1 - filter(ROWNUM<=10)
       3 - filter(ROWNUM<=10)
       4 - filter(((INTERNAL_FUNCTION("HDR_IND") AND "DTL"."HDRID" IS NOT NULL) OR "DTL"."HDRID" IS NULL))
       6 - filter(("DTL"."PROCESS_IND"='NEW' AND "DTL"."PROCESS_DATE"<SYSDATE@!+30))
       8 - access("DTL"."HDRID"="HDR"."HDRID")
     
    Note
    -----
       - cardinality feedback used for this statement
    I guess that there might be some advantages to the optimizer not having access to cardinality feedback. :-)

    Charles Hooper
    http://hoopercharles.wordpress.com/
    IT Manager/Oracle DBA
    K&M Machine-Fabricating, Inc.
  • 17. Re: UPDATE with ROWNUM (or TOP-N UPDATE)
    user503699 Expert
    Currently Being Moderated
    Charles Hooper wrote:
    Narendra,

    It is great that the technique worked for you. Hopefully, the automatic maintenance of the new index will not adversely affect other activities on that table.
    Charles,

    Actually, during QA, we found that the query with new index did more I/Os than expected/observed earlier (but still way less than current one running in live). Interestingly, the first attempt (that is asked to use existing index) appeared to do less I/Os than the one using new index. I am not sure I understand why that would be the case but my guess is it has something to do with the nature of data.
    To summarise, the short-circuit that I was expecting with new index did not occur. Also, I had to change the query from using an outer join to one that used a subquery. I am not really sure I understand what is going on but then I am not really able to generate test data that simulates the live data.


    With new index:
    -------------------------------------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                  | Name          | Starts | E-Rows | A-Rows |   A-Time     | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
    -------------------------------------------------------------------------------------------------------------------------------------------------------
    |   0 | UPDATE STATEMENT             |               |      1 |       |      0 |00:00:00.40 |   13128 |      1 |        |        |           |
    |   1 |  UPDATE                   | XXXXXXXXXXXXX     |      1 |       |      0 |00:00:00.40 |   13128 |      1 |        |        |           |
    |   2 |   NESTED LOOPS                  |               |      1 |    100 |    100 |00:00:00.40 |   11810 |      1 |        |        |           |
    |   3 |    VIEW                   | VW_NSO_1          |      1 |    100 |    100 |00:00:00.37 |   11772 |      0 |        |        |           |
    |   4 |     SORT UNIQUE              |               |      1 |    100 |    100 |00:00:00.37 |   11772 |      0 | 73728 | 73728 |           |
    |*  5 |      COUNT STOPKEY             |               |      1 |       |    100 |00:00:00.37 |   11772 |      0 |        |        |           |
    |   6 |       VIEW                  |               |      1 |  14417 |    100 |00:00:00.37 |   11772 |      0 |        |        |           |
    |*  7 |        FILTER                  |               |      1 |       |    100 |00:00:00.37 |   11772 |      0 |        |        |           |
    |   8 |      TABLE ACCESS BY INDEX ROWID| XXXXXXXXXXXXX     |      1 |  14417 |    100 |00:00:00.37 |   11766 |      0 |        |        |           |
    |*  9 |       INDEX RANGE SCAN        | XXXXXXXXXXXXX_IXX2 |      1 |  14417 |    100 |00:00:00.37 |   11760 |      0 |        |        |           |
    |* 10 |      TABLE ACCESS BY INDEX ROWID| YYYYYYYYYY      |      2 |     1 |      2 |00:00:00.01 |     6 |      0 |        |        |           |
    |* 11 |       INDEX UNIQUE SCAN        | YYYYYYYYYY_PK     |      2 |     1 |      2 |00:00:00.01 |     4 |      0 |        |        |           |
    |* 12 |    INDEX UNIQUE SCAN             | XXXXXXXXXXXXX_PK     |    100 |     1 |    100 |00:00:00.02 |      38 |      1 |        |        |           |
    -------------------------------------------------------------------------------------------------------------------------------------------------------
    With existing index:
    -----------------------------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                   | Name           | Starts | E-Rows | A-Rows |     A-Time      | Buffers |  OMem |  1Mem | Used-Mem |
    -----------------------------------------------------------------------------------------------------------------------------------------------
    |   0 | UPDATE STATEMENT              |                |     1 |        |       0 |00:00:00.11 |    9539 |        |        |           |
    |   1 |  UPDATE                    | XXXXXXXXXXXXX      |     1 |        |       0 |00:00:00.11 |    9539 |        |        |           |
    |   2 |   NESTED LOOPS                   |                |     1 |    100 |     100 |00:00:00.10 |    8222 |        |        |           |
    |   3 |    VIEW                    | VW_NSO_1           |     1 |    100 |     100 |00:00:00.10 |    8184 |        |        |           |
    |   4 |     SORT UNIQUE               |                |     1 |    100 |     100 |00:00:00.10 |    8184 | 73728 | 73728 |           |
    |*  5 |      COUNT STOPKEY              |                |     1 |        |     100 |00:00:00.10 |    8184 |        |        |           |
    |   6 |       VIEW                   |                |     1 |  14417 |     100 |00:00:00.10 |    8184 |        |        |           |
    |*  7 |        SORT ORDER BY STOPKEY         |                |     1 |  14417 |     100 |00:00:00.10 |    8184 | 11264 | 11264 |10240  (0)|
    |*  8 |      FILTER                   |                |     1 |        |  36000 |00:00:00.08 |    8184 |        |        |           |
    |*  9 |       TABLE ACCESS BY INDEX ROWID| XXXXXXXXXXXXX      |     1 |  14417 |  36000 |00:00:00.06 |    8178 |        |        |           |
    |* 10 |        INDEX RANGE SCAN         | XXXXXXXXXXXXX_IXX1 |     1 |  14418 |  36000 |00:00:00.03 |    6374 |        |        |           |
    |* 11 |       TABLE ACCESS BY INDEX ROWID| YYYYYYYYYY      |     2 |      1 |       2 |00:00:00.01 |      6 |        |        |           |
    |* 12 |        INDEX UNIQUE SCAN         | YYYYYYYYYY_PK      |     2 |      1 |       2 |00:00:00.01 |      4 |        |        |           |
    |* 13 |    INDEX UNIQUE SCAN              | XXXXXXXXXXXXX_PK      |    100 |      1 |     100 |00:00:00.01 |     38 |        |        |           |
    -----------------------------------------------------------------------------------------------------------------------------------------------
  • 18. Re: UPDATE with ROWNUM (or TOP-N UPDATE)
    CharlesHooper Expert
    Currently Being Moderated
    Narendra,

    Based on a little more testing, you may need to add one additional hint so that the new index is used with the outer join syntax:
    /*+ INDEX(HRD) INDEX(DTL) NO_USE_HASH(HDR) NO_USE_MERGE(HDR) */
    Does the value of PROCESS_IND change from NEW to some other value at any point? What about DTLID or PROCESS_DATE - for a row do those values ever change? The execution plan shows that the index range scan of the XXXXXXXXXXXXX_IXX2 index retrieves 100 ROWID values, and none of those ROWID values are eliminated when the table itself is accessed - this is usually a good sign because it means that the index is selective for the SQL statement. The problem is that 11,760 consistent gets were performed when trying to find 100 matching values in the index - that possibly suggests that the third column in the index is disqualifying a lot of rows. You might try swapping the order of the third column in the index definition to either the first or second position in the index definition to see if the column position makes a difference (it might not).

    The unhinted version of the query without the UNION ALL produces an efficient execution plan under Oracle Database 11.2.0.2 on the first execution.
    DROP INDEX IND_DTL_TEST;
    CREATE INDEX IND_DTL_TEST ON DTL(DTLID,PROCESS_IND,PROCESS_DATE);
     
    select /*+ GATHER_PLAN_STATISTICS */
      dtlid
    from
      (select
         dtlid
       from
         (select
            dtl.dtlid,
            dtl.process_date
          from
            dtl,
            hdr
          where
            dtl.hdrid = hdr.hdrid(+)
            and dtl.process_date < (sysdate + 30)
            and dtl.process_ind = 'NEW'
            and (
                (hdr_ind in (0, 2) and dtl.hdrid IS NOT NULL)
                OR (dtl.hdrid IS NULL)))
       order by
         dtlid,
         process_date)
    where
      rownum <= 10 ;
     
    SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));
    The execution plan for the first execution:
    SQL_ID  5d8128vjhm1j0, child number 1
    -------------------------------------
    select /*+ GATHER_PLAN_STATISTICS */   dtlid from   (select      dtlid
      from      (select         dtl.dtlid,         dtl.process_date
    from         dtl,         hdr       where         dtl.hdrid =
    hdr.hdrid(+)         and dtl.process_date < (sysdate + 30)         and
    dtl.process_ind = 'NEW'         and (             (hdr_ind in (0, 2)
    and dtl.hdrid IS NOT NULL)             OR (dtl.hdrid IS NULL)))
    order by      dtlid,      process_date) where   rownum <= 10
     
    Plan hash value: 1187173211
     
    -------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                       | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
    -------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                |              |      1 |        |     10 |00:00:00.01 |      80 |     49 |
    |*  1 |  COUNT STOPKEY                  |              |      1 |        |     10 |00:00:00.01 |      80 |     49 |
    |   2 |   VIEW                          |              |      1 |     11 |     10 |00:00:00.01 |      80 |     49 |
    |*  3 |    FILTER                       |              |      1 |        |     10 |00:00:00.01 |      80 |     49 |
    |   4 |     NESTED LOOPS OUTER          |              |      1 |     11 |     19 |00:00:00.01 |      80 |     49 |
    |   5 |      TABLE ACCESS BY INDEX ROWID| DTL          |      1 |  19656 |     19 |00:00:00.01 |      52 |     49 |
    |*  6 |       INDEX FULL SCAN           | IND_DTL_TEST |      1 |     16 |     19 |00:00:00.01 |      49 |     49 |
    |   7 |      TABLE ACCESS BY INDEX ROWID| HDR          |     19 |      1 |     18 |00:00:00.01 |      28 |      0 |
    |*  8 |       INDEX UNIQUE SCAN         | HDR_PK       |     19 |      1 |     18 |00:00:00.01 |      10 |      0 |
    -------------------------------------------------------------------------------------------------------------------
     
    Predicate Information (identified by operation id):
    ---------------------------------------------------
       1 - filter(ROWNUM<=10)
       3 - filter(((INTERNAL_FUNCTION("HDR_IND") AND "DTL"."HDRID" IS NOT NULL) OR "DTL"."HDRID" IS NULL))
       6 - access("DTL"."PROCESS_IND"='NEW' AND "DTL"."PROCESS_DATE"<SYSDATE@!+30)
           filter(("DTL"."PROCESS_IND"='NEW' AND "DTL"."PROCESS_DATE"<SYSDATE@!+30))
       8 - access("DTL"."HDRID"="HDR"."HDRID")
    Cardinality feedback in 11.2.0.2 causes the plan to change on the second execution - this may be the execution plan that you saw on 10.2.0.5:
    SQL_ID  5d8128vjhm1j0, child number 2
    -------------------------------------
    select /*+ GATHER_PLAN_STATISTICS */   dtlid from   (select      dtlid
      from      (select         dtl.dtlid,         dtl.process_date
    from         dtl,         hdr       where         dtl.hdrid =
    hdr.hdrid(+)         and dtl.process_date < (sysdate + 30)         and
    dtl.process_ind = 'NEW'         and (             (hdr_ind in (0, 2)
    and dtl.hdrid IS NOT NULL)             OR (dtl.hdrid IS NULL)))
    order by      dtlid,      process_date) where   rownum <= 10
     
    Plan hash value: 672193791
     
    --------------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                        | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
    --------------------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                 |        |      1 |        |     10 |00:00:00.02 |    5335 |       |       |          |
    |*  1 |  COUNT STOPKEY                   |        |      1 |        |     10 |00:00:00.02 |    5335 |       |       |          |
    |   2 |   VIEW                           |        |      1 |      9 |     10 |00:00:00.02 |    5335 |       |       |          |
    |*  3 |    SORT ORDER BY STOPKEY         |        |      1 |      9 |     10 |00:00:00.02 |    5335 |  2048 |  2048 | 2048  (0)|
    |*  4 |     FILTER                       |        |      1 |        |   1383 |00:00:00.01 |    5335 |       |       |          |
    |   5 |      NESTED LOOPS OUTER          |        |      1 |      9 |  15186 |00:00:00.03 |    5335 |       |       |          |
    |*  6 |       TABLE ACCESS FULL          | DTL    |      1 |     19 |  15186 |00:00:00.01 |    1117 |       |       |          |
    |   7 |       TABLE ACCESS BY INDEX ROWID| HDR    |  15186 |      1 |   2873 |00:00:00.01 |    4218 |       |       |          |
    |*  8 |        INDEX UNIQUE SCAN         | HDR_PK |  15186 |      1 |   2873 |00:00:00.01 |    1345 |       |       |          |
    --------------------------------------------------------------------------------------------------------------------------------
     
    Predicate Information (identified by operation id):
    ---------------------------------------------------
       1 - filter(ROWNUM<=10)
       3 - filter(ROWNUM<=10)
       4 - filter(((INTERNAL_FUNCTION("HDR_IND") AND "DTL"."HDRID" IS NOT NULL) OR "DTL"."HDRID" IS NULL))
       6 - filter(("DTL"."PROCESS_IND"='NEW' AND "DTL"."PROCESS_DATE"<SYSDATE@!+30))
       8 - access("DTL"."HDRID"="HDR"."HDRID")
     
    Note
    -----
       - cardinality feedback used for this statement
    As seen above the optimizer switched to using a full table scan of table DTL - the access path for that table was not hinted in any of my previous examples in this thread.

    Hinting the access path for table DTL:
    select /*+ GATHER_PLAN_STATISTICS */
      dtlid
    from
      (select
         dtlid
       from
         (select /*+ INDEX(HRD) INDEX(DTL) NO_USE_HASH(HDR) NO_USE_MERGE(HDR) */
            dtl.dtlid,
            dtl.process_date
          from
            dtl,
            hdr
          where
            dtl.hdrid = hdr.hdrid(+)
            and dtl.process_date < (sysdate + 30)
            and dtl.process_ind = 'NEW'
            and (
                (hdr_ind in (0, 2) and dtl.hdrid IS NOT NULL)
                OR (dtl.hdrid IS NULL)))
       order by
         dtlid,
         process_date)
    where
      rownum <= 10 ;
     
    SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));
    With the extra hint in place, cardinality feedback no longer alters the execution plan on 11.2.0.2, and the optimizer would hopefully generate the same execution plan on 10.2.0.5:
    SQL_ID  2j1tvatn9vhc4, child number 0
    -------------------------------------
    select /*+ GATHER_PLAN_STATISTICS */   dtlid from   (select      dtlid
      from      (select /*+ INDEX(HRD) INDEX(DTL) NO_USE_HASH(HDR)
    NO_USE_MERGE(HDR) */         dtl.dtlid,         dtl.process_date
    from         dtl,         hdr       where         dtl.hdrid =
    hdr.hdrid(+)         and dtl.process_date < (sysdate + 30)         and
    dtl.process_ind = 'NEW'         and (             (hdr_ind in (0, 2)
    and dtl.hdrid IS NOT NULL)             OR (dtl.hdrid IS NULL)))
    order by      dtlid,      process_date) where   rownum <= 10
     
    Plan hash value: 1187173211
     
    ----------------------------------------------------------------------------------------------------------
    | Id  | Operation                       | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
    ----------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                |              |      1 |        |     10 |00:00:00.01 |      80 |
    |*  1 |  COUNT STOPKEY                  |              |      1 |        |     10 |00:00:00.01 |      80 |
    |   2 |   VIEW                          |              |      1 |     11 |     10 |00:00:00.01 |      80 |
    |*  3 |    FILTER                       |              |      1 |        |     10 |00:00:00.01 |      80 |
    |   4 |     NESTED LOOPS OUTER          |              |      1 |     11 |     19 |00:00:00.01 |      80 |
    |   5 |      TABLE ACCESS BY INDEX ROWID| DTL          |      1 |     17 |     19 |00:00:00.01 |      52 |
    |*  6 |       INDEX FULL SCAN           | IND_DTL_TEST |      1 |  19658 |     19 |00:00:00.01 |      49 |
    |   7 |      TABLE ACCESS BY INDEX ROWID| HDR          |     19 |      1 |     18 |00:00:00.01 |      28 |
    |*  8 |       INDEX UNIQUE SCAN         | HDR_PK       |     19 |      1 |     18 |00:00:00.01 |      10 |
    ----------------------------------------------------------------------------------------------------------
     
    Predicate Information (identified by operation id):
    ---------------------------------------------------
       1 - filter(ROWNUM<=10)
       3 - filter(((INTERNAL_FUNCTION("HDR_IND") AND "DTL"."HDRID" IS NOT NULL) OR "DTL"."HDRID" IS
                  NULL))
       6 - access("DTL"."PROCESS_IND"='NEW' AND "DTL"."PROCESS_DATE"<SYSDATE@!+30)
           filter(("DTL"."PROCESS_IND"='NEW' AND "DTL"."PROCESS_DATE"<SYSDATE@!+30))
       8 - access("DTL"."HDRID"="HDR"."HDRID")
    Charles Hooper
    http://hoopercharles.wordpress.com/
    IT Manager/Oracle DBA
    K&M Machine-Fabricating, Inc.
  • 19. Re: UPDATE with ROWNUM (or TOP-N UPDATE)
    user503699 Expert
    Currently Being Moderated
    Charles Hooper wrote:
    Narendra,

    Based on a little more testing, you may need to add one additional hint so that the new index is used with the outer join syntax:
    /*+ INDEX(HRD) INDEX(DTL) NO_USE_HASH(HDR) NO_USE_MERGE(HDR) */
    Does the value of PROCESS_IND change from NEW to some other value at any point? What about DTLID or PROCESS_DATE - for a row do those values ever change? The execution plan shows that the index range scan of the XXXXXXXXXXXXX_IXX2 index retrieves 100 ROWID values, and none of those ROWID values are eliminated when the table itself is accessed - this is usually a good sign because it means that the index is selective for the SQL statement. The problem is that 11,760 consistent gets were performed when trying to find 100 matching values in the index - that possibly suggests that the third column in the index is disqualifying a lot of rows. You might try swapping the order of the third column in the index definition to either the first or second position in the index definition to see if the column position makes a difference (it might not).
    Charles,

    The value of PROCESS_IND is changed by the UPDATE statement itself (to PROCESSING). The value of PROCESS_DATE does not change.
    DTLID column in my test case is just a simple sequential number whereas in the original query there is one column in the WHERE clause with an equality condition and another column, embedded in the DECODE expression, in the ORDER BY clause. That is why I mentioned in my earlier post that I might find it difficult to come up with new index that helps this UPDATE.
    It appears that the short-circuiting that you mentioned earlier (and which I am trying to achieve) only takes place with a NESTED LOOP join operation that is driven by an indexed access to the driving table. Is that the way things work?
    After I posted my "surprise" findings earlier, I looked again and I could see that the ONLY plan that did the least amount of I/O used NESTED LOOP join and the plans that I posted in my last post only use a FILTER operation(is it because I am now using a IN subquery?) . I am guessing FILTER operation is not benefiting from short-circuit ??
    I will test out your suggestions later and post my findings.

    Appreciate your help.
  • 20. Re: UPDATE with ROWNUM (or TOP-N UPDATE)
    user503699 Expert
    Currently Being Moderated
    Charles Hooper wrote:
    Narendra,

    Based on a little more testing, you may need to add one additional hint so that the new index is used with the outer join syntax:
    /*+ INDEX(HRD) INDEX(DTL) NO_USE_HASH(HDR) NO_USE_MERGE(HDR) */
    Charles,

    Just a follow-up. The additional hint did not make any difference (I must admit I was not sure how hint for HDR table will change anything as the revised plan is already accessing HDR table with INDEX UNIQUE SCAN on its PK index).
    I am running out of time (not allowed to spend more time on this... :( ) but I am absolutely confused as why would there be such dramatic difference in I/Os when the execution plan and number of rows processed remain same. I can confirm that there has not been any DDL operation on the problem table/indexes neither the data pattern has been too different.
    But I am not able to reproduce the results that I got when I first tested your idea of using new index.
    First test of new index:
    -----------------------------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                   | Name           | Starts | E-Rows | A-Rows |     A-Time      | Buffers |  OMem |  1Mem | Used-Mem |
    -----------------------------------------------------------------------------------------------------------------------------------------------
    |   0 | UPDATE STATEMENT              |                |     1 |        |       0 |00:00:00.01 |    1616 |        |        |           |
    |   1 |  UPDATE                    | XXXXXXXXXXXXX      |     1 |        |       0 |00:00:00.01 |    1616 |        |        |           |
    |   2 |   NESTED LOOPS                   |                |     1 |    100 |     100 |00:00:00.01 |     300 |        |        |           |
    |   3 |    VIEW                    | VW_NSO_1           |     1 |    100 |     100 |00:00:00.01 |     98 |        |        |           |
    |   4 |     SORT UNIQUE               |                |     1 |    100 |     100 |00:00:00.01 |     98 | 73728 | 73728 |           |
    |*  5 |      COUNT STOPKEY              |                |     1 |        |     100 |00:00:00.01 |     98 |        |        |           |
    |   6 |       VIEW                   |                |     1 |  14417 |     100 |00:00:00.01 |     98 |        |        |           |
    |*  7 |        FILTER                   |                |     1 |        |     100 |00:00:00.01 |     98 |        |        |           |
    |   8 |      NESTED LOOPS OUTER         |                |     1 |  14417 |     100 |00:00:00.01 |     98 |        |        |           |
    |   9 |       TABLE ACCESS BY INDEX ROWID| XXXXXXXXXXXXX      |     1 |  14417 |     100 |00:00:00.01 |     98 |        |        |           |
    |* 10 |        INDEX RANGE SCAN         | XXXXXXXXXXXXX_IXX2 |     1 |  14417 |     100 |00:00:00.01 |      4 |        |        |           |
    |  11 |       TABLE ACCESS BY INDEX ROWID| YYYYYYYYYY      |    100 |      1 |       0 |00:00:00.01 |      0 |        |        |           |
    |* 12 |        INDEX UNIQUE SCAN         | YYYYYYYYYY_PK      |    100 |      1 |       0 |00:00:00.01 |      0 |        |        |           |
    |* 13 |    INDEX UNIQUE SCAN              | XXXXXXXXXXXXX_PK      |    100 |      1 |     100 |00:00:00.01 |     202 |        |        |           |
    -----------------------------------------------------------------------------------------------------------------------------------------------
    Subsequent tests with new index:
    --------------------------------------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                   | Name           | Starts | E-Rows | A-Rows |     A-Time      | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
    --------------------------------------------------------------------------------------------------------------------------------------------------------
    |   0 | UPDATE STATEMENT              |                |     1 |        |       0 |00:00:00.39 |   10384 |       1 |         |         |            |
    |   1 |  UPDATE                    | XXXXXXXXXXXXX      |     1 |        |       0 |00:00:00.39 |   10384 |       1 |         |         |            |
    |   2 |   NESTED LOOPS                   |                |     1 |    100 |     100 |00:00:00.38 |    9070 |       1 |         |         |            |
    |   3 |    VIEW                    | VW_NSO_1           |     1 |    100 |     100 |00:00:00.37 |    9032 |       0 |         |         |            |
    |   4 |     SORT UNIQUE               |                |     1 |    100 |     100 |00:00:00.37 |    9032 |       0 | 73728 | 73728 |            |
    |*  5 |      COUNT STOPKEY              |                |     1 |        |     100 |00:00:00.37 |    9032 |       0 |         |         |            |
    |   6 |       VIEW                   |                |     1 |  14417 |     100 |00:00:00.37 |    9032 |       0 |         |         |            |
    |*  7 |        FILTER                   |                |     1 |        |     100 |00:00:00.37 |    9032 |       0 |         |         |            |
    |   8 |      NESTED LOOPS OUTER         |                |     1 |  14417 |     100 |00:00:00.37 |    9032 |       0 |         |         |            |
    |   9 |       TABLE ACCESS BY INDEX ROWID| XXXXXXXXXXXXX      |     1 |  14417 |     100 |00:00:00.37 |    8923 |       0 |         |         |            |
    |* 10 |        INDEX RANGE SCAN         | XXXXXXXXXXXXX_IXX2 |     1 |  14417 |     100 |00:00:00.37 |    8916 |       0 |         |         |            |
    |  11 |       TABLE ACCESS BY INDEX ROWID| YYYYYYYYYY      |    100 |      1 |     100 |00:00:00.01 |     109 |       0 |         |         |            |
    |* 12 |        INDEX UNIQUE SCAN         | YYYYYYYYYY_PK      |    100 |      1 |     100 |00:00:00.01 |      9 |       0 |         |         |            |
    |* 13 |    INDEX UNIQUE SCAN              | XXXXXXXXXXXXX_PK      |    100 |      1 |     100 |00:00:00.01 |     38 |       1 |         |         |            |
    --------------------------------------------------------------------------------------------------------------------------------------------------------
  • 21. Re: UPDATE with ROWNUM (or TOP-N UPDATE)
    CharlesHooper Expert
    Currently Being Moderated
    Narendra,
    user503699 wrote:
    Charles Hooper wrote:
    Based on a little more testing, you may need to add one additional hint so that the new index is used with the outer join syntax:
    /*+ INDEX(HRD) INDEX(DTL) NO_USE_HASH(HDR) NO_USE_MERGE(HDR) */
    Charles,

    Just a follow-up. The additional hint did not make any difference (I must admit I was not sure how hint for HDR table will change anything as the revised plan is already accessing HDR table with INDEX UNIQUE SCAN on its PK index).
    Sorry for the confusion regarding the hint. Your test case included the following SQL statement:
    select
      dtlid
    from
      (select
         dtlid
       from
         (select
            dtlid,
            process_date
          from
            dtl
          where
            hdrid is null
            and process_date < (sysdate + 30)
            and process_ind = 'NEW'
          UNION ALL
          select
            dtl.dtlid,
            dtl.process_date
          from
            dtl,
            hdr
          where
            dtl.hdrid = hdr.hdrid
            and dtl.process_date < (sysdate + 30)
            and dtl.process_ind = 'NEW'
            and hdr_ind in (0, 2))
       order by
         dtlid,
         process_date)
    where
      rownum <= 10 ;
    The UNION ALL in the iinline view makes it impossible for the runtime engine to short-circuit the operations in the inline view, so I suggested testing whether or not the following change would produce the same result:
    select
      dtlid
    from
      (select
         dtlid
       from
         (select
            dtl.dtlid,
            dtl.process_date
          from
            dtl,
            hdr
          where
            dtl.hdrid = hdr.hdrid(+)
            and dtl.process_date < (sysdate + 30)
            and dtl.process_ind = 'NEW'
            and (
                (hdr_ind in (0, 2) and dtl.hdrid IS NOT NULL)
                OR (dtl.hdrid IS NULL)))
       order by
         dtlid,
         process_date)
    where
      rownum <= 10 ;
    I then suggested creating the following index to further test, because if the index were used, the ORDER BY clause could be silently ignored, and the runtime engine could short-circuit the nested loop operation (meaning that the INDEX FULL SCAN operation would not actually read every leaf block in the index):
    CREATE INDEX IND_DTL_TEST ON DTL(DTLID, PROCESS_DATE, PROCESS_IND);
    I then suggested that you try testing the following SQL statement:
    update dtl set process_ind = 'PROCESSED' where dtlid in (
    select
      dtlid
    from
      (select
         dtlid
       from
         (select /*+ INDEX(DTL) INDEX(HRD) NO_USE_HASH(HDR) NO_USE_MERGE(HDR) */
            dtl.dtlid,
            dtl.process_date
          from
            dtl,
            hdr
          where
            dtl.hdrid = hdr.hdrid(+)
            and dtl.process_date < (sysdate + 30)
            and dtl.process_ind = 'NEW'
            and (
                (hdr_ind in (0, 2) and dtl.hdrid IS NOT NULL)
                OR (dtl.hdrid IS NULL)))
       order by
         dtlid,
         process_date)
    where
      rownum <= 10);
    For some reason, i thought that I forgot to include the INDEX(DTL) hint (I misread my local copy of the OTN thread), and that is why I thought that you still needed to add that hint to the SQL statement. Sorry for the confusion.
    I am running out of time (not allowed to spend more time on this... :( ) but I am absolutely confused as why would there be such dramatic difference in I/Os when the execution plan and number of rows processed remain same. I can confirm that there has not been any DDL operation on the problem table/indexes neither the data pattern has been too different.
    Unfortunately, I did not realize that the value of the PROCESS_IND column is subject to change from the value of NEW to another value. What is probably happening is that the index structure has many rows with a value other than NEW, so it takes an increasingly longer time for the runtime engine to navigate through the leaf blocks to find the first 10 (or 100) rows with a PROCESS_DATE less than (SYSDATE + 30) and a PROCESS_IND value of NEW.

    Looking back, I see that the test case created the following index, which includes 2 of the three columns that I specified:
    create index dtl_idx1 on dtl(process_ind, process_date) nologging ;
    We could try a test to combine these two indexes:
    DROP INDEX IND_DTL_TEST;
    CREATE INDEX IND_DTL_TEST ON DTL(PROCESS_IND, DTLID, PROCESS_DATE);
    The test case script follows:
    update /*+ GATHER_PLAN_STATISTICS */ dtl set process_ind = 'PROCESSED' where dtlid in (
    select 
      dtlid
    from
      (select
         dtlid
       from
         (select /*+ INDEX(DTL) INDEX(HRD) NO_USE_HASH(HDR) NO_USE_MERGE(HDR) */
            dtl.dtlid,
            dtl.process_date
          from
            dtl,
            hdr
          where
            dtl.hdrid = hdr.hdrid(+)
            and dtl.process_date < (sysdate + 30)
            and dtl.process_ind = 'NEW'
            and (
                (hdr_ind in (0, 2) and dtl.hdrid IS NOT NULL)
                OR (dtl.hdrid IS NULL)))
       order by
         dtlid,
         process_date)
    where
      rownum <= 10);
     
    SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));
    On the first execution, the following execution plan displayed:
    SQL_ID  2d8gbxmgpq5d8, child number 0
    -------------------------------------
    update /*+ GATHER_PLAN_STATISTICS */ dtl set process_ind = 'PROCESSED'
    where dtlid in ( select   dtlid from   (select      dtlid    from
    (select /*+ INDEX(DTL) INDEX(HRD) NO_USE_HASH(HDR) NO_USE_MERGE(HDR) */
            dtl.dtlid,         dtl.process_date       from         dtl,
        hdr       where         dtl.hdrid = hdr.hdrid(+)         and
    dtl.process_date < (sysdate + 30)         and dtl.process_ind = 'NEW'
          and (             (hdr_ind in (0, 2) and dtl.hdrid IS NOT NULL)
              OR (dtl.hdrid IS NULL)))    order by      dtlid,
    process_date) where   rownum <= 10)
     
    Plan hash value: 567960041
     
    --------------------------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                           | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem    |  1Mem | Used-Mem |
    --------------------------------------------------------------------------------------------------------------------------------------------
    |   0 | UPDATE STATEMENT                    |              |      1 |        |      0 |00:00:00.01 |     134 |          |       |          |
    |   1 |  UPDATE                             | DTL          |      1 |        |      0 |00:00:00.01 |     134 |          |       |          |
    |   2 |   NESTED LOOPS                      |              |      1 |     10 |     10 |00:00:00.01 |      40 |          |       |          |
    |   3 |    VIEW                             | VW_NSO_1     |      1 |     10 |     10 |00:00:00.01 |      31 |          |       |          |
    |   4 |     SORT UNIQUE                     |              |      1 |     10 |     10 |00:00:00.01 |      31 |    73728 | 73728 |          |
    |*  5 |      COUNT STOPKEY                  |              |      1 |        |     10 |00:00:00.01 |      31 |          |       |          |
    |   6 |       VIEW                          |              |      1 |  13119 |     10 |00:00:00.01 |      31 |          |       |          |
    |*  7 |        FILTER                       |              |      1 |        |     10 |00:00:00.01 |      31 |          |       |          |
    |   8 |         NESTED LOOPS OUTER          |              |      1 |  13119 |     19 |00:00:00.01 |      31 |          |       |          |
    |   9 |          TABLE ACCESS BY INDEX ROWID| DTL          |      1 |  19775 |     19 |00:00:00.01 |       4 |          |       |          |
    |* 10 |           INDEX RANGE SCAN          | IND_DTL_TEST |      1 |  19775 |     19 |00:00:00.01 |       2 |          |       |          |
    |  11 |          TABLE ACCESS BY INDEX ROWID| HDR          |     19 |      1 |     18 |00:00:00.01 |      27 |          |       |          |
    |* 12 |           INDEX UNIQUE SCAN         | HDR_PK       |     19 |      1 |     18 |00:00:00.01 |       9 |          |       |          |
    |* 13 |    INDEX UNIQUE SCAN                | DTL_PK       |     10 |      1 |     10 |00:00:00.01 |       9 |          |       |          |
    --------------------------------------------------------------------------------------------------------------------------------------------
     
    Predicate Information (identified by operation id):
    ---------------------------------------------------
       5 - filter(ROWNUM<=10)
       7 - filter(((INTERNAL_FUNCTION("HDR_IND") AND "DTL"."HDRID" IS NOT NULL) OR "DTL"."HDRID" IS NULL))
      10 - access("DTL"."PROCESS_IND"='NEW' AND "DTL"."PROCESS_DATE"<SYSDATE@!+30)
           filter("DTL"."PROCESS_DATE"<SYSDATE@!+30)
      12 - access("DTL"."HDRID"="HDR"."HDRID")
      13 - access("DTLID"="DTLID")
    After about 20 executions (without a ROLLBACK) I received the following execution plan:
    SQL_ID  2d8gbxmgpq5d8, child number 0
    -------------------------------------
    update /*+ GATHER_PLAN_STATISTICS */ dtl set process_ind = 'PROCESSED'
    where dtlid in ( select   dtlid from   (select      dtlid    from
    (select /*+ INDEX(DTL) INDEX(HRD) NO_USE_HASH(HDR) NO_USE_MERGE(HDR) */
            dtl.dtlid,         dtl.process_date       from         dtl,
        hdr       where         dtl.hdrid = hdr.hdrid(+)         and
    dtl.process_date < (sysdate + 30)         and dtl.process_ind = 'NEW'
          and (             (hdr_ind in (0, 2) and dtl.hdrid IS NOT NULL)
              OR (dtl.hdrid IS NULL)))    order by      dtlid,
    process_date) where   rownum <= 10)
     
    Plan hash value: 567960041
     
    --------------------------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                           | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem    |  1Mem | Used-Mem |
    --------------------------------------------------------------------------------------------------------------------------------------------
    |   0 | UPDATE STATEMENT                    |              |      1 |        |      0 |00:00:00.01 |     459 |          |       |          |
    |   1 |  UPDATE                             | DTL          |      1 |        |      0 |00:00:00.01 |     459 |          |       |          |
    |   2 |   NESTED LOOPS                      |              |      1 |     10 |     10 |00:00:00.01 |     347 |          |       |          |
    |   3 |    VIEW                             | VW_NSO_1     |      1 |     10 |     10 |00:00:00.01 |     338 |          |       |          |
    |   4 |     SORT UNIQUE                     |              |      1 |     10 |     10 |00:00:00.01 |     338 |    73728 | 73728 |          |
    |*  5 |      COUNT STOPKEY                  |              |      1 |        |     10 |00:00:00.01 |     338 |          |       |          |
    |   6 |       VIEW                          |              |      1 |  13119 |     10 |00:00:00.01 |     338 |          |       |          |
    |*  7 |        FILTER                       |              |      1 |        |     10 |00:00:00.01 |     338 |          |       |          |
    |   8 |         NESTED LOOPS OUTER          |              |      1 |  13119 |    280 |00:00:00.01 |     338 |          |       |          |
    |   9 |          TABLE ACCESS BY INDEX ROWID| DTL          |      1 |  19775 |    280 |00:00:00.01 |      36 |          |       |          |
    |* 10 |           INDEX RANGE SCAN          | IND_DTL_TEST |      1 |  19775 |    280 |00:00:00.01 |       8 |          |       |          |
    |  11 |          TABLE ACCESS BY INDEX ROWID| HDR          |    280 |      1 |    279 |00:00:00.01 |     302 |          |       |          |
    |* 12 |           INDEX UNIQUE SCAN         | HDR_PK       |    280 |      1 |    279 |00:00:00.01 |      23 |          |       |          |
    |* 13 |    INDEX UNIQUE SCAN                | DTL_PK       |     10 |      1 |     10 |00:00:00.01 |       9 |          |       |          |
    --------------------------------------------------------------------------------------------------------------------------------------------
     
    Predicate Information (identified by operation id):
    ---------------------------------------------------
       5 - filter(ROWNUM<=10)
       7 - filter(((INTERNAL_FUNCTION("HDR_IND") AND "DTL"."HDRID" IS NOT NULL) OR "DTL"."HDRID" IS NULL))
      10 - access("DTL"."PROCESS_IND"='NEW' AND "DTL"."PROCESS_DATE"<SYSDATE@!+30)
           filter("DTL"."PROCESS_DATE"<SYSDATE@!+30)
      12 - access("DTL"."HDRID"="HDR"."HDRID")
      13 - access("DTLID"="DTLID")
    The number of consistent gets for the execution jumped from 134 to 459 - so that is a significant jump. In the first execution, the IND_DTL_TEST index was responsible for 2 of the consistent gets, while the DTL table was responsible for an additional 2 consistent gets (2+2 = 4). After the 20th execution, we see that the IND_DTL_TEST index was responsible for 8 of the 459 consistent gets, with the DTL table being responsible for 28 (28+8 = 36), with the index and table returning 280 rows (up from 19). The increasing number of consistent gets is due to the increasing number of accesses to the HDR_PK index (based on the number of rows returned from the DTL table). The number of rows only drops back to 10 once the HDR_IND value is checked to determine if it is either 0 or 2, and whether or not the HDRID value IS NULL. That step likely needs to be optimized - maybe someone else can suggest an improvement (I do not know whether or not it is wise to add these two columns to the new index - HDR_IND is in the HDR table).
    But I am not able to reproduce the results that I got when I first tested your idea of using new index.
    First test of new index:
    -----------------------------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                   | Name           | Starts | E-Rows | A-Rows |     A-Time      | Buffers |  OMem |  1Mem | Used-Mem |
    -----------------------------------------------------------------------------------------------------------------------------------------------
    |   0 | UPDATE STATEMENT              |                |     1 |        |       0 |00:00:00.01 |    1616 |        |        |           |
    |   1 |  UPDATE                    | XXXXXXXXXXXXX      |     1 |        |       0 |00:00:00.01 |    1616 |        |        |           |
    |   2 |   NESTED LOOPS                   |                |     1 |    100 |     100 |00:00:00.01 |     300 |        |        |           |
    |   3 |    VIEW                    | VW_NSO_1           |     1 |    100 |     100 |00:00:00.01 |     98 |        |        |           |
    |   4 |     SORT UNIQUE               |                |     1 |    100 |     100 |00:00:00.01 |     98 | 73728 | 73728 |           |
    |*  5 |      COUNT STOPKEY              |                |     1 |        |     100 |00:00:00.01 |     98 |        |        |           |
    |   6 |       VIEW                   |                |     1 |  14417 |     100 |00:00:00.01 |     98 |        |        |           |
    |*  7 |        FILTER                   |                |     1 |        |     100 |00:00:00.01 |     98 |        |        |           |
    |   8 |      NESTED LOOPS OUTER         |                |     1 |  14417 |     100 |00:00:00.01 |     98 |        |        |           |
    |   9 |       TABLE ACCESS BY INDEX ROWID| XXXXXXXXXXXXX      |     1 |  14417 |     100 |00:00:00.01 |     98 |        |        |           |
    |* 10 |        INDEX RANGE SCAN         | XXXXXXXXXXXXX_IXX2 |     1 |  14417 |     100 |00:00:00.01 |      4 |        |        |           |
    |  11 |       TABLE ACCESS BY INDEX ROWID| YYYYYYYYYY      |    100 |      1 |       0 |00:00:00.01 |      0 |        |        |           |
    |* 12 |        INDEX UNIQUE SCAN         | YYYYYYYYYY_PK      |    100 |      1 |       0 |00:00:00.01 |      0 |        |        |           |
    |* 13 |    INDEX UNIQUE SCAN              | XXXXXXXXXXXXX_PK      |    100 |      1 |     100 |00:00:00.01 |     202 |        |        |           |
    -----------------------------------------------------------------------------------------------------------------------------------------------
    Subsequent tests with new index:
    --------------------------------------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                   | Name           | Starts | E-Rows | A-Rows |     A-Time      | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
    --------------------------------------------------------------------------------------------------------------------------------------------------------
    |   0 | UPDATE STATEMENT              |                |     1 |        |       0 |00:00:00.39 |   10384 |       1 |         |         |            |
    |   1 |  UPDATE                    | XXXXXXXXXXXXX      |     1 |        |       0 |00:00:00.39 |   10384 |       1 |         |         |            |
    |   2 |   NESTED LOOPS                   |                |     1 |    100 |     100 |00:00:00.38 |    9070 |       1 |         |         |            |
    |   3 |    VIEW                    | VW_NSO_1           |     1 |    100 |     100 |00:00:00.37 |    9032 |       0 |         |         |            |
    |   4 |     SORT UNIQUE               |                |     1 |    100 |     100 |00:00:00.37 |    9032 |       0 | 73728 | 73728 |            |
    |*  5 |      COUNT STOPKEY              |                |     1 |        |     100 |00:00:00.37 |    9032 |       0 |         |         |            |
    |   6 |       VIEW                   |                |     1 |  14417 |     100 |00:00:00.37 |    9032 |       0 |         |         |            |
    |*  7 |        FILTER                   |                |     1 |        |     100 |00:00:00.37 |    9032 |       0 |         |         |            |
    |   8 |      NESTED LOOPS OUTER         |                |     1 |  14417 |     100 |00:00:00.37 |    9032 |       0 |         |         |            |
    |   9 |       TABLE ACCESS BY INDEX ROWID| XXXXXXXXXXXXX      |     1 |  14417 |     100 |00:00:00.37 |    8923 |       0 |         |         |            |
    |* 10 |        INDEX RANGE SCAN         | XXXXXXXXXXXXX_IXX2 |     1 |  14417 |     100 |00:00:00.37 |    8916 |       0 |         |         |            |
    |  11 |       TABLE ACCESS BY INDEX ROWID| YYYYYYYYYY      |    100 |      1 |     100 |00:00:00.01 |     109 |       0 |         |         |            |
    |* 12 |        INDEX UNIQUE SCAN         | YYYYYYYYYY_PK      |    100 |      1 |     100 |00:00:00.01 |      9 |       0 |         |         |            |
    |* 13 |    INDEX UNIQUE SCAN              | XXXXXXXXXXXXX_PK      |    100 |      1 |     100 |00:00:00.01 |     38 |       1 |         |         |            |
    --------------------------------------------------------------------------------------------------------------------------------------------------------
    In the above, notice that for the first execution plan that there were 100 starts for the YYYYYYYYYY_PK index, but no consistent gets. In the second execution plan there are also 100 starts with 9 consistent gets for the index access and 100 for the YYYYYYYYYY table - so, unlike the test case, the increase in the number of consistent gets is much less driven by the increased access of the YYYYYYYYYY table (I am guessing that this is the HDR table in your test case). The majority of the increase in the consistent gets are from the XXXXXXXXXXXXX_IXX2 index. Is there any positive change by making the PROCESS_IND column the leading column in that index?

    Charles Hooper
    http://hoopercharles.wordpress.com/
    IT Manager/Oracle DBA
    K&M Machine-Fabricating, Inc.
  • 22. Re: UPDATE with ROWNUM (or TOP-N UPDATE)
    user503699 Expert
    Currently Being Moderated
    Charles Hooper wrote:
    We could try a test to combine these two indexes:
    DROP INDEX IND_DTL_TEST;
    CREATE INDEX IND_DTL_TEST ON DTL(PROCESS_IND, DTLID, PROCESS_DATE);
    Charles,

    Appreciate your patience. Actually, after you suggested first about creating new index, I tried many combinations (including one you suggested). In fact, the one you have suggested here is the one which I thought was the most useful (for this UPDATE).
    In the above, notice that for the first execution plan that there were 100 starts for the YYYYYYYYYY_PK index, but no consistent gets. In the second execution plan there are also 100 starts with 9 consistent gets for the index access and 100 for the YYYYYYYYYY table - so, unlike the test case, the increase in the number of consistent gets is much less driven by the increased access of the YYYYYYYYYY table (I am guessing that this is the HDR table in your test case). The majority of the increase in the consistent gets are from the XXXXXXXXXXXXX_IXX2 index. Is there any positive change by making the PROCESS_IND column the leading column in that index?
    As I said earlier, after my initial test (when I saw much less I/Os due to new index), every subsequent execution has proven to be consuming more I/Os than if I use existing index (on PROCESS_IND and PROCESS_DATE). You are correct that increase in number of consistent gets is mainly due to the access to the YYYYYY table. If you look at the plans I posted earlier, the execution path is identical. Even the number of rows derived from YYYYYY table are also same. But there is significant difference between the number of consistent gets while accessing the index as well as YYYYY table. I can think of only 2 possible explanations here:
    1) Either between those 2 executions, the entries in the index have spread over more number of blocks due to the index getting updated OR
    2) for some strange reason, the short-circuit is not taking place.

    I guess I am more disappointed with myself for not being able to understand why reality does not match theory and not being able to generate test data that can simulate the results I am seeing with original issue.

    p.s. In fact, I also tried to reconstruct the test case and populated data in DTL table in a way that I thought was similar to the original application but I ended up getting a CONCATENATION in the execution plan for the UPDATE, even when I was using outer join and not UNION ALL (and hence decided not to post it).
  • 23. Re: UPDATE with ROWNUM (or TOP-N UPDATE)
    user503699 Expert
    Currently Being Moderated
    user503699 wrote:
    I guess I am more disappointed with myself for not being able to understand why reality does not match theory and not being able to generate test data that can simulate the results I am seeing with original issue.

    p.s. In fact, I also tried to reconstruct the test case and populated data in DTL table in a way that I thought was similar to the original application but I ended up getting a CONCATENATION in the execution plan for the UPDATE, even when I was using outer join and not UNION ALL (and hence decided not to post it).
    I believe I have finally managed to create a test set up that resembles the queries and tables in the original problem. As this thread might have deviated to some extent from my OP, I will summarise the problem I am trying to solve
    1) There is no longer a need to use UNION ALL and I have managed to convert the query to use outer join.
    2) The following test case shows that while a SELECT statement manages to use right index and more efficient execution path (NL join) that benefits from short-circuit processing (as Charles described earlier), the same SELECT, when used as sub-query feeding records to an UPDATE statement, does not use the optimal execution path.
    So, in short, I am trying to achieve the execution plan (with short-circuit processing) of the standalone SELECT in the corresponding UPDATE statement.
    SQL> select * from v$version ;
    
    BANNER
    --------------------------------------------------------------------------------
    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
    PL/SQL Release 11.2.0.1.0 - Production
    CORE    11.2.0.1.0      Production
    TNS for Linux: Version 11.2.0.1.0 - Production
    NLSRTL Version 11.2.0.1.0 - Production
    
    SQL> drop table dtl purge ;
    
    Table dropped.
    
    SQL> drop table hdr purge ;
    
    Table dropped.
    
    SQL> create table hdr (hdrid number(10), hdr_ind number(2) not null, hpad varchar2(100), constraint hdr_pk primary key (hdrid)) ;
    
    Table created.
    
    SQL> create table dtl (dtlid number(10), process_ind varchar2(10) not null, process_date date not null, hdrid number(10), type_id number(2), cat_id number(2), dpad varchar2(100), constraint dtl_pk primary key (dtlid)) ;
    
    Table created.
    
    SQL> create index dtl_idx1 on dtl(process_ind, process_date) nologging ;
    
    Index created.
    
    SQL> create index dtl_idx2 on dtl(process_ind, decode(type_id, 1, 99, type_id), process_date) nologging ;
    
    Index created.
    
    SQL> insert into hdr select level, case when mod(level, 3) = 0 then 0 when mod(level, 7) = 0 then 2 else 6 end, dbms_random.string('a', 100) from dual connect by level <= 10000 ;
    
    10000 rows created.
    
    SQL> commit ;
    
    Commit complete.
    
    SQL> drop sequence dtl_seq ;
    
    Sequence dropped.
    
    SQL> create sequence dtl_seq cache 1000 ;
    
    Sequence created.
    SQL> insert into dtl select dtl_seq.nextval, 'NEW', sysdate, case mod(rownum, 500) when 0 then null else hdrid end, case mod(rownum, 100) when 0 then 2 else 1 end, 1, dbms_random.string('a', 10) from (select hdrid from hdr where rownum <= 3000),(select level from dual connect by level <= 2) ;
    
    6000 rows created.
    
    SQL> commit ;
    
    Commit complete.
    
    SQL> begin
      2  for i in (select level rn from dual connect by level <= 20 order by dbms_random.random)
      3  loop
      4  insert into dtl select dtl_seq.nextval, 'NEW', (sysdate + i.rn), case mod(rownum, 500) when 0 then null else hdrid end, case mod(rownum, 100) when 0 then 2 else 1 end, 1, dbms_random.string('a', 10) from (select hdrid from hdr where rownum <= 5000),(select level from dual connect by level <= 20) ;
      5  commit;
      6  end loop;
      7  end;
      8  /
    
    PL/SQL procedure successfully completed.
    
    SQL> exec dbms_stats.gather_table_stats(user, 'DTL', cascade=>true);
    
    PL/SQL procedure successfully completed.
    
    SQL> exec dbms_stats.gather_table_stats(user, 'HDR', cascade=>true);
    
    PL/SQL procedure successfully completed.
    
    SQL>                 select /*+ gather_plan_statistics */ dtlid
      2                    from (
      3                          select dtl.dtlid
      4                            from dtl,
      5                                 hdr
      6                           where dtl.hdrid = hdr.hdrid(+)
      7                             and dtl.process_date < sysdate
      8                             and dtl.process_ind = 'NEW'
      9                             and dtl.cat_id = 1
     10                             and (
     11                                  (hdr_ind in (0, 2) and dtl.hdrid IS NOT NULL) OR (dtl.hdrid IS NULL)
     12                                 )
     13                           order by decode(dtl.type_id, 1, 99, dtl.type_id),dtl.process_date
     14                         )
     15                   where rownum <= 100 ;
    
         DTLID
    ----------
           101
           201
           .
           .
    
    100 rows selected.
    
    SQL> select * from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST')) ;
    
    PLAN_TABLE_OUTPUT
    -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    SQL_ID  16kydq6600bjc, child number 0
    -------------------------------------
                    select /*+ gather_plan_statistics */ dtlid
         from (                         select dtl.dtlid
           from dtl,                                hdr
         where dtl.hdrid = hdr.hdrid(+)                            and
    dtl.process_date < sysdate                            and
    dtl.process_ind = 'NEW'                            and dtl.cat_id = 1
                             and (                                 (hdr_ind
    in (0, 2) and dtl.hdrid IS NOT NULL) OR (dtl.hdrid IS NULL)
                       )                          order by
    decode(dtl.type_id, 1, 99, dtl.type_id),dtl.process_date
            )                  where rownum <= 100
    
    Plan hash value: 2162242114
    
    ---------------------------------------------------------------------------------------------------------------
    | Id  | Operation                       | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
    ---------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                |          |      1 |        |    100 |00:00:00.01 |     487 |    106 |
    |*  1 |  COUNT STOPKEY                  |          |      1 |        |    100 |00:00:00.01 |     487 |    106 |
    |   2 |   VIEW                          |          |      1 |    101 |    100 |00:00:00.01 |     487 |    106 |
    |*  3 |    FILTER                       |          |      1 |        |    100 |00:00:00.01 |     487 |    106 |
    |   4 |     NESTED LOOPS OUTER          |          |      1 |    101 |    215 |00:00:00.02 |     487 |    106 |
    |*  5 |      TABLE ACCESS BY INDEX ROWID| DTL      |      1 |  95524 |    215 |00:00:00.02 |     225 |    106 |
    |*  6 |       INDEX RANGE SCAN          | DTL_IDX2 |      1 |    152 |    215 |00:00:00.01 |     189 |     71 |
    |   7 |      TABLE ACCESS BY INDEX ROWID| HDR      |    215 |      1 |    203 |00:00:00.01 |     262 |      0 |
    |*  8 |       INDEX UNIQUE SCAN         | HDR_PK   |    215 |      1 |    203 |00:00:00.01 |      59 |      0 |
    ---------------------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       1 - filter(ROWNUM<=100)
       3 - filter(((INTERNAL_FUNCTION("HDR_IND") AND "DTL"."HDRID" IS NOT NULL) OR "DTL"."HDRID" IS NULL))
       5 - filter("DTL"."CAT_ID"=1)
       6 - access("DTL"."PROCESS_IND"='NEW' AND "DTL"."PROCESS_DATE"<SYSDATE@!)
           filter("DTL"."PROCESS_DATE"<SYSDATE@!)
       8 - access("DTL"."HDRID"="HDR"."HDRID")
    
    
    40 rows selected.
    
    SQL>                 select /*+ gather_plan_statistics */ dtlid
      2                    from (
      3                          select /*+ LEADING(dtl hdr) USE_NL(hdr) INDEX(dtl DTL_IDX1) */ dtl.dtlid
      4                            from dtl,
      5                                 hdr
      6                           where dtl.hdrid = hdr.hdrid(+)
      7                             and dtl.process_date < sysdate
      8                             and dtl.process_ind = 'NEW'
      9                             and dtl.cat_id = 1
     10                             and (
     11                                  (hdr_ind in (0, 2) and dtl.hdrid IS NOT NULL) OR (dtl.hdrid IS NULL)
     12                                 )
     13                           order by decode(dtl.type_id, 1, 99, dtl.type_id),dtl.process_date
     14                         )
     15                   where rownum <= 100 ;
    
         DTLID
    ----------
           101
          5301
           .
           .
    
    100 rows selected.
    
    SQL> select * from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST')) ;
    
    PLAN_TABLE_OUTPUT
    -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    SQL_ID  g41r9gg0nftu4, child number 0
    -------------------------------------
                    select /*+ gather_plan_statistics */ dtlid
         from (                         select /*+ LEADING(dtl hdr)
    USE_NL(hdr) INDEX(dtl DTL_IDX1) */ dtl.dtlid
    from dtl,                                hdr
    where dtl.hdrid = hdr.hdrid(+)                            and
    dtl.process_date < sysdate                            and
    dtl.process_ind = 'NEW'                            and dtl.cat_id = 1
                             and (                                 (hdr_ind
    in (0, 2) and dtl.hdrid IS NOT NULL) OR (dtl.hdrid IS NULL)
                       )                          order by
    decode(dtl.type_id, 1, 99, dtl.type_id),dtl.process_date
            )                  where rownum <= 100
    
    Plan hash value: 2203913375
    -------------------------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                        | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
    -------------------------------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                 |          |      1 |        |    100 |00:00:00.41 |    6206 |     24 |       |       |          |
    |*  1 |  COUNT STOPKEY                   |          |      1 |        |    100 |00:00:00.41 |    6206 |     24 |       |       |          |
    |   2 |   VIEW                           |          |      1 |  42370 |    100 |00:00:00.41 |    6206 |     24 |       |       |          |
    |*  3 |    SORT ORDER BY STOPKEY         |          |      1 |  42370 |    100 |00:00:00.41 |    6206 |     24 |  6144 |  6144 | 6144  (0)|
    |*  4 |     FILTER                       |          |      1 |        |   2576 |00:00:00.36 |    6206 |     24 |       |       |          |
    |   5 |      NESTED LOOPS OUTER          |          |      1 |  42370 |   6000 |00:00:00.45 |    6206 |     24 |       |       |          |
    |*  6 |       TABLE ACCESS BY INDEX ROWID| DTL      |      1 |  95524 |   6000 |00:00:00.08 |      74 |     24 |       |       |          |
    |*  7 |        INDEX RANGE SCAN          | DTL_IDX1 |      1 |  95524 |   6000 |00:00:00.03 |      39 |     24 |       |       |          |
    |   8 |       TABLE ACCESS BY INDEX ROWID| HDR      |   6000 |      1 |   5988 |00:00:00.28 |    6132 |      0 |       |       |          |
    |*  9 |        INDEX UNIQUE SCAN         | HDR_PK   |   6000 |      1 |   5988 |00:00:00.09 |     144 |      0 |       |       |          |
    -------------------------------------------------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       1 - filter(ROWNUM<=100)
       3 - filter(ROWNUM<=100)
       4 - filter(((INTERNAL_FUNCTION("HDR_IND") AND "DTL"."HDRID" IS NOT NULL) OR "DTL"."HDRID" IS NULL))
       6 - filter("DTL"."CAT_ID"=1)
       7 - access("DTL"."PROCESS_IND"='NEW' AND "DTL"."PROCESS_DATE"<SYSDATE@!)
       9 - access("DTL"."HDRID"="HDR"."HDRID")
    
    
    42 rows selected.
    
    SQL> update /*+ gather_plan_statistics */ dtl set process_ind = 'PROCESSED'
    where dtlid in (
                    select dtlid
                      from (
                            select dtl.dtlid
                              from dtl,
                                   hdr
                             where dtl.hdrid = hdr.hdrid(+)
                               and dtl.process_date < sysdate
                               and dtl.process_ind = 'NEW'
                               and dtl.cat_id = 1
                               and (
                                    (hdr_ind in (0, 2) and dtl.hdrid IS NOT NULL) OR (dtl.hdrid IS NULL)
                                   )
                             order by decode(dtl.type_id, 1, 99, dtl.type_id),dtl.process_date
                           )
                     where rownum <= 100
     18                 ) ;
    
    100 rows updated.
    
    SQL> select * from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST')) ;
    
    PLAN_TABLE_OUTPUT
    -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    SQL_ID  0qd1bvrsvr4yj, child number 0
    -------------------------------------
    update /*+ gather_plan_statistics */ dtl set process_ind = 'PROCESSED'
    where dtlid in (                 select dtlid                   from (
                           select dtl.dtlid                           from
    dtl,                                hdr                          where
    dtl.hdrid = hdr.hdrid(+)                            and
    dtl.process_date < sysdate                            and
    dtl.process_ind = 'NEW'                            and dtl.cat_id = 1
                             and (                                 (hdr_ind
    in (0, 2) and dtl.hdrid IS NOT NULL) OR (dtl.hdrid IS NULL)
                       )                          order by
    decode(dtl.type_id, 1, 99, dtl.type_id),dtl.process_date
            )                  where rownum <= 100                )
    
    Plan hash value: 4241854096
    
    -----------------------------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                            | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
    -----------------------------------------------------------------------------------------------------------------------------------------------
    |   0 | UPDATE STATEMENT                     |          |      1 |        |      0 |00:00:00.06 |    1665 |     23 |       |       |          |
    |   1 |  UPDATE                              | DTL      |      1 |        |      0 |00:00:00.06 |    1665 |     23 |       |       |          |
    |   2 |   NESTED LOOPS                       |          |      1 |    100 |    100 |00:00:00.05 |     323 |     21 |       |       |          |
    |   3 |    VIEW                              | VW_NSO_1 |      1 |    100 |    100 |00:00:00.05 |     247 |     21 |       |       |          |
    |   4 |     SORT UNIQUE                      |          |      1 |    100 |    100 |00:00:00.04 |     247 |     21 | 73728 | 73728 |          |
    |*  5 |      COUNT STOPKEY                   |          |      1 |        |    100 |00:00:00.15 |     247 |     21 |       |       |          |
    |   6 |       VIEW                           |          |      1 |  63619 |    100 |00:00:00.15 |     247 |     21 |       |       |          |
    |*  7 |        SORT ORDER BY STOPKEY         |          |      1 |  63619 |    100 |00:00:00.15 |     247 |     21 |  9216 |  9216 | 8192  (0)|
    |*  8 |         FILTER                       |          |      1 |        |   2576 |00:00:00.07 |     247 |     21 |       |       |          |
    |*  9 |          HASH JOIN RIGHT OUTER       |          |      1 |  63619 |   6000 |00:00:00.49 |     247 |     21 |   968K|   968K| 1416K (0)|
    |  10 |           TABLE ACCESS FULL          | HDR      |      1 |  10000 |  10000 |00:00:00.14 |     190 |      0 |       |       |          |
    |* 11 |           TABLE ACCESS BY INDEX ROWID| DTL      |      1 |  95524 |   6000 |00:00:00.16 |      57 |     21 |       |       |          |
    |* 12 |            INDEX RANGE SCAN          | DTL_IDX1 |      1 |  95524 |   6000 |00:00:00.05 |      22 |     21 |       |       |          |
    |* 13 |    INDEX UNIQUE SCAN                 | DTL_PK   |    100 |      1 |    100 |00:00:00.01 |      76 |      0 |       |       |          |
    -----------------------------------------------------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       5 - filter(ROWNUM<=100)
       7 - filter(ROWNUM<=100)
       8 - filter(((INTERNAL_FUNCTION("HDR_IND") AND "DTL"."HDRID" IS NOT NULL) OR "DTL"."HDRID" IS NULL))
       9 - access("DTL"."HDRID"="HDR"."HDRID")
      11 - filter("DTL"."CAT_ID"=1)
      12 - access("DTL"."PROCESS_IND"='NEW' AND "DTL"."PROCESS_DATE"<SYSDATE@!)
      13 - access("DTLID"="DTLID")
    
    
    47 rows selected.
    
    SQL> spool off
  • 24. Re: UPDATE with ROWNUM (or TOP-N UPDATE)
    CharlesHooper Expert
    Currently Being Moderated
    Narendra,

    It appears that you are coming closer to a solution.

    I used your test case script on 11.2.0.2, and arrived at similar results. In the case of the UPDATE statement, hints should help.

    The unhinted SQL statement:
    select /*+ gather_plan_statistics */ dtlid
    from (
        select dtl.dtlid
        from dtl,
             hdr
        where dtl.hdrid = hdr.hdrid(+)
              and dtl.process_date < sysdate
              and dtl.process_ind = 'NEW'
              and dtl.cat_id = 1
              and (
                   (hdr_ind in (0, 2) and dtl.hdrid IS NOT NULL) OR (dtl.hdrid IS NULL)
                  )
        order by decode(dtl.type_id, 1, 99, dtl.type_id),dtl.process_date
                )
        where rownum <= 100 ;
     
    select * from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST')) ;
    This SQL statement will possibly be subject to cardinality feedback on future executions. First execution:
    SQL_ID  fz6p3jyhtfchh, child number 0
    -------------------------------------
    select /*+ gather_plan_statistics */ dtlid from (     select dtl.dtlid
       from dtl,          hdr     where dtl.hdrid = hdr.hdrid(+)
    and dtl.process_date < sysdate           and dtl.process_ind = 'NEW'
           and dtl.cat_id = 1           and (                (hdr_ind in
    (0, 2) and dtl.hdrid IS NOT NULL) OR (dtl.hdrid IS NULL)
    )     order by decode(dtl.type_id, 1, 99, dtl.type_id),dtl.process_date
                )     where rownum <= 100
     
    Plan hash value: 2162242114
     
    ------------------------------------------------------------------------------------------------------
    | Id  | Operation                       | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
    ------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                |          |      1 |        |    100 |00:00:00.01 |     523 |
    |*  1 |  COUNT STOPKEY                  |          |      1 |        |    100 |00:00:00.01 |     523 |
    |   2 |   VIEW                          |          |      1 |    101 |    100 |00:00:00.01 |     523 |
    |*  3 |    FILTER                       |          |      1 |        |    100 |00:00:00.01 |     523 |
    |   4 |     NESTED LOOPS OUTER          |          |      1 |    101 |    211 |00:00:00.01 |     523 |
    |*  5 |      TABLE ACCESS BY INDEX ROWID| DTL      |      1 |  95524 |    211 |00:00:00.01 |     253 |
    |*  6 |       INDEX RANGE SCAN          | DTL_IDX2 |      1 |    152 |    211 |00:00:00.01 |     212 |
    |   7 |      TABLE ACCESS BY INDEX ROWID| HDR      |    211 |      1 |    199 |00:00:00.01 |     270 |
    |*  8 |       INDEX UNIQUE SCAN         | HDR_PK   |    211 |      1 |    199 |00:00:00.01 |      71 |
    ------------------------------------------------------------------------------------------------------
     
    Predicate Information (identified by operation id):
    ---------------------------------------------------
       1 - filter(ROWNUM<=100)
       3 - filter(((INTERNAL_FUNCTION("HDR_IND") AND "DTL"."HDRID" IS NOT NULL) OR "DTL"."HDRID"
                  IS NULL))
       5 - filter("DTL"."CAT_ID"=1)
       6 - access("DTL"."PROCESS_IND"='NEW' AND "DTL"."PROCESS_DATE"<SYSDATE@!)
           filter("DTL"."PROCESS_DATE"<SYSDATE@!)
       8 - access("DTL"."HDRID"="HDR"."HDRID")
    Second Execution, the execxution plan changes because of cardinality feedback, resulting in at least a 10 fold increase in the execution time:
    SQL_ID  fz6p3jyhtfchh, child number 1
    -------------------------------------
    select /*+ gather_plan_statistics */ dtlid from (     select dtl.dtlid
       from dtl,          hdr     where dtl.hdrid = hdr.hdrid(+)
    and dtl.process_date < sysdate           and dtl.process_ind = 'NEW'
           and dtl.cat_id = 1           and (                (hdr_ind in
    (0, 2) and dtl.hdrid IS NOT NULL) OR (dtl.hdrid IS NULL)
    )     order by decode(dtl.type_id, 1, 99, dtl.type_id),dtl.process_date
                )     where rownum <= 100
     
    Plan hash value: 907805734
     
    ---------------------------------------------------------------------------------------------------------------------
    | Id  | Operation               | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
    ---------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT        |      |      1 |        |    100 |00:00:00.10 |   12350 |       |       |          |
    |*  1 |  COUNT STOPKEY          |      |      1 |        |    100 |00:00:00.10 |   12350 |       |       |          |
    |   2 |   VIEW                  |      |      1 |     94 |    100 |00:00:00.10 |   12350 |       |       |          |
    |*  3 |    SORT ORDER BY STOPKEY|      |      1 |     94 |    100 |00:00:00.10 |   12350 |  6144 |  6144 | 6144  (0)|
    |*  4 |     FILTER              |      |      1 |        |   2582 |00:00:00.10 |   12350 |       |       |          |
    |*  5 |      HASH JOIN OUTER    |      |      1 |     94 |   6000 |00:00:00.10 |   12350 |  1095K|  1095K| 1527K (0)|
    |*  6 |       TABLE ACCESS FULL | DTL  |      1 |    211 |   6000 |00:00:00.01 |   12147 |       |       |          |
    |   7 |       TABLE ACCESS FULL | HDR  |      1 |  10000 |  10000 |00:00:00.01 |     203 |       |       |          |
    ---------------------------------------------------------------------------------------------------------------------
     
    Predicate Information (identified by operation id):
    ---------------------------------------------------
       1 - filter(ROWNUM<=100)
       3 - filter(ROWNUM<=100)
       4 - filter(((INTERNAL_FUNCTION("HDR_IND") AND "DTL"."HDRID" IS NOT NULL) OR "DTL"."HDRID" IS NULL))
       5 - access("DTL"."HDRID"="HDR"."HDRID")
       6 - filter(("DTL"."PROCESS_DATE"<SYSDATE@! AND "DTL"."PROCESS_IND"='NEW' AND "DTL"."CAT_ID"=1))
     
    Note
    -----
       - cardinality feedback used for this statement
    Trying again with the hints that you provided:
    select /*+ gather_plan_statistics */ dtlid
    from (
        select /*+ LEADING(dtl hdr) USE_NL(hdr) INDEX(dtl DTL_IDX1) */ dtl.dtlid
        from dtl,
             hdr
        where dtl.hdrid = hdr.hdrid(+)
              and dtl.process_date < sysdate
              and dtl.process_ind = 'NEW'
              and dtl.cat_id = 1
              and (
                   (hdr_ind in (0, 2) and dtl.hdrid IS NOT NULL) OR (dtl.hdrid IS NULL)
                  )
        order by decode(dtl.type_id, 1, 99, dtl.type_id),dtl.process_date
                )
        where rownum <= 100 ;
     
    select * from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST')) ;
    This SQL statement could be subject to cardinality feedback, although the specified hints should help constrain the execution plan. First Execution:
    SQL_ID  9x0a45ud8yct7, child number 0
    -------------------------------------
    select /*+ gather_plan_statistics */ dtlid from (     select /*+
    LEADING(dtl hdr) USE_NL(hdr) INDEX(dtl DTL_IDX1) */ dtl.dtlid     from
    dtl,          hdr     where dtl.hdrid = hdr.hdrid(+)           and
    dtl.process_date < sysdate           and dtl.process_ind = 'NEW'
       and dtl.cat_id = 1           and (                (hdr_ind in (0, 2)
    and dtl.hdrid IS NOT NULL) OR (dtl.hdrid IS NULL)               )
    order by decode(dtl.type_id, 1, 99, dtl.type_id),dtl.process_date
          )     where rownum <= 100
     
    Plan hash value: 2203913375
     
    ----------------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                        | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
    ----------------------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                 |          |      1 |        |    100 |00:00:00.01 |    6253 |       |       |          |
    |*  1 |  COUNT STOPKEY                   |          |      1 |        |    100 |00:00:00.01 |    6253 |       |       |          |
    |   2 |   VIEW                           |          |      1 |  42370 |    100 |00:00:00.01 |    6253 |       |       |          |
    |*  3 |    SORT ORDER BY STOPKEY         |          |      1 |  42370 |    100 |00:00:00.01 |    6253 |  6144 |  6144 | 6144  (0)|
    |*  4 |     FILTER                       |          |      1 |        |   2582 |00:00:00.01 |    6253 |       |       |          |
    |   5 |      NESTED LOOPS OUTER          |          |      1 |  42370 |   6000 |00:00:00.01 |    6253 |       |       |          |
    |*  6 |       TABLE ACCESS BY INDEX ROWID| DTL      |      1 |  95524 |   6000 |00:00:00.01 |      81 |       |       |          |
    |*  7 |        INDEX RANGE SCAN          | DTL_IDX1 |      1 |  95524 |   6000 |00:00:00.01 |      46 |       |       |          |
    |   8 |       TABLE ACCESS BY INDEX ROWID| HDR      |   6000 |      1 |   5988 |00:00:00.01 |    6172 |       |       |          |
    |*  9 |        INDEX UNIQUE SCAN         | HDR_PK   |   6000 |      1 |   5988 |00:00:00.01 |     184 |       |       |          |
    ----------------------------------------------------------------------------------------------------------------------------------
     
    Predicate Information (identified by operation id):
    ---------------------------------------------------
       1 - filter(ROWNUM<=100)
       3 - filter(ROWNUM<=100)
       4 - filter(((INTERNAL_FUNCTION("HDR_IND") AND "DTL"."HDRID" IS NOT NULL) OR "DTL"."HDRID" IS NULL))
       6 - filter("DTL"."CAT_ID"=1)
       7 - access("DTL"."PROCESS_IND"='NEW' AND "DTL"."PROCESS_DATE"<SYSDATE@!)
       9 - access("DTL"."HDRID"="HDR"."HDRID")
    Second Execution:
    SQL_ID  9x0a45ud8yct7, child number 1
    -------------------------------------
    select /*+ gather_plan_statistics */ dtlid from (     select /*+
    LEADING(dtl hdr) USE_NL(hdr) INDEX(dtl DTL_IDX1) */ dtl.dtlid     from
    dtl,          hdr     where dtl.hdrid = hdr.hdrid(+)           and
    dtl.process_date < sysdate           and dtl.process_ind = 'NEW'
       and dtl.cat_id = 1           and (                (hdr_ind in (0, 2)
    and dtl.hdrid IS NOT NULL) OR (dtl.hdrid IS NULL)               )
    order by decode(dtl.type_id, 1, 99, dtl.type_id),dtl.process_date
          )     where rownum <= 100
     
    Plan hash value: 2203913375
     
    ----------------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                        | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
    ----------------------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                 |          |      1 |        |    100 |00:00:00.01 |    6236 |       |       |          |
    |*  1 |  COUNT STOPKEY                   |          |      1 |        |    100 |00:00:00.01 |    6236 |       |       |          |
    |   2 |   VIEW                           |          |      1 |   2661 |    100 |00:00:00.01 |    6236 |       |       |          |
    |*  3 |    SORT ORDER BY STOPKEY         |          |      1 |   2661 |    100 |00:00:00.01 |    6236 |  6144 |  6144 | 6144  (0)|
    |*  4 |     FILTER                       |          |      1 |        |   2582 |00:00:00.01 |    6236 |       |       |          |
    |   5 |      NESTED LOOPS OUTER          |          |      1 |   2661 |   6000 |00:00:00.01 |    6236 |       |       |          |
    |*  6 |       TABLE ACCESS BY INDEX ROWID| DTL      |      1 |   6000 |   6000 |00:00:00.01 |      64 |       |       |          |
    |*  7 |        INDEX RANGE SCAN          | DTL_IDX1 |      1 |   6000 |   6000 |00:00:00.01 |      29 |       |       |          |
    |   8 |       TABLE ACCESS BY INDEX ROWID| HDR      |   6000 |      1 |   5988 |00:00:00.01 |    6172 |       |       |          |
    |*  9 |        INDEX UNIQUE SCAN         | HDR_PK   |   6000 |      1 |   5988 |00:00:00.01 |     184 |       |       |          |
    ----------------------------------------------------------------------------------------------------------------------------------
     
    Predicate Information (identified by operation id):
    ---------------------------------------------------
       1 - filter(ROWNUM<=100)
       3 - filter(ROWNUM<=100)
       4 - filter(((INTERNAL_FUNCTION("HDR_IND") AND "DTL"."HDRID" IS NOT NULL) OR "DTL"."HDRID" IS NULL))
       6 - filter("DTL"."CAT_ID"=1)
       7 - access("DTL"."PROCESS_IND"='NEW' AND "DTL"."PROCESS_DATE"<SYSDATE@!)
       9 - access("DTL"."HDRID"="HDR"."HDRID")
     
    Note
    -----
       - cardinality feedback used for this statement
    In the above, the execution plan remained the same, with the cardinality estimates closer to the actual number of rows returned.

    Let's try an unhinted version of the UPDATE statement:
    update /*+ gather_plan_statistics */ dtl set process_ind = 'PROCESSED'
    where dtlid in (
      select dtlid
      from (
        select dtl.dtlid
        from dtl,
             hdr
        where dtl.hdrid = hdr.hdrid(+)
              and dtl.process_date < sysdate
              and dtl.process_ind = 'NEW'
              and dtl.cat_id = 1
              and (
                   (hdr_ind in (0, 2) and dtl.hdrid IS NOT NULL) OR (dtl.hdrid IS NULL)
                  )
        order by decode(dtl.type_id, 1, 99, dtl.type_id),dtl.process_date
                )
        where rownum <= 100);
    
    select * from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST')) ;
    
    ROLLBACK;
    The execution plan I received is similar to the one that you posted, although I have a full table scan of the DTL table, where you show an index range scan of the DTL_IDX1 index.
    SQL_ID  2r2vmu7fgsstx, child number 0
    -------------------------------------
    update /*+ gather_plan_statistics */ dtl set process_ind = 'PROCESSED'
    where dtlid in (   select dtlid   from (     select dtl.dtlid     from
    dtl,          hdr     where dtl.hdrid = hdr.hdrid(+)           and
    dtl.process_date < sysdate           and dtl.process_ind = 'NEW'
       and dtl.cat_id = 1           and (                (hdr_ind in (0, 2)
    and dtl.hdrid IS NOT NULL) OR (dtl.hdrid IS NULL)               )
    order by decode(dtl.type_id, 1, 99, dtl.type_id),dtl.process_date
          )     where rownum <= 100)
     
    Plan hash value: 404032855
     
    -------------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                     | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
    -------------------------------------------------------------------------------------------------------------------------------
    |   0 | UPDATE STATEMENT              |          |      1 |        |      0 |00:00:00.11 |   13789 |       |       |          |
    |   1 |  UPDATE                       | DTL      |      1 |        |      0 |00:00:00.11 |   13789 |       |       |          |
    |   2 |   NESTED LOOPS                |          |      1 |    100 |    100 |00:00:00.11 |   12431 |       |       |          |
    |   3 |    VIEW                       | VW_NSO_1 |      1 |    100 |    100 |00:00:00.11 |   12350 |       |       |          |
    |   4 |     SORT UNIQUE               |          |      1 |    100 |    100 |00:00:00.11 |   12350 | 73728 | 73728 |          |
    |*  5 |      COUNT STOPKEY            |          |      1 |        |    100 |00:00:00.11 |   12350 |       |       |          |
    |   6 |       VIEW                    |          |      1 |  63619 |    100 |00:00:00.11 |   12350 |       |       |          |
    |*  7 |        SORT ORDER BY STOPKEY  |          |      1 |  63619 |    100 |00:00:00.11 |   12350 |  9216 |  9216 | 8192  (0)|
    |*  8 |         FILTER                |          |      1 |        |   2582 |00:00:00.01 |   12350 |       |       |          |
    |*  9 |          HASH JOIN RIGHT OUTER|          |      1 |  63619 |   6000 |00:00:00.01 |   12350 |  1306K|  1306K| 1646K (0)|
    |  10 |           TABLE ACCESS FULL   | HDR      |      1 |  10000 |  10000 |00:00:00.01 |     203 |       |       |          |
    |* 11 |           TABLE ACCESS FULL   | DTL      |      1 |  95524 |   6000 |00:00:00.01 |   12147 |       |       |          |
    |* 12 |    INDEX UNIQUE SCAN          | DTL_PK   |    100 |      1 |    100 |00:00:00.01 |      81 |       |       |          |
    -------------------------------------------------------------------------------------------------------------------------------
     
    Predicate Information (identified by operation id):
    ---------------------------------------------------
       5 - filter(ROWNUM<=100)
       7 - filter(ROWNUM<=100)
       8 - filter(((INTERNAL_FUNCTION("HDR_IND") AND "DTL"."HDRID" IS NOT NULL) OR "DTL"."HDRID" IS NULL))
       9 - access("DTL"."HDRID"="HDR"."HDRID")
      11 - filter(("DTL"."PROCESS_DATE"<SYSDATE@! AND "DTL"."PROCESS_IND"='NEW' AND "DTL"."CAT_ID"=1))
      12 - access("DTLID"="DTLID")
    Let's try the same hints that you used in the SELECT statement above:
    update /*+ gather_plan_statistics */ dtl set process_ind = 'PROCESSED'
    where dtlid in (
      select dtlid
      from (
        select /*+ LEADING(dtl hdr) USE_NL(hdr) INDEX(dtl DTL_IDX1) */ dtl.dtlid
        from dtl,
             hdr
        where dtl.hdrid = hdr.hdrid(+)
              and dtl.process_date < sysdate
              and dtl.process_ind = 'NEW'
              and dtl.cat_id = 1
              and (
                   (hdr_ind in (0, 2) and dtl.hdrid IS NOT NULL) OR (dtl.hdrid IS NULL)
                  )
        order by decode(dtl.type_id, 1, 99, dtl.type_id),dtl.process_date
                )
        where rownum <= 100);
     
    select * from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST')) ;
     
    ROLLBACK;
    The execution time decreases and so do the number of consistent gets:
    SQL_ID  dyvc2crds3p6w, child number 0
    -------------------------------------
    update /*+ gather_plan_statistics */ dtl set process_ind = 'PROCESSED'
    where dtlid in (   select dtlid   from (     select /*+ LEADING(dtl
    hdr) USE_NL(hdr) INDEX(dtl DTL_IDX1) */ dtl.dtlid     from dtl,
     hdr     where dtl.hdrid = hdr.hdrid(+)           and dtl.process_date
    < sysdate           and dtl.process_ind = 'NEW'           and
    dtl.cat_id = 1           and (                (hdr_ind in (0, 2) and
    dtl.hdrid IS NOT NULL) OR (dtl.hdrid IS NULL)               )     order
    by decode(dtl.type_id, 1, 99, dtl.type_id),dtl.process_date
    )     where rownum <= 100)
     
    Plan hash value: 3856197454
     
    --------------------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                            | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
    --------------------------------------------------------------------------------------------------------------------------------------
    |   0 | UPDATE STATEMENT                     |          |      1 |        |      0 |00:00:00.02 |    7657 |       |       |          |
    |   1 |  UPDATE                              | DTL      |      1 |        |      0 |00:00:00.02 |    7657 |       |       |          |
    |   2 |   NESTED LOOPS                       |          |      1 |    100 |    100 |00:00:00.01 |    6317 |       |       |          |
    |   3 |    VIEW                              | VW_NSO_1 |      1 |    100 |    100 |00:00:00.01 |    6236 |       |       |          |
    |   4 |     SORT UNIQUE                      |          |      1 |    100 |    100 |00:00:00.01 |    6236 | 73728 | 73728 |          |
    |*  5 |      COUNT STOPKEY                   |          |      1 |        |    100 |00:00:00.01 |    6236 |       |       |          |
    |   6 |       VIEW                           |          |      1 |  63619 |    100 |00:00:00.01 |    6236 |       |       |          |
    |*  7 |        SORT ORDER BY STOPKEY         |          |      1 |  63619 |    100 |00:00:00.01 |    6236 | 11264 | 11264 |10240  (0)|
    |*  8 |         FILTER                       |          |      1 |        |   2582 |00:00:00.01 |    6236 |       |       |          |
    |   9 |          NESTED LOOPS OUTER          |          |      1 |  63619 |   6000 |00:00:00.01 |    6236 |       |       |          |
    |* 10 |           TABLE ACCESS BY INDEX ROWID| DTL      |      1 |  95524 |   6000 |00:00:00.01 |      64 |       |       |          |
    |* 11 |            INDEX RANGE SCAN          | DTL_IDX1 |      1 |  95524 |   6000 |00:00:00.01 |      29 |       |       |          |
    |  12 |           TABLE ACCESS BY INDEX ROWID| HDR      |   6000 |      1 |   5988 |00:00:00.01 |    6172 |       |       |          |
    |* 13 |            INDEX UNIQUE SCAN         | HDR_PK   |   6000 |      1 |   5988 |00:00:00.01 |     184 |       |       |          |
    |* 14 |    INDEX UNIQUE SCAN                 | DTL_PK   |    100 |      1 |    100 |00:00:00.01 |      81 |       |       |          |
    --------------------------------------------------------------------------------------------------------------------------------------
     
    Predicate Information (identified by operation id):
    ---------------------------------------------------
       5 - filter(ROWNUM<=100)
       7 - filter(ROWNUM<=100)
       8 - filter(((INTERNAL_FUNCTION("HDR_IND") AND "DTL"."HDRID" IS NOT NULL) OR "DTL"."HDRID" IS NULL))
      10 - filter("DTL"."CAT_ID"=1)
      11 - access("DTL"."PROCESS_IND"='NEW' AND "DTL"."PROCESS_DATE"<SYSDATE@!)
      13 - access("DTL"."HDRID"="HDR"."HDRID")
      14 - access("DTLID"="DTLID")
    Changing the INDEX hint to use the other index that you created:
    update /*+ gather_plan_statistics */ dtl set process_ind = 'PROCESSED'
    where dtlid in (
      select dtlid
      from (
        select /*+ LEADING(dtl hdr) USE_NL(hdr) INDEX(dtl DTL_IDX2) */ dtl.dtlid
        from dtl,
             hdr
        where dtl.hdrid = hdr.hdrid(+)
              and dtl.process_date < sysdate
              and dtl.process_ind = 'NEW'
              and dtl.cat_id = 1
              and (
                   (hdr_ind in (0, 2) and dtl.hdrid IS NOT NULL) OR (dtl.hdrid IS NULL)
                  )
        order by decode(dtl.type_id, 1, 99, dtl.type_id),dtl.process_date
                )
        where rownum <= 100);
     
    select * from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST')) ;
      
    ROLLBACK;
    The execution plan shows that the execution time decreased again, as did the number of consistent gets:
     
    SQL_ID  7367s8aqb4mp0, child number 0
    -------------------------------------
    update /*+ gather_plan_statistics */ dtl set process_ind = 'PROCESSED'
    where dtlid in (   select dtlid   from (     select /*+ LEADING(dtl
    hdr) USE_NL(hdr) INDEX(dtl DTL_IDX2) */ dtl.dtlid     from dtl,
     hdr     where dtl.hdrid = hdr.hdrid(+)           and dtl.process_date
    < sysdate           and dtl.process_ind = 'NEW'           and
    dtl.cat_id = 1           and (                (hdr_ind in (0, 2) and
    dtl.hdrid IS NOT NULL) OR (dtl.hdrid IS NULL)               )     order
    by decode(dtl.type_id, 1, 99, dtl.type_id),dtl.process_date
    )     where rownum <= 100)
     
    Plan hash value: 3052733514
     
    -------------------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                           | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
    -------------------------------------------------------------------------------------------------------------------------------------
    |   0 | UPDATE STATEMENT                    |          |      1 |        |      0 |00:00:00.01 |    1845 |       |       |          |
    |   1 |  UPDATE                             | DTL      |      1 |        |      0 |00:00:00.01 |    1845 |       |       |          |
    |   2 |   NESTED LOOPS                      |          |      1 |    100 |    100 |00:00:00.01 |     505 |       |       |          |
    |   3 |    VIEW                             | VW_NSO_1 |      1 |    100 |    100 |00:00:00.01 |     418 |       |       |          |
    |   4 |     SORT UNIQUE                     |          |      1 |    100 |    100 |00:00:00.01 |     418 | 73728 | 73728 |          |
    |*  5 |      COUNT STOPKEY                  |          |      1 |        |    100 |00:00:00.01 |     418 |       |       |          |
    |   6 |       VIEW                          |          |      1 |  63619 |    100 |00:00:00.01 |     418 |       |       |          |
    |*  7 |        FILTER                       |          |      1 |        |    100 |00:00:00.01 |     418 |       |       |          |
    |   8 |         NESTED LOOPS OUTER          |          |      1 |  63619 |    211 |00:00:00.01 |     418 |       |       |          |
    |*  9 |          TABLE ACCESS BY INDEX ROWID| DTL      |      1 |  95524 |    211 |00:00:00.01 |     155 |       |       |          |
    |* 10 |           INDEX RANGE SCAN          | DTL_IDX2 |      1 |  95524 |    211 |00:00:00.01 |     119 |       |       |          |
    |  11 |          TABLE ACCESS BY INDEX ROWID| HDR      |    211 |      1 |    199 |00:00:00.01 |     263 |       |       |          |
    |* 12 |           INDEX UNIQUE SCAN         | HDR_PK   |    211 |      1 |    199 |00:00:00.01 |      64 |       |       |          |
    |* 13 |    INDEX UNIQUE SCAN                | DTL_PK   |    100 |      1 |    100 |00:00:00.01 |      87 |       |       |          |
    -------------------------------------------------------------------------------------------------------------------------------------
     
    Predicate Information (identified by operation id):
    ---------------------------------------------------
       5 - filter(ROWNUM<=100)
       7 - filter(((INTERNAL_FUNCTION("HDR_IND") AND "DTL"."HDRID" IS NOT NULL) OR "DTL"."HDRID" IS NULL))
       9 - filter("DTL"."CAT_ID"=1)
      10 - access("DTL"."PROCESS_IND"='NEW' AND "DTL"."PROCESS_DATE"<SYSDATE@!)
           filter("DTL"."PROCESS_DATE"<SYSDATE@!)
      12 - access("DTL"."HDRID"="HDR"."HDRID")
      13 - access("DTLID"="DTLID")
    So, it is possible to add hints to an UPDATE... IN SELECT type statement, as long as the hints are correctly located. Now, the question remaining is whether or not the DTL_IDX1 index, DTL_IDX2 index, or the hash join with full table scans will be more efficient with a larger data set, after the 5th, 10th, 20th, and 100th update. I think that we found in earlier experiments in this thread that the index size continues to grow on each UPDATE, in the process increasing the number of consistent gets that are performed.

    Charles Hooper
    http://hoopercharles.wordpress.com/
    IT Manager/Oracle DBA
    K&M Machine-Fabricating, Inc.
  • 25. Re: UPDATE with ROWNUM (or TOP-N UPDATE)
    user503699 Expert
    Currently Being Moderated
    Charles Hooper wrote:
    So, it is possible to add hints to an UPDATE... IN SELECT type statement, as long as the hints are correctly located. Now, the question remaining is whether or not the DTL_IDX1 index, DTL_IDX2 index, or the hash join with full table scans will be more efficient with a larger data set, after the 5th, 10th, 20th, and 100th update. I think that we found in earlier experiments in this thread that the index size continues to grow on each UPDATE, in the process increasing the number of consistent gets that are performed.
    Charles,

    It appears that those hints work in 11.2.0.2 but not in 11.2.0.1 and 10.2.0.5. It did not work on 11.2.0.1 (as earlier posted) and it also does not appear to work on 10.2.0.5 (See below).
    My theory is as both indexes, DTL_IDX1 and DTL_IDX2 have PROCESS_IND column and UPDATE changes the value in this column, both indexes should get affected following repeated UPDATEs. Assuming amount of data for each day remains similar in volume, why should an INDEX RANGE SCAN be affected? My understanding is an INDEX RANGE SCAN will find first matching index block and start reading blocks in sequential order thereafter.
    As PROCESS_IND column is on the leading edge of the index and the sub-query in UPDATE uses euality predicate on PROCESS_IND column, INDEX RANGE SCAN should be able to find the data by accessing fairly small and consistent number of blocks.
    Or have I lost the plot completely?
    SQL> select * from v$version ;
    
    BANNER
    ----------------------------------------------------------------
    Oracle Database 10g Release 10.2.0.5.0 - Production
    PL/SQL Release 10.2.0.5.0 - Production
    CORE    10.2.0.5.0      Production
    TNS for Linux: Version 10.2.0.5.0 - Production
    NLSRTL Version 10.2.0.5.0 - Production
    
    SQL> update /*+ gather_plan_statistics */ dtl set process_ind = 'PROCESSED'
      2  where dtlid in (
      3                  select /*+ LEADING(dtl hdr) USE_NL(hdr) INDEX(dtl DTL_IDX2) */ dtlid
      4                    from (
      5                          select dtl.dtlid
      6                            from dtl,
      7                                 hdr
      8                           where dtl.hdrid = hdr.hdrid(+)
      9                             and dtl.process_date < sysdate
     10                             and dtl.process_ind = 'NEW'
     11                             and dtl.cat_id = 1
     12                             and (
     13                                  (hdr_ind in (0, 2) and dtl.hdrid IS NOT NULL) OR (dtl.hdrid IS NULL)
     14                                 )
     15                           order by decode(dtl.type_id, 1, 99, dtl.type_id),dtl.process_date
     16                         )
     17                   where rownum <= 100
     18                 ) ;
    
    100 rows updated.
    
    SQL> select * from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST')) ;
    
    PLAN_TABLE_OUTPUT
    -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    SQL_ID  6y75aw5zny8gd, child number 0
    -------------------------------------
    update /*+ gather_plan_statistics */ dtl set process_ind = 'PROCESSED' where dtlid in (                 select /*+
    LEADING(dtl hdr) USE_NL(hdr) INDEX(dtl DTL_IDX2) */ dtlid                   from (                         select dtl.dtlid
                            from dtl,                                hdr                          where dtl.hdrid = hdr.hdrid(+)
                              and dtl.process_date < sysdate                            and dtl.process_ind = 'NEW'
                 and dtl.cat_id = 1                            and (                                 (hdr_ind in (0, 2) and
    dtl.hdrid IS NOT NULL) OR (dtl.hdrid IS NULL)                                )                          order by
    decode(dtl.type_id, 1, 99, dtl.type_id),dtl.process_date                        )                  where rownum <= 100
            )
    
    Plan hash value: 4241854096
    
    --------------------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                            | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
    --------------------------------------------------------------------------------------------------------------------------------------
    |   0 | UPDATE STATEMENT                     |          |      1 |        |      0 |00:00:00.02 |    1794 |       |       |          |
    |   1 |  UPDATE                              | DTL      |      1 |        |      0 |00:00:00.02 |    1794 |       |       |          |
    |   2 |   NESTED LOOPS                       |          |      1 |    100 |    100 |00:00:00.01 |     457 |       |       |          |
    |   3 |    VIEW                              | VW_NSO_1 |      1 |    100 |    100 |00:00:00.01 |     255 |       |       |          |
    |   4 |     SORT UNIQUE                      |          |      1 |    100 |    100 |00:00:00.01 |     255 | 73728 | 73728 |          |
    |*  5 |      COUNT STOPKEY                   |          |      1 |        |    100 |00:00:00.01 |     255 |       |       |          |
    |   6 |       VIEW                           |          |      1 |  63585 |    100 |00:00:00.01 |     255 |       |       |          |
    |*  7 |        SORT ORDER BY STOPKEY         |          |      1 |  63585 |    100 |00:00:00.01 |     255 |  9216 |  9216 | 8192  (0)|
    |*  8 |         FILTER                       |          |      1 |        |   2574 |00:00:00.01 |     255 |       |       |          |
    |*  9 |          HASH JOIN RIGHT OUTER       |          |      1 |  63585 |   6000 |00:00:00.02 |     255 |   968K|   968K| 1342K (0)|
    |  10 |           TABLE ACCESS FULL          | HDR      |      1 |  10000 |  10000 |00:00:00.01 |     190 |       |       |          |
    |* 11 |           TABLE ACCESS BY INDEX ROWID| DTL      |      1 |  95474 |   6000 |00:00:00.01 |      65 |       |       |          |
    |* 12 |            INDEX RANGE SCAN          | DTL_IDX1 |      1 |  95474 |   6000 |00:00:00.01 |      30 |       |       |          |
    |* 13 |    INDEX UNIQUE SCAN                 | DTL_PK   |    100 |      1 |    100 |00:00:00.01 |     202 |       |       |          |
    --------------------------------------------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       5 - filter(ROWNUM<=100)
       7 - filter(ROWNUM<=100)
       8 - filter(((INTERNAL_FUNCTION("HDR_IND") AND "DTL"."HDRID" IS NOT NULL) OR "DTL"."HDRID" IS NULL))
       9 - access("DTL"."HDRID"="HDR"."HDRID")
      11 - filter("DTL"."CAT_ID"=1)
      12 - access("DTL"."PROCESS_IND"='NEW' AND "DTL"."PROCESS_DATE"<SYSDATE@!)
      13 - access("DTLID"="$nso_col_1")
    
    
    43 rows selected.
  • 26. Re: UPDATE with ROWNUM (or TOP-N UPDATE)
    CharlesHooper Expert
    Currently Being Moderated
    Narendra,

    You have a "scope" problem with the hints. Where the hints are currently located in your SQL statement, there is no DTL or HDR table, only the resultset of an inline view. This is what you currently have:
    SQL> update /*+ gather_plan_statistics */ dtl set process_ind = 'PROCESSED'
      2  where dtlid in (
      3                  select /*+ LEADING(dtl hdr) USE_NL(hdr) INDEX(dtl DTL_IDX2) */ dtlid
      4                    from (
      5                          select dtl.dtlid
      6                            from dtl,
      7                                 hdr
      8                           where dtl.hdrid = hdr.hdrid(+)
      9                             and dtl.process_date < sysdate
     10                             and dtl.process_ind = 'NEW'
     11                             and dtl.cat_id = 1
     12                             and (
     13                                  (hdr_ind in (0, 2) and dtl.hdrid IS NOT NULL) OR (dtl.hdrid IS NULL)
     14                                 )
     15                           order by decode(dtl.type_id, 1, 99, dtl.type_id),dtl.process_date
     16                         )
     17                   where rownum <= 100
     18                 ) ;
    This is where the hints should be located:
    SQL> update /*+ gather_plan_statistics */ dtl set process_ind = 'PROCESSED'
      2  where dtlid in (
      3                  select  dtlid
      4                    from (
      5                          select /*+ LEADING(dtl hdr) USE_NL(hdr) INDEX(dtl DTL_IDX2) */ dtl.dtlid
      6                            from dtl,
      7                                 hdr
      8                           where dtl.hdrid = hdr.hdrid(+)
      9                             and dtl.process_date < sysdate
     10                             and dtl.process_ind = 'NEW'
     11                             and dtl.cat_id = 1
     12                             and (
     13                                  (hdr_ind in (0, 2) and dtl.hdrid IS NOT NULL) OR (dtl.hdrid IS NULL)
     14                                 )
     15                           order by decode(dtl.type_id, 1, 99, dtl.type_id),dtl.process_date
     16                         )
     17                   where rownum <= 100
     18                 ) ;
    Charles Hooper
    http://hoopercharles.wordpress.com/
    IT Manager/Oracle DBA
    K&M Machine-Fabricating, Inc.
  • 27. Re: UPDATE with ROWNUM (or TOP-N UPDATE)
    user503699 Expert
    Currently Being Moderated
    Charles Hooper wrote:
    Narendra,

    You have a "scope" problem with the hints. Where the hints are currently located in your SQL statement, there is no DTL or HDR table, only the resultset of an inline view. This is what you currently have:
    Charles,

    Apologies for not reviewing my work before asking question and thanks for pointing out the mistake.
    However, as I had noticed yesterday, I still get "even worse" (??) plan with hints for UPDATE on 11.2.0.1
    I will be able to give feedback about the impact on 10.2.0.5 later (But I have a feeling it will be same as below).
    update /*+ gather_plan_statistics */ dtl set process_ind = 'PROCESSED'
    where dtlid in (
                    select dtlid
                      from (
                            select /*+ LEADING(dtl hdr) USE_NL(hdr) INDEX(dtl DTL_IDX2) */ dtl.dtlid
                              from dtl,
                                   hdr
                             where dtl.hdrid = hdr.hdrid(+)
                               and dtl.process_date < sysdate
                               and dtl.process_ind = 'NEW'
                               and dtl.cat_id = 1
                               and (
                                    (hdr_ind in (0, 2) and dtl.hdrid IS NOT NULL) OR (dtl.hdrid IS NULL)
                                   )
                             order by decode(dtl.type_id, 1, 99, dtl.type_id),dtl.process_date
                           )
                     where rownum <= 100
     18                   ) ;
    
    100 rows updated.
    
    SQL> select * from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST')) ;
    
    PLAN_TABLE_OUTPUT
    -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    SQL_ID  fpq6q3bncgtu7, child number 0
    -------------------------------------
    update /*+ gather_plan_statistics */ dtl set process_ind = 'PROCESSED'
    where dtlid in (                 select dtlid                   from (
                           select /*+ LEADING(dtl hdr) USE_NL(hdr)
    INDEX(dtl DTL_IDX2) */ dtl.dtlid                           from dtl,
                                hdr                          where
    dtl.hdrid = hdr.hdrid(+)                            and
    dtl.process_date < sysdate                            and
    dtl.process_ind = 'NEW'                            and dtl.cat_id = 1
                             and (                                 (hdr_ind
    in (0, 2) and dtl.hdrid IS NOT NULL) OR (dtl.hdrid IS NULL)
                       )                          order by
    decode(dtl.type_id, 1, 99, dtl.type_id),dtl.process_date
            )                  where rownum <= 100                  )
    
    Plan hash value: 2804510156
    
    ------------------------------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                             | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
    ------------------------------------------------------------------------------------------------------------------------------------------------
    |   0 | UPDATE STATEMENT                      |          |      1 |        |      0 |00:00:07.80 |     114K|     65 |       |       |          |
    |   1 |  UPDATE                               | DTL      |      1 |        |      0 |00:00:07.80 |     114K|     65 |       |       |          |
    |   2 |   NESTED LOOPS                        |          |      1 |    100 |    100 |00:00:07.64 |     113K|     60 |       |       |          |
    |   3 |    VIEW                               | VW_NSO_1 |      1 |    100 |    100 |00:00:07.63 |     113K|      0 |       |       |          |
    |   4 |     SORT UNIQUE                       |          |      1 |    100 |    100 |00:00:07.63 |     113K|      0 | 73728 | 73728 |          |
    |*  5 |      COUNT STOPKEY                    |          |      1 |        |    100 |00:00:07.64 |     113K|      0 |       |       |          |
    |   6 |       VIEW                            |          |      1 |  79739 |    100 |00:00:07.64 |     113K|      0 |       |       |          |
    |*  7 |        SORT ORDER BY STOPKEY          |          |      1 |  79739 |    100 |00:00:07.63 |     113K|      0 | 11264 | 11264 |10240  (0)|
    |   8 |         CONCATENATION                 |          |      1 |        |  45536 |00:00:08.29 |     113K|      0 |       |       |          |
    |   9 |          NESTED LOOPS OUTER           |          |      1 |    239 |    212 |00:00:00.08 |     972 |      0 |       |       |          |
    |* 10 |           TABLE ACCESS BY INDEX ROWID | DTL      |      1 |    239 |    212 |00:00:00.07 |     972 |      0 |       |       |          |
    |* 11 |            INDEX RANGE SCAN           | DTL_IDX1 |      1 |    119K|    106K|00:00:01.92 |     342 |      0 |       |       |          |
    |  12 |           TABLE ACCESS BY INDEX ROWID | HDR      |    212 |      1 |      0 |00:00:00.01 |       0 |      0 |       |       |          |
    |* 13 |            INDEX UNIQUE SCAN          | HDR_PK   |    212 |      1 |      0 |00:00:00.01 |       0 |      0 |       |       |          |
    |* 14 |          FILTER                       |          |      1 |        |  45324 |00:00:07.48 |     112K|      0 |       |       |          |
    |  15 |           NESTED LOOPS OUTER          |          |      1 |  79500 |    105K|00:00:08.38 |     112K|      0 |       |       |          |
    |* 16 |            TABLE ACCESS BY INDEX ROWID| DTL      |      1 |    119K|    105K|00:00:00.88 |     972 |      0 |       |       |          |
    |* 17 |             INDEX RANGE SCAN          | DTL_IDX1 |      1 |    119K|    106K|00:00:00.59 |     342 |      0 |       |       |          |
    |  18 |            TABLE ACCESS BY INDEX ROWID| HDR      |    105K|      1 |    105K|00:00:05.30 |     111K|      0 |       |       |          |
    |* 19 |             INDEX UNIQUE SCAN         | HDR_PK   |    105K|      1 |    105K|00:00:01.99 |    5529 |      0 |       |       |          |
    |* 20 |    INDEX UNIQUE SCAN                  | DTL_PK   |    100 |      1 |    100 |00:00:00.07 |     202 |     60 |       |       |          |
    ------------------------------------------------------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       5 - filter(ROWNUM<=100)
       7 - filter(ROWNUM<=100)
      10 - filter(("DTL"."HDRID" IS NULL AND "DTL"."CAT_ID"=1))
      11 - access("DTL"."PROCESS_IND"='NEW' AND "DTL"."PROCESS_DATE"<SYSDATE@!)
      13 - access("DTL"."HDRID"="HDR"."HDRID")
      14 - filter(("HDR_IND"=0 OR "HDR_IND"=2))
      16 - filter(("DTL"."HDRID" IS NOT NULL AND "DTL"."CAT_ID"=1 AND LNNVL("DTL"."HDRID" IS NULL)))
      17 - access("DTL"."PROCESS_IND"='NEW' AND "DTL"."PROCESS_DATE"<SYSDATE@!)
      19 - access("DTL"."HDRID"="HDR"."HDRID")
      20 - access("DTLID"="DTLID")
    
    
    58 rows selected.
    
    SQL> rollback ;
    
    Rollback complete.
  • 28. Re: UPDATE with ROWNUM (or TOP-N UPDATE)
    CharlesHooper Expert
    Currently Being Moderated
    The CONCATENATION step is a sign of OR expansion. Checking the documentation:
    http://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements006.htm#SQLRF50502
    "The NO_EXPAND hint instructs the optimizer not to consider OR-expansion for queries having OR conditions or IN-lists in the WHERE clause. Usually, the optimizer considers using OR expansion and uses this method if it decides that the cost is lower than not using it."

    It appears that you need to use a NO_EXPAND hint along with the other hints.

    Here is an example that reproduces the problem that you are seeing - notice that I added a USE_CONCAT hint:
    update /*+ gather_plan_statistics */ dtl set process_ind = 'PROCESSED'
    where dtlid in (
                    select dtlid
                      from (
                            select /*+ USE_CONCAT LEADING(dtl hdr) USE_NL(hdr) INDEX(dtl DTL_IDX2) */ dtl.dtlid
                              from dtl,
                                   hdr
                             where dtl.hdrid = hdr.hdrid(+)
                               and dtl.process_date < sysdate
                               and dtl.process_ind = 'NEW'
                               and dtl.cat_id = 1
                               and (
                                    (hdr_ind in (0, 2) and dtl.hdrid IS NOT NULL) OR (dtl.hdrid IS NULL)
                                   )
                             order by decode(dtl.type_id, 1, 99, dtl.type_id),dtl.process_date
                           )
                     where rownum <= 100
                       ) ;
     
    select * from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST')) ;
     
    rollback;
    The output:
    SQL_ID  6gw28htu74var, child number 0
    -------------------------------------
    update /*+ gather_plan_statistics */ dtl set process_ind = 'PROCESSED'
    where dtlid in (                 select dtlid                   from (
                           select /*+ USE_CONCAT LEADING(dtl hdr)
    USE_NL(hdr) INDEX(dtl DTL_IDX2) */ dtl.dtlid
    from dtl,                                hdr
    where dtl.hdrid = hdr.hdrid(+)                            and
    dtl.process_date < sysdate                            and
    dtl.process_ind = 'NEW'                            and dtl.cat_id = 1
                             and (                                 (hdr_ind
    in (0, 2) and dtl.hdrid IS NOT NULL) OR (dtl.hdrid IS NULL)
                       )                          order by
    decode(dtl.type_id, 1, 99, dtl.type_id),dtl.process_date
            )                  where rownum <= 100                    )
     
    Plan hash value: 80420790
     
    ---------------------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                             | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
    ---------------------------------------------------------------------------------------------------------------------------------------
    |   0 | UPDATE STATEMENT                      |          |      1 |        |      0 |00:00:00.38 |   43039 |       |       |          |
    |   1 |  UPDATE                               | DTL      |      1 |        |      0 |00:00:00.38 |   43039 |       |       |          |
    |   2 |   NESTED LOOPS                        |          |      1 |    100 |    100 |00:00:00.38 |   41703 |       |       |          |
    |   3 |    VIEW                               | VW_NSO_1 |      1 |    100 |    100 |00:00:00.38 |   41616 |       |       |          |
    |   4 |     SORT UNIQUE                       |          |      1 |    100 |    100 |00:00:00.38 |   41616 | 73728 | 73728 |          |
    |*  5 |      COUNT STOPKEY                    |          |      1 |        |    100 |00:00:00.38 |   41616 |       |       |          |
    |   6 |       VIEW                            |          |      1 |  63619 |    100 |00:00:00.38 |   41616 |       |       |          |
    |*  7 |        SORT ORDER BY STOPKEY          |          |      1 |  63619 |    100 |00:00:00.38 |   41616 | 11264 | 11264 |10240  (0)|
    |   8 |         CONCATENATION                 |          |      1 |        |   2582 |00:00:00.01 |   41616 |       |       |          |
    |   9 |          NESTED LOOPS OUTER           |          |      1 |    191 |     12 |00:00:00.01 |   23160 |       |       |          |
    |* 10 |           TABLE ACCESS BY INDEX ROWID | DTL      |      1 |    191 |     12 |00:00:00.01 |   23160 |       |       |          |
    |* 11 |            INDEX RANGE SCAN           | DTL_IDX2 |      1 |  95524 |   6000 |00:00:00.01 |   23090 |       |       |          |
    |  12 |           TABLE ACCESS BY INDEX ROWID | HDR      |     12 |      1 |      0 |00:00:00.01 |    0 |          |       |          |
    |* 13 |            INDEX UNIQUE SCAN          | HDR_PK   |     12 |      1 |      0 |00:00:00.01 |    0 |          |       |          |
    |* 14 |          FILTER                       |          |      1 |        |   2570 |00:00:00.01 |   18456 |       |       |          |
    |  15 |           NESTED LOOPS OUTER          |          |      1 |  63428 |   5988 |00:00:00.01 |   18456 |       |       |          |
    |* 16 |            TABLE ACCESS BY INDEX ROWID| DTL      |      1 |  95142 |   5988 |00:00:00.01 |   12236 |       |       |          |
    |* 17 |             INDEX RANGE SCAN          | DTL_IDX2 |      1 |  95524 |   6000 |00:00:00.01 |   12166 |       |       |          |
    |  18 |            TABLE ACCESS BY INDEX ROWID| HDR      |   5988 |      1 |   5988 |00:00:00.01 |    6220 |       |       |          |
    |* 19 |             INDEX UNIQUE SCAN         | HDR_PK   |   5988 |      1 |   5988 |00:00:00.01 |     232 |       |       |          |
    |* 20 |    INDEX UNIQUE SCAN                  | DTL_PK   |    100 |      1 |    100 |00:00:00.01 |   87 |          |       |          |
    ---------------------------------------------------------------------------------------------------------------------------------------
     
    Predicate Information (identified by operation id):
    ---------------------------------------------------
       5 - filter(ROWNUM<=100)
       7 - filter(ROWNUM<=100)
      10 - filter(("DTL"."HDRID" IS NULL AND "DTL"."CAT_ID"=1))
      11 - access("DTL"."PROCESS_IND"='NEW' AND "DTL"."PROCESS_DATE"<SYSDATE@!)
           filter("DTL"."PROCESS_DATE"<SYSDATE@!)
      13 - access("DTL"."HDRID"="HDR"."HDRID")
      14 - filter(("HDR_IND"=0 OR "HDR_IND"=2))
      16 - filter(("DTL"."HDRID" IS NOT NULL AND "DTL"."CAT_ID"=1 AND LNNVL("DTL"."HDRID" IS NULL)))
      17 - access("DTL"."PROCESS_IND"='NEW' AND "DTL"."PROCESS_DATE"<SYSDATE@!)
           filter("DTL"."PROCESS_DATE"<SYSDATE@!)
      19 - access("DTL"."HDRID"="HDR"."HDRID")
      20 - access("DTLID"="DTLID")
    Now, to get rid of the CONCATENATION operation (assuming that I was seeing it), I would place a NO_EXPAND hint (in place of where I currently have a USE_CONCAT hint):
    update /*+ gather_plan_statistics */ dtl set process_ind = 'PROCESSED'
    where dtlid in (
                    select dtlid
                      from (
                            select /*+ NO_EXPAND LEADING(dtl hdr) USE_NL(hdr) INDEX(dtl DTL_IDX2) */ dtl.dtlid
                              from dtl,
                                   hdr
                             where dtl.hdrid = hdr.hdrid(+)
                               and dtl.process_date < sysdate
                               and dtl.process_ind = 'NEW'
                               and dtl.cat_id = 1
                               and (
                                    (hdr_ind in (0, 2) and dtl.hdrid IS NOT NULL) OR (dtl.hdrid IS NULL)
                                   )
                             order by decode(dtl.type_id, 1, 99, dtl.type_id),dtl.process_date
                           )
                     where rownum <= 100
                       ) ;
     
    select * from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST')) ;
     
    rollback;
    The output:
    SQL_ID  b4d0ysvbgsf5q, child number 0
    -------------------------------------
    update /*+ gather_plan_statistics */ dtl set process_ind = 'PROCESSED'
    where dtlid in (                 select dtlid                   from (
                           select /*+ NO_EXPAND LEADING(dtl hdr)
    USE_NL(hdr) INDEX(dtl DTL_IDX2) */ dtl.dtlid
    from dtl,                                hdr
    where dtl.hdrid = hdr.hdrid(+)                            and
    dtl.process_date < sysdate                            and
    dtl.process_ind = 'NEW'                            and dtl.cat_id = 1
                             and (                                 (hdr_ind
    in (0, 2) and dtl.hdrid IS NOT NULL) OR (dtl.hdrid IS NULL)
                       )                          order by
    decode(dtl.type_id, 1, 99, dtl.type_id),dtl.process_date
            )                  where rownum <= 100                    )
     
    Plan hash value: 3052733514
     
    -------------------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                           | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
    -------------------------------------------------------------------------------------------------------------------------------------
    |   0 | UPDATE STATEMENT                    |          |      1 |        |      0 |00:00:00.01 |    1843 |       |       |          |
    |   1 |  UPDATE                             | DTL      |      1 |        |      0 |00:00:00.01 |    1843 |       |       |          |
    |   2 |   NESTED LOOPS                      |          |      1 |    100 |    100 |00:00:00.01 |     505 |       |       |          |
    |   3 |    VIEW                             | VW_NSO_1 |      1 |    100 |    100 |00:00:00.01 |     418 |       |       |          |
    |   4 |     SORT UNIQUE                     |          |      1 |    100 |    100 |00:00:00.01 |     418 | 73728 | 73728 |          |
    |*  5 |      COUNT STOPKEY                  |          |      1 |        |    100 |00:00:00.01 |     418 |       |       |          |
    |   6 |       VIEW                          |          |      1 |  63619 |    100 |00:00:00.01 |     418 |       |       |          |
    |*  7 |        FILTER                       |          |      1 |        |    100 |00:00:00.01 |     418 |       |       |          |
    |   8 |         NESTED LOOPS OUTER          |          |      1 |  63619 |    211 |00:00:00.01 |     418 |       |       |          |
    |*  9 |          TABLE ACCESS BY INDEX ROWID| DTL      |      1 |  95524 |    211 |00:00:00.01 |     155 |       |       |          |
    |* 10 |           INDEX RANGE SCAN          | DTL_IDX2 |      1 |  95524 |    211 |00:00:00.01 |     119 |       |       |          |
    |  11 |          TABLE ACCESS BY INDEX ROWID| HDR      |    211 |      1 |    199 |00:00:00.01 |     263 |       |       |          |
    |* 12 |           INDEX UNIQUE SCAN         | HDR_PK   |    211 |      1 |    199 |00:00:00.01 |      64 |       |       |          |
    |* 13 |    INDEX UNIQUE SCAN                | DTL_PK   |    100 |      1 |    100 |00:00:00.01 |      87 |       |       |          |
    -------------------------------------------------------------------------------------------------------------------------------------
     
    Predicate Information (identified by operation id):
    ---------------------------------------------------
       5 - filter(ROWNUM<=100)
       7 - filter(((INTERNAL_FUNCTION("HDR_IND") AND "DTL"."HDRID" IS NOT NULL) OR "DTL"."HDRID" IS NULL))
       9 - filter("DTL"."CAT_ID"=1)
      10 - access("DTL"."PROCESS_IND"='NEW' AND "DTL"."PROCESS_DATE"<SYSDATE@!)
           filter("DTL"."PROCESS_DATE"<SYSDATE@!)
      12 - access("DTL"."HDRID"="HDR"."HDRID")
      13 - access("DTLID"="DTLID")
    Charles Hooper
    http://hoopercharles.wordpress.com/
    IT Manager/Oracle DBA
    K&M Machine-Fabricating, Inc.
  • 29. Re: UPDATE with ROWNUM (or TOP-N UPDATE)
    user503699 Expert
    Currently Being Moderated
    Charles Hooper wrote:
    It appears that you need to use a NO_EXPAND hint along with the other hints.
    Charles,

    While NO_EXPAND eliminates the concatenation, it still "ignores" other hints like LEADING, USE_NL and INDEX and generates plan same as the one with no hints.
    Could it be a bug in the 11.2.0.1 version?
    update /*+ gather_plan_statistics */ dtl set process_ind = 'PROCESSED'
    where dtlid in (
                    select /*+ NO_EXPAND LEADING(dtl hdr) USE_NL(hdr) INDEX(dtl DTL_IDX2) */ dtlid
                      from (
                            select dtl.dtlid
                              from dtl,
                                   hdr
                             where dtl.hdrid = hdr.hdrid(+)
                               and dtl.process_date < sysdate
                               and dtl.process_ind = 'NEW'
                               and dtl.cat_id = 1
                               and (
                                    (hdr_ind in (0, 2) and dtl.hdrid IS NOT NULL) OR (dtl.hdrid IS NULL)
                                   )
                             order by decode(dtl.type_id, 1, 99, dtl.type_id),dtl.process_date
                           )
                     where rownum <= 100
     18                 ) ;
    
    100 rows updated.
    
    SQL> select * from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST')) ;
    
    PLAN_TABLE_OUTPUT
    -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    SQL_ID  3sk9ryscfkjfn, child number 0
    -------------------------------------
    update /*+ gather_plan_statistics */ dtl set process_ind = 'PROCESSED'
    where dtlid in (                 select /*+ NO_EXPAND LEADING(dtl hdr)
    USE_NL(hdr) INDEX(dtl DTL_IDX2) */ dtlid                   from (
                      select dtl.dtlid                           from dtl,
                                  hdr                          where
    dtl.hdrid = hdr.hdrid(+)                            and
    dtl.process_date < sysdate                            and
    dtl.process_ind = 'NEW'                            and dtl.cat_id = 1
                             and (                                 (hdr_ind
    in (0, 2) and dtl.hdrid IS NOT NULL) OR (dtl.hdrid IS NULL)
                       )                          order by
    decode(dtl.type_id, 1, 99, dtl.type_id),dtl.process_date
            )                  where rownum <= 100                )
    
    Plan hash value: 4241854096
    
    --------------------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                            | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
    --------------------------------------------------------------------------------------------------------------------------------------
    |   0 | UPDATE STATEMENT                     |          |      1 |        |      0 |00:00:00.29 |    2739 |       |       |          |
    |   1 |  UPDATE                              | DTL      |      1 |        |      0 |00:00:00.29 |    2739 |       |       |          |
    |   2 |   NESTED LOOPS                       |          |      1 |    100 |    100 |00:00:00.28 |    1364 |       |       |          |
    |   3 |    VIEW                              | VW_NSO_1 |      1 |    100 |    100 |00:00:00.27 |    1162 |       |       |          |
    |   4 |     SORT UNIQUE                      |          |      1 |    100 |    100 |00:00:00.27 |    1162 | 73728 | 73728 |          |
    |*  5 |      COUNT STOPKEY                   |          |      1 |        |    100 |00:00:00.27 |    1162 |       |       |          |
    |   6 |       VIEW                           |          |      1 |  86394 |    100 |00:00:00.27 |    1162 |       |       |          |
    |*  7 |        SORT ORDER BY STOPKEY         |          |      1 |  86394 |    100 |00:00:00.27 |    1162 | 13312 | 13312 |12288  (0)|
    |*  8 |         FILTER                       |          |      1 |        |  45536 |00:00:01.21 |    1162 |       |       |          |
    |*  9 |          HASH JOIN RIGHT OUTER       |          |      1 |  86394 |    106K|00:00:08.68 |    1162 |   968K|   968K| 1364K (0)|
    |  10 |           TABLE ACCESS FULL          | HDR      |      1 |  10000 |  10000 |00:00:00.17 |     190 |       |       |          |
    |* 11 |           TABLE ACCESS BY INDEX ROWID| DTL      |      1 |    129K|    106K|00:00:02.75 |     972 |       |       |          |
    |* 12 |            INDEX RANGE SCAN          | DTL_IDX1 |      1 |    129K|    106K|00:00:01.03 |     342 |       |       |          |
    |* 13 |    INDEX UNIQUE SCAN                 | DTL_PK   |    100 |      1 |    100 |00:00:00.01 |     202 |       |       |          |
    --------------------------------------------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       5 - filter(ROWNUM<=100)
       7 - filter(ROWNUM<=100)
       8 - filter(((INTERNAL_FUNCTION("HDR_IND") AND "DTL"."HDRID" IS NOT NULL) OR "DTL"."HDRID" IS NULL))
       9 - access("DTL"."HDRID"="HDR"."HDRID")
      11 - filter("DTL"."CAT_ID"=1)
      12 - access("DTL"."PROCESS_IND"='NEW' AND "DTL"."PROCESS_DATE"<SYSDATE@!)
      13 - access("DTLID"="DTLID")
    
    
    48 rows selected.
    p.s. I am actually surprised that CBO manages to ignore 3 hints at the same time and picks up different index than the one mentioned in the hint.

    Edited by: user503699 on Jul 3, 2012 2:38 AM

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points