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
  • 30. Re: UPDATE with ROWNUM (or TOP-N UPDATE)
    CharlesHooper Expert
    Currently Being Moderated
    Narendra,

    The optimizer did NOT obey 1 hint while ignoring the other 3 hints.

    Repeating what I stated earlier:
    "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:"

    I think that you will know what to do when you read the above message. :-)

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

    The optimizer did NOT obey 1 hint while ignoring the other 3 hints.

    Repeating what I stated earlier:
    "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:"

    I think that you will know what to do when you read the above message. :-)
    Charles,

    Once again, a BIG THANK YOU for your patience and my sincere apologies for the oversight.
    I could get the desired plan, along with expected short-circuit processing that I was hoping to see.
  • 32. 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,

    Unless I am out of my mind (which is quite possible), I am not sure if the increase in number of consistent gets is ONLY due to growing index size.
    Here is my stats output for FIRST UPDATE:
    SQL> 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
                   ) ;
      5    6    7    8    9   10   11   12   13   14   15   16   17   18  
    100 rows updated.
    
    SQL> select * from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST')) ;
    
    PLAN_TABLE_OUTPUT
    -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    SQL_ID  4gdgwvdd08gga, 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.04 |    1954 |       |       |          |
    |   1 |  UPDATE                             | DTL      |      1 |        |      0 |00:00:00.04 |    1954 |       |       |          |
    |   2 |   NESTED LOOPS                      |          |      1 |    100 |    100 |00:00:00.02 |     579 |       |       |          |
    |   3 |    VIEW                             | VW_NSO_1 |      1 |    100 |    100 |00:00:00.02 |     377 |       |       |          |
    |   4 |     SORT UNIQUE                     |          |      1 |    100 |    100 |00:00:00.02 |     377 | 73728 | 73728 |          |
    |*  5 |      COUNT STOPKEY                  |          |      1 |        |    100 |00:00:00.02 |     377 |       |       |          |
    |   6 |       VIEW                          |          |      1 |  90017 |    100 |00:00:00.01 |     377 |       |       |          |
    |*  7 |        FILTER                       |          |      1 |        |    100 |00:00:00.01 |     377 |       |       |          |
    |   8 |         NESTED LOOPS OUTER          |          |      1 |  90017 |    169 |00:00:00.02 |     377 |       |       |          |
    |*  9 |          TABLE ACCESS BY INDEX ROWID| DTL      |      1 |    135K|    169 |00:00:00.01 |     103 |       |       |          |
    |* 10 |           INDEX RANGE SCAN          | DTL_IDX2 |      1 |    135K|    169 |00:00:00.01 |       3 |       |       |          |
    |  11 |          TABLE ACCESS BY INDEX ROWID| HDR      |    169 |      1 |    135 |00:00:00.01 |     274 |       |       |          |
    |* 12 |           INDEX UNIQUE SCAN         | HDR_PK   |    169 |      1 |    135 |00:00:00.01 |     139 |       |       |          |
    |* 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(((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")
    
    
    48 rows selected.
    And after I executed this UPDATE for 7 times, each execution followed by COMMIT, the 8th execution generated following stats output:
    SQL> 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
     18                 ) ;
    
    100 rows updated.
    
    SQL> select * from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST')) ;
    
    PLAN_TABLE_OUTPUT
    -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    SQL_ID  4gdgwvdd08gga, 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 | Reads  |  OMem |  1Mem | Used-Mem |
    ----------------------------------------------------------------------------------------------------------------------------------------------
    |   0 | UPDATE STATEMENT                    |          |      1 |        |      0 |00:00:00.12 |    3217 |      2 |       |       |          |
    |   1 |  UPDATE                             | DTL      |      1 |        |      0 |00:00:00.12 |    3217 |      2 |       |       |          |
    |   2 |   NESTED LOOPS                      |          |      1 |    100 |    100 |00:00:00.16 |    1846 |      1 |       |       |          |
    |   3 |    VIEW                             | VW_NSO_1 |      1 |    100 |    100 |00:00:00.10 |    1832 |      1 |       |       |          |
    |   4 |     SORT UNIQUE                     |          |      1 |    100 |    100 |00:00:00.10 |    1832 |      1 | 73728 | 73728 |          |
    |*  5 |      COUNT STOPKEY                  |          |      1 |        |    100 |00:00:00.10 |    1832 |      1 |       |       |          |
    |   6 |       VIEW                          |          |      1 |  90017 |    100 |00:00:00.10 |    1832 |      1 |       |       |          |
    |*  7 |        FILTER                       |          |      1 |        |    100 |00:00:00.10 |    1832 |      1 |       |       |          |
    |   8 |         NESTED LOOPS OUTER          |          |      1 |  90017 |    841 |00:00:00.09 |    1832 |      1 |       |       |          |
    |*  9 |          TABLE ACCESS BY INDEX ROWID| DTL      |      1 |    135K|    841 |00:00:00.02 |     516 |      1 |       |       |          |
    |* 10 |           INDEX RANGE SCAN          | DTL_IDX2 |      1 |    135K|    841 |00:00:00.01 |     107 |      1 |       |       |          |
    |  11 |          TABLE ACCESS BY INDEX ROWID| HDR      |    841 |      1 |    841 |00:00:00.07 |    1316 |      0 |       |       |          |
    |* 12 |           INDEX UNIQUE SCAN         | HDR_PK   |    841 |      1 |    841 |00:00:00.04 |     475 |      0 |       |       |          |
    |* 13 |    INDEX UNIQUE SCAN                | DTL_PK   |    100 |      1 |    100 |00:00:00.01 |      14 |      0 |       |       |          |
    ----------------------------------------------------------------------------------------------------------------------------------------------
    
    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")
    
    
    48 rows selected.
    If we look at FIRST UPDATE stats, we will notice that the INDEX RANGE_SCAN (and corresponding TABLE ACCESS BY INDEX ROWID) needed to get only 169 rows (from DTL table) in order to satisfy the query and these rows could be found by doing only 103 I/Os (3 for INDEX RANGE SCAN and 100 for corresponding TABLE ACCESS BY INDEX ROWID).
    However, from 8th UPDATE stats, we will notice that the INDEX RANGE_SCAN (and corresponding TABLE ACCESS BY INDEX ROWID) needed to get 841 rows (from DTL table) in order to satisfy the query and these rows could be found by doing 516 I/Os (107 for INDEX RANGE SCAN and 409 for corresponding TABLE ACCESS BY INDEX ROWID).
    Could the underlying data distribution be also affecting the number of I/Os performed ?
    BTW, at this stage (during 8th UPDATE), the non-hinted version of UPDATE generated less I/Os than the hinted one but still needed more time to complete (0.30 vs. 0.12) and did one SORT more than the hinted version (3 vs. 2)
    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 1
    -------------------------------------
    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 |  OMem |  1Mem | Used-Mem |
    --------------------------------------------------------------------------------------------------------------------------------------
    |   0 | UPDATE STATEMENT                     |          |      1 |        |      0 |00:00:00.30 |    2490 |       |       |          |
    |   1 |  UPDATE                              | DTL      |      1 |        |      0 |00:00:00.30 |    2490 |       |       |          |
    |   2 |   NESTED LOOPS                       |          |      1 |    100 |    100 |00:00:00.29 |    1176 |       |       |          |
    |   3 |    VIEW                              | VW_NSO_1 |      1 |    100 |    100 |00:00:00.29 |    1162 |       |       |          |
    |   4 |     SORT UNIQUE                      |          |      1 |    100 |    100 |00:00:00.29 |    1162 | 73728 | 73728 |          |
    |*  5 |      COUNT STOPKEY                   |          |      1 |        |    100 |00:00:00.29 |    1162 |       |       |          |
    |   6 |       VIEW                           |          |      1 |  89952 |    100 |00:00:00.29 |    1162 |       |       |          |
    |*  7 |        SORT ORDER BY STOPKEY         |          |      1 |  89952 |    100 |00:00:00.29 |    1162 |  9216 |  9216 | 8192  (0)|
    |*  8 |         FILTER                       |          |      1 |        |  44836 |00:00:00.83 |    1162 |       |       |          |
    |*  9 |          HASH JOIN RIGHT OUTER       |          |      1 |  89952 |    105K|00:00:05.68 |    1162 |   968K|   968K| 1388K (0)|
    |  10 |           TABLE ACCESS FULL          | HDR      |      1 |  10000 |  10000 |00:00:00.09 |     190 |       |       |          |
    |* 11 |           TABLE ACCESS BY INDEX ROWID| DTL      |      1 |    135K|    105K|00:00:01.54 |     972 |       |       |          |
    |* 12 |            INDEX RANGE SCAN          | DTL_IDX1 |      1 |    135K|    105K|00:00:00.54 |     342 |       |       |          |
    |* 13 |    INDEX UNIQUE SCAN                 | DTL_PK   |    100 |      1 |    100 |00:00:00.01 |      14 |       |       |          |
    --------------------------------------------------------------------------------------------------------------------------------------
    
    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.
    Edited by: user503699 on Jul 3, 2012 4:30 AM - Corrected mention of I/O counts.
  • 33. Re: UPDATE with ROWNUM (or TOP-N UPDATE)
    CharlesHooper Expert
    Currently Being Moderated
    Narendra,

    Your analysis of an increasing number of rows needing to be fetched from the DTL table for each execution of the UPDATE statement is correct (I think that I mentioned this issue early in this thread). So, what is happening?

    First, let's make it easy for people to create your latest version of the test case tables:
    drop table dtl purge ;
    drop table hdr purge ;
    create table hdr (hdrid number(10), hdr_ind number(2) not null, hpad varchar2(100), constraint hdr_pk primary key (hdrid)) ;
    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)) ;
    create index dtl_idx1 on dtl(process_ind, process_date) nologging ;
    create index dtl_idx2 on dtl(process_ind, decode(type_id, 1, 99, type_id), process_date) nologging ;
    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 ;
    commit ;
    drop sequence dtl_seq ;
    create sequence dtl_seq cache 1000 ;
    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) ;
    commit ;
     
    begin
    for i in (select level rn from dual connect by level <= 20 order by dbms_random.random)
    loop
    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) ;
    commit;
    end loop;
    end;
    /
     
    exec dbms_stats.gather_table_stats(user, 'DTL', cascade=>true);
    exec dbms_stats.gather_table_stats(user, 'HDR', cascade=>true);
    With those tables freshly created, we can start experimenting. What if we execute the update statement 20 times, outputting the execution plan each time, and executing the following SQL statement before the first update statement, and then after every 5 executions of the update statement:
    select /*+ gather_plan_statistics */
      V.*,
      COUNT(decode(INCLUDE,'Y','Y',NULL)) OVER (ORDER BY ROWNUM) C,
      ROWNUM RN
    from
    (    select /*+ NO_EXPAND LEADING(dtl hdr) USE_NL(hdr) INDEX(dtl DTL_IDX2) */
     dtl.dtlid,
      hdr_ind,
      dtl.hdrid,
      dtl.cat_id,
      dtl.type_id,
      DTL.process_date,
      decode(dtl.hdrid,null,'Y',decode(hdr.hdr_ind,0,'Y',2,'Y','N')) INCLUDE
        from dtl,
             hdr
        where dtl.hdrid = hdr.hdrid(+)
              and dtl.process_date < sysdate
              and dtl.process_ind = 'NEW'
              AND dtl.cat_id = 1
        order by decode(dtl.type_id, 1, 99, dtl.type_id),dtl.process_date) V
    where
      rownum<=2800;
    The above output will show you the rows that are making it out of the DTL table, which are then used to probe the primary key index on the HDR table. The C column indicates the number of rows that match the WHERE clause in your UPDATE statement, after those rows are pulled from the DTL table (once the C column reaches 100, the Oracle runtime engine is able to short-circuit the execution plan). The RN column provides a running counter of the number of rows from the DTL table.

    Before the first update, notice that the first row that will be returned (based on your WHERE clause) is the first row printed, and that the 100th row that will be returned is the 211th row that is printed:
         DTLID    HDR_IND      HDRID     CAT_ID    TYPE_ID PROCESS_D I          C         RN
    ---------- ---------- ---------- ---------- ---------- --------- - ---------- ----------
           600          0        600          1          2 03-JUL-12 Y          1          1
           700          2        700          1          2 03-JUL-12 Y          2          2
           800          6        800          1          2 03-JUL-12 N          2          3
           100          6        100          1          2 03-JUL-12 N          2          4
           200          6        200          1          2 03-JUL-12 N          2          5
           300          0        300          1          2 03-JUL-12 Y          3          6
           400          6        400          1          2 03-JUL-12 N          3          7
    ...
           669          0        669          1          1 03-JUL-12 Y         98        205
           670          6        670          1          1 03-JUL-12 N         98        206
           671          6        671          1          1 03-JUL-12 N         98        207
           672          0        672          1          1 03-JUL-12 Y         99        208
           673          6        673          1          1 03-JUL-12 N         99        209
           674          6        674          1          1 03-JUL-12 N         99        210
           675          0        675          1          1 03-JUL-12 Y        100        211
    The execution plan, notice the A-Rows value for the DTL table is 211, the same value as the RN value above.
    SQL_ID  a2g3h48p54pa2, 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 |    1863 |       |       |          |       
    |   1 |  UPDATE                             | DTL      |      1 |        |      0 |00:00:00.01 |    1863 |       |       |          |       
    |   2 |   NESTED LOOPS                      |          |      1 |    100 |    100 |00:00:00.01 |     502 |       |       |          |       
    |   3 |    VIEW                             | VW_NSO_1 |      1 |    100 |    100 |00:00:00.01 |     415 |       |       |          |       
    |   4 |     SORT UNIQUE                     |          |      1 |    100 |    100 |00:00:00.01 |     415 | 73728 | 73728 |          |       
    |*  5 |      COUNT STOPKEY                  |          |      1 |        |    100 |00:00:00.01 |     415 |       |       |          |       
    |   6 |       VIEW                          |          |      1 |  63619 |    100 |00:00:00.01 |     415 |       |       |          |       
    |*  7 |        FILTER                       |          |      1 |        |    100 |00:00:00.01 |     415 |       |       |          |       
    |   8 |         NESTED LOOPS OUTER          |          |      1 |  63619 |    211 |00:00:00.01 |     415 |       |       |          |       
    |*  9 |          TABLE ACCESS BY INDEX ROWID| DTL      |      1 |  95524 |    211 |00:00:00.01 |     152 |       |       |          |       
    |* 10 |           INDEX RANGE SCAN          | DTL_IDX2 |      1 |  95524 |    211 |00:00:00.01 |     116 |       |       |          |       
    |  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")      
    Before the sixth execution - notice that the C column does not advance from 0 until the 645th row, and then runs for about 230 rows until hitting the 100th row that will be returned:
         DTLID    HDR_IND      HDRID     CAT_ID    TYPE_ID PROCESS_D I          C         RN
    ---------- ---------- ---------- ---------- ---------- --------- - ---------- ----------
          1093          6       1093          1          1 03-JUL-12 N          0        643
          1094          6       1094          1          1 03-JUL-12 N          0        644
          1095          0       1095          1          1 03-JUL-12 Y          1        645
          1096          6       1096          1          1 03-JUL-12 N          1        646
          1097          6       1097          1          1 03-JUL-12 N          1        647
          1098          0       1098          1          1 03-JUL-12 Y          2        648
          1099          2       1099          1          1 03-JUL-12 Y          3        649
          1101          0       1101          1          1 03-JUL-12 Y          4        650
    ...
          1840          0       1269          1          1 03-JUL-12 Y         98        868
          1841          6       1270          1          1 03-JUL-12 N         98        869
          1842          6       1271          1          1 03-JUL-12 N         98        870
          1843          0       1272          1          1 03-JUL-12 Y         99        871
          1844          6       1273          1          1 03-JUL-12 N         99        872
          1845          2       1274          1          1 03-JUL-12 Y        100        873
    The execution plan for the 6th execution of the update statement, notice that the DTL table is returning 873 rows, just as the above predicted:
    -------------------------------------------------------------------------------------------------------------------------------------       
    | Id  | Operation                           | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |       
    -------------------------------------------------------------------------------------------------------------------------------------       
    |   0 | UPDATE STATEMENT                    |          |      1 |        |      0 |00:00:00.01 |    2482 |       |       |          |       
    |   1 |  UPDATE                             | DTL      |      1 |        |      0 |00:00:00.01 |    2482 |       |       |          |       
    |   2 |   NESTED LOOPS                      |          |      1 |    100 |    100 |00:00:00.01 |    1134 |       |       |          |       
    |   3 |    VIEW                             | VW_NSO_1 |      1 |    100 |    100 |00:00:00.01 |    1096 |       |       |          |       
    |   4 |     SORT UNIQUE                     |          |      1 |    100 |    100 |00:00:00.01 |    1096 | 73728 | 73728 |          |       
    |*  5 |      COUNT STOPKEY                  |          |      1 |        |    100 |00:00:00.01 |    1096 |       |       |          |       
    |   6 |       VIEW                          |          |      1 |  63619 |    100 |00:00:00.01 |    1096 |       |       |          |       
    |*  7 |        FILTER                       |          |      1 |        |    100 |00:00:00.01 |    1096 |       |       |          |       
    |   8 |         NESTED LOOPS OUTER          |          |      1 |  63619 |    873 |00:00:00.01 |    1096 |       |       |          |       
    |*  9 |          TABLE ACCESS BY INDEX ROWID| DTL      |      1 |  95524 |    873 |00:00:00.01 |     155 |       |       |          |       
    |* 10 |           INDEX RANGE SCAN          | DTL_IDX2 |      1 |  95524 |    873 |00:00:00.01 |     123 |       |       |          |       
    |  11 |          TABLE ACCESS BY INDEX ROWID| HDR      |    873 |      1 |    873 |00:00:00.01 |     941 |       |       |          |       
    |* 12 |           INDEX UNIQUE SCAN         | HDR_PK   |    873 |      1 |    873 |00:00:00.01 |      68 |       |       |          |       
    |* 13 |    INDEX UNIQUE SCAN                | DTL_PK   |    100 |      1 |    100 |00:00:00.01 |      38 |       |       |          |       
    -------------------------------------------------------------------------------------------------------------------------------------       
     
    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")      
    Just before the 11th update, the first row returned will begin with the 1312th row printed, and end with the 1544th row printed (again running for about 230 rows once the first usable row is found):
         DTLID    HDR_IND      HDRID     CAT_ID    TYPE_ID PROCESS_D I          C         RN
    ---------- ---------- ---------- ---------- ---------- --------- - ---------- ----------
          2791          6       2791          1          1 03-JUL-12 N          0       1310
          2792          6       2792          1          1 03-JUL-12 N          0       1311
          2793          0       2793          1          1 03-JUL-12 Y          1       1312
          2794          6       2794          1          1 03-JUL-12 N          1       1313
          2795          6       2795          1          1 03-JUL-12 N          1       1314
          2796          0       2796          1          1 03-JUL-12 Y          2       1315
          2797          6       2797          1          1 03-JUL-12 N          2       1316
          2798          6       2798          1          1 03-JUL-12 N          2       1317
    ...
          3024          0         24          1          1 03-JUL-12 Y         98       1540
          3025          6         25          1          1 03-JUL-12 N         98       1541
          3026          6         26          1          1 03-JUL-12 N         98       1542
          3027          0         27          1          1 03-JUL-12 Y         99       1543
          3028          2         28          1          1 03-JUL-12 Y        100       1544
    You can probably guess what the execution plan looks like at this point:
    SQL_ID  a2g3h48p54pa2, 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 |    3172 |       |       |          |       
    |   1 |  UPDATE                             | DTL      |      1 |        |      0 |00:00:00.01 |    3172 |       |       |          |       
    |   2 |   NESTED LOOPS                      |          |      1 |    100 |    100 |00:00:00.01 |    1841 |       |       |          |       
    |   3 |    VIEW                             | VW_NSO_1 |      1 |    100 |    100 |00:00:00.01 |    1815 |       |       |          |       
    |   4 |     SORT UNIQUE                     |          |      1 |    100 |    100 |00:00:00.01 |    1815 | 73728 | 73728 |          |       
    |*  5 |      COUNT STOPKEY                  |          |      1 |        |    100 |00:00:00.01 |    1815 |       |       |          |       
    |   6 |       VIEW                          |          |      1 |  63619 |    100 |00:00:00.01 |    1815 |       |       |          |       
    |*  7 |        FILTER                       |          |      1 |        |    100 |00:00:00.01 |    1815 |       |       |          |       
    |   8 |         NESTED LOOPS OUTER          |          |      1 |  63619 |   1544 |00:00:00.01 |    1815 |       |       |          |       
    |*  9 |          TABLE ACCESS BY INDEX ROWID| DTL      |      1 |  95524 |   1544 |00:00:00.01 |     168 |       |       |          |       
    |* 10 |           INDEX RANGE SCAN          | DTL_IDX2 |      1 |  95524 |   1544 |00:00:00.01 |     129 |       |       |          |       
    |  11 |          TABLE ACCESS BY INDEX ROWID| HDR      |   1544 |      1 |   1544 |00:00:00.01 |    1647 |       |       |          |       
    |* 12 |           INDEX UNIQUE SCAN         | HDR_PK   |   1544 |      1 |   1544 |00:00:00.01 |     103 |       |       |          |       
    |* 13 |    INDEX UNIQUE SCAN                | DTL_PK   |    100 |      1 |    100 |00:00:00.01 |      26 |       |       |          |       
    -------------------------------------------------------------------------------------------------------------------------------------       
     
    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")      
    The execution plan for the 20th update:
    -------------------------------------------------------------------------------------------------------------------------------------       
    | Id  | Operation                           | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |       
    -------------------------------------------------------------------------------------------------------------------------------------       
    |   0 | UPDATE STATEMENT                    |          |      1 |        |      0 |00:00:00.01 |    4468 |       |       |          |       
    |   1 |  UPDATE                             | DTL      |      1 |        |      0 |00:00:00.01 |    4468 |       |       |          |       
    |   2 |   NESTED LOOPS                      |          |      1 |    100 |    100 |00:00:00.01 |    3120 |       |       |          |       
    |   3 |    VIEW                             | VW_NSO_1 |      1 |    100 |    100 |00:00:00.01 |    3094 |       |       |          |       
    |   4 |     SORT UNIQUE                     |          |      1 |    100 |    100 |00:00:00.01 |    3094 | 73728 | 73728 |          |       
    |*  5 |      COUNT STOPKEY                  |          |      1 |        |    100 |00:00:00.01 |    3094 |       |       |          |       
    |   6 |       VIEW                          |          |      1 |  63619 |    100 |00:00:00.01 |    3094 |       |       |          |       
    |*  7 |        FILTER                       |          |      1 |        |    100 |00:00:00.01 |    3094 |       |       |          |       
    |   8 |         NESTED LOOPS OUTER          |          |      1 |  63619 |   2743 |00:00:00.01 |    3094 |       |       |          |       
    |*  9 |          TABLE ACCESS BY INDEX ROWID| DTL      |      1 |  95524 |   2743 |00:00:00.01 |     192 |       |       |          |       
    |* 10 |           INDEX RANGE SCAN          | DTL_IDX2 |      1 |  95524 |   2743 |00:00:00.01 |     141 |       |       |          |       
    |  11 |          TABLE ACCESS BY INDEX ROWID| HDR      |   2743 |      1 |   2743 |00:00:00.01 |    2902 |       |       |          |       
    |* 12 |           INDEX UNIQUE SCAN         | HDR_PK   |   2743 |      1 |   2743 |00:00:00.01 |     159 |       |       |          |       
    |* 13 |    INDEX UNIQUE SCAN                | DTL_PK   |    100 |      1 |    100 |00:00:00.01 |      26 |       |       |          |       
    -------------------------------------------------------------------------------------------------------------------------------------       
     
    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") 
    After the 20th update, the 21st update would need to go well beyond the 2800th row printed (probably close to the 2900th row):
         DTLID    HDR_IND      HDRID     CAT_ID    TYPE_ID PROCESS_D I          C         RN
    ---------- ---------- ---------- ---------- ---------- --------- - ---------- ----------
          4109          6       1109          1          1 03-JUL-12 N          0       2643
          4111          6       1111          1          1 03-JUL-12 N          0       2644
          4112          6       1112          1          1 03-JUL-12 N          0       2645
          4113          0       1113          1          1 03-JUL-12 Y          1       2646
          4114          6       1114          1          1 03-JUL-12 N          1       2647
          4115          6       1115          1          1 03-JUL-12 N          1       2648
          4116          0       1116          1          1 03-JUL-12 Y          2       2649
          4117          6       1117          1          1 03-JUL-12 N          2       2650
          4118          6       1118          1          1 03-JUL-12 N          2       2651
    ...
          4266          6       1837          1          1 03-JUL-12 N         66       2798
          4267          6       1838          1          1 03-JUL-12 N         66       2799
          4268          0       1839          1          1 03-JUL-12 Y         67       2800
    ...
    Now, I think you know what you need to do to optimize this SQL statement, if your test case correctly replicates the product environment (if it is not obvious yet, look at the output of the SELECT SQL statement that returns 2800 rows, after the 20th update).

    Charles Hooper
    http://hoopercharles.wordpress.com/
    IT Manager/Oracle DBA
    K&M Machine-Fabricating, Inc.
  • 34. Re: UPDATE with ROWNUM (or TOP-N UPDATE)
    user503699 Expert
    Currently Being Moderated
    Charles Hooper wrote:
    Now, I think you know what you need to do to optimize this SQL statement, if your test case correctly replicates the product environment (if it is not obvious yet, look at the output of the SELECT SQL statement that returns 2800 rows, after the 20th update).
    Charles,

    That is really a great way to explain things. The data pattern query certainly made things easy to understand.
    Many Many thanks for educating me with great patience...
    If I understand correctly, everything boils down to more the number of DTL records such that either the number of records in DTL table that have HDRID as NULL (in addition to other conditions) and/or HDRID such that HDR_IND is either 0 or 2, more the benefit of new index (DTL_IDX2) and short-circuit processing.
    If it is other way round, a HASH JOIN but with indexed access to DTL table looks better.
    For e.g. in the original test set up, I just changed the INSERT for HDR table as follows:
    insert into hdr select level, case when mod(level,70) = 0 then 6 else case when mod(level,25) = 0 then 2 else 0 end end, dbms_random.string('a', 100) from dual connect by level <= 10000
    Repeating the earlier execution, the first UPDATE results were similar but 9th execution of UPDATE (after committing earlier executions) produced following stats
    PLAN_TABLE_OUTPUT
    -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    SQL_ID  dwphb7y3mqb9d, 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 | Reads  |  OMem |  1Mem | Used-Mem |
    ----------------------------------------------------------------------------------------------------------------------------------------------
    |   0 | UPDATE STATEMENT                    |          |      1 |        |      0 |00:00:00.04 |    1660 |      1 |       |       |          |
    |   1 |  UPDATE                             | DTL      |      1 |        |      0 |00:00:00.04 |    1660 |      1 |       |       |          |
    |   2 |   NESTED LOOPS                      |          |      1 |    100 |    100 |00:00:00.03 |     261 |      1 |       |       |          |
    |   3 |    VIEW                             | VW_NSO_1 |      1 |    100 |    100 |00:00:00.02 |     247 |      1 |       |       |          |
    |   4 |     SORT UNIQUE                     |          |      1 |    100 |    100 |00:00:00.02 |     247 |      1 | 73728 | 73728 |          |
    |*  5 |      COUNT STOPKEY                  |          |      1 |        |    100 |00:00:00.02 |     247 |      1 |       |       |          |
    |   6 |       VIEW                          |          |      1 |  63619 |    100 |00:00:00.02 |     247 |      1 |       |       |          |
    |*  7 |        FILTER                       |          |      1 |        |    100 |00:00:00.02 |     247 |      1 |       |       |          |
    |   8 |         NESTED LOOPS OUTER          |          |      1 |  63619 |    112 |00:00:00.02 |     247 |      1 |       |       |          |
    |*  9 |          TABLE ACCESS BY INDEX ROWID| DTL      |      1 |  95524 |    112 |00:00:00.01 |     119 |      1 |       |       |          |
    |* 10 |           INDEX RANGE SCAN          | DTL_IDX2 |      1 |  95524 |    112 |00:00:00.01 |     114 |      1 |       |       |          |
    |  11 |          TABLE ACCESS BY INDEX ROWID| HDR      |    112 |      1 |    112 |00:00:00.01 |     128 |      0 |       |       |          |
    |* 12 |           INDEX UNIQUE SCAN         | HDR_PK   |    112 |      1 |    112 |00:00:00.01 |      16 |      0 |       |       |          |
    |* 13 |    INDEX UNIQUE SCAN                | DTL_PK   |    100 |      1 |    100 |00:00:00.01 |      14 |      0 |       |       |          |
    ----------------------------------------------------------------------------------------------------------------------------------------------
    
    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")
    
    
    48 rows selected.
    and the corresponding non-hinted version of UPDATE produced following stats
    PLAN_TABLE_OUTPUT
    -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    SQL_ID  508hbs4fuzjqc, 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.07 |    1630 |    169 |       |       |          |
    |   1 |  UPDATE                              | DTL      |      1 |        |      0 |00:00:00.07 |    1630 |    169 |       |       |          |
    |   2 |   NESTED LOOPS                       |          |      1 |    100 |    100 |00:00:00.06 |     269 |    169 |       |       |          |
    |   3 |    VIEW                              | VW_NSO_1 |      1 |    100 |    100 |00:00:00.05 |     255 |    169 |       |       |          |
    |   4 |     SORT UNIQUE                      |          |      1 |    100 |    100 |00:00:00.05 |     255 |    169 | 73728 | 73728 |          |
    |*  5 |      COUNT STOPKEY                   |          |      1 |        |    100 |00:00:00.05 |     255 |    169 |       |       |          |
    |   6 |       VIEW                           |          |      1 |  63619 |    100 |00:00:00.05 |     255 |    169 |       |       |          |
    |*  7 |        SORT ORDER BY STOPKEY         |          |      1 |  63619 |    100 |00:00:00.05 |     255 |    169 |  9216 |  9216 | 8192  (0)|
    |*  8 |         FILTER                       |          |      1 |        |   5114 |00:00:00.08 |     255 |    169 |       |       |          |
    |*  9 |          HASH JOIN RIGHT OUTER       |          |      1 |  63619 |   5200 |00:00:00.32 |     255 |    169 |   968K|   968K| 1383K (0)|
    |  10 |           TABLE ACCESS FULL          | HDR      |      1 |  10000 |  10000 |00:00:00.04 |     190 |    169 |       |       |          |
    |* 11 |           TABLE ACCESS BY INDEX ROWID| DTL      |      1 |  95524 |   5200 |00:00:00.08 |      65 |      0 |       |       |          |
    |* 12 |            INDEX RANGE SCAN          | DTL_IDX1 |      1 |  95524 |   5200 |00:00:00.03 |      30 |      0 |       |       |          |
    |* 13 |    INDEX UNIQUE SCAN                 | DTL_PK   |    100 |      1 |    100 |00:00:00.01 |      14 |      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.
1 2 3 Previous Next

Legend

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