1 2 3 Previous Next 34 Replies Latest reply: Jul 3, 2012 4:53 PM by user503699 RSS

    UPDATE with ROWNUM (or TOP-N UPDATE)

    user503699
      Hello All,

      I am facing a performance issue with a UPDATE statement on one of our database. The UPDATE statement is written with the intention of updating only (up to) 100 records in the table that contains more than 2 million records. The UPDATE statement does enormous amount of I/Os (many thousands per row). This happens mainly due to the table in question being accessed using full table scan, even though an indexed access would be beneficial (IMO)
      I have so far gone through following references which, I believe, are similar to my case but I am not really sure if I can reduce the amount of I/O.
      Optimizer choosing different plans when ROWNUM filter. [UPDATED: 11.2.0.1]
      http://jonathanlewis.wordpress.com/2008/11/11/first_rows_n/
      http://jonathanlewis.wordpress.com/2010/09/30/rownum-effects-2/
      http://jonathanlewis.wordpress.com/2006/11/22/tuning-updates/
      http://afatkulin.blogspot.co.uk/2009/01/update-and-rownum-oddity.html
      http://dioncho.wordpress.com/2009/01/30/89/

      Can somebody help please?

      Here is my attempt (test case) to simulate the issue (although I was not able to get the indexed access here)
      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> create table dtl (dtlid number(10), process_ind varchar2(10) not null, process_date date not null, hdrid number(10), 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 table hdr (hdrid number(10), hdr_ind number(2) not null, hpad varchar2(100), constraint hdr_pk primary key (hdrid)) ;
      
      Table 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> insert into dtl select rownum, ind, pdate, hdrid, hpad from (select level, 'PROCESSED' ind, (sysdate - dbms_random.value(1, 100)) pdate, case mod(level, 100) when 13 then null else level end hdrid, dbms_random.string('a', 100) hpad from dual connect by level <= 10000 UNION ALL select level, 'NEW', (sysdate + dbms_random.value(1, 100)), case mod(level, 100) when 13 then null else level end, dbms_random.string('a', 100) from dual connect by level <= 50000) ;
      
      60000 rows created.
      
      SQL> commit ;
      
      Commit complete.
      
      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.
      Following are 3 versions of the query that I believe are functionally equivalent:
      SQL> set autot traceon
      SQL> 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 ;
      
      10 rows selected.
      
      
      Execution Plan
      ----------------------------------------------------------
      Plan hash value: 3926759265
      
      ----------------------------------------------------------------------------------------------
      | Id  | Operation                 | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
      ----------------------------------------------------------------------------------------------
      |   0 | SELECT STATEMENT            |          |       10 |      130 |       54   (2)| 00:00:01 |
      |*  1 |  COUNT STOPKEY                 |          |          |          |            |          |
      |   2 |   VIEW                      |          |       21 |      273 |       54   (2)| 00:00:01 |
      |*  3 |    SORT ORDER BY STOPKEY       |          |       21 |      462 |       54   (2)| 00:00:01 |
      |   4 |     VIEW                 |          |       21 |      462 |       53   (0)| 00:00:01 |
      |   5 |      UNION-ALL                 |          |          |          |            |          |
      |*  6 |       TABLE ACCESS FULL        | DTL      |       11 |      264 |       15   (0)| 00:00:01 |
      |   7 |       NESTED LOOPS            |          |       10 |      310 |       38   (0)| 00:00:01 |
      |   8 |        TABLE ACCESS BY INDEX ROWID| DTL      |       29 |      696 |       32   (0)| 00:00:01 |
      |*  9 |      INDEX RANGE SCAN       | DTL_IDX1 |          |          |        2   (0)| 00:00:01 |
      |* 10 |        TABLE ACCESS BY INDEX ROWID| HDR      |        1 |        7 |        1   (0)| 00:00:01 |
      |* 11 |      INDEX UNIQUE SCAN       | HDR_PK   |        1 |          |        0   (0)| 00:00:01 |
      ----------------------------------------------------------------------------------------------
      
      Predicate Information (identified by operation id):
      ---------------------------------------------------
      
         1 - filter(ROWNUM<=10)
         3 - filter(ROWNUM<=10)
         6 - filter("HDRID" IS NULL AND "PROCESS_IND"='NEW' AND "PROCESS_DATE"<SYSDATE@!+30)
         9 - access("DTL"."PROCESS_IND"='NEW' AND "DTL"."PROCESS_DATE"<SYSDATE@!+30)
        10 - filter("HDR_IND"=0 OR "HDR_IND"=2)
        11 - access("DTL"."HDRID"="HDR"."HDRID")
      
      
      Statistics
      ----------------------------------------------------------
           704  recursive calls
             0  db block gets
            33741  consistent gets
             0  physical reads
             0  redo size
           442  bytes sent via SQL*Net to client
           349  bytes received via SQL*Net from client
             2  SQL*Net roundtrips to/from client
            13  sorts (memory)
             0  sorts (disk)
            10  rows processed
      
      SQL> select dtlid from (select dtlid from (select dtlid, process_date, process_ind from dtl where hdrid is null UNION ALL select dtl.dtlid, dtl.process_date, dtl.process_ind from dtl, hdr where dtl.hdrid = hdr.hdrid and hdr_ind in (0, 2)) where process_date < (sysdate + 30) and process_ind = 'NEW' order by dtlid, process_date) where rownum <= 10 ;
      
      10 rows selected.
      
      
      Execution Plan
      ----------------------------------------------------------
      Plan hash value: 3926759265
      
      ----------------------------------------------------------------------------------------------
      | Id  | Operation                 | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
      ----------------------------------------------------------------------------------------------
      |   0 | SELECT STATEMENT            |          |       10 |      130 |       57   (2)| 00:00:01 |
      |*  1 |  COUNT STOPKEY                 |          |          |          |            |          |
      |   2 |   VIEW                      |          |       22 |      286 |       57   (2)| 00:00:01 |
      |*  3 |    SORT ORDER BY STOPKEY       |          |       22 |      638 |       57   (2)| 00:00:01 |
      |   4 |     VIEW                 |          |       22 |      638 |       56   (0)| 00:00:01 |
      |   5 |      UNION-ALL                 |          |          |          |            |          |
      |*  6 |       TABLE ACCESS FULL        | DTL      |       11 |      264 |       15   (0)| 00:00:01 |
      |   7 |       NESTED LOOPS            |          |       11 |      341 |       41   (0)| 00:00:01 |
      |   8 |        TABLE ACCESS BY INDEX ROWID| DTL      |       32 |      768 |       35   (0)| 00:00:01 |
      |*  9 |      INDEX RANGE SCAN       | DTL_IDX1 |          |          |        2   (0)| 00:00:01 |
      |* 10 |        TABLE ACCESS BY INDEX ROWID| HDR      |        1 |        7 |        1   (0)| 00:00:01 |
      |* 11 |      INDEX UNIQUE SCAN       | HDR_PK   |        1 |          |        0   (0)| 00:00:01 |
      ----------------------------------------------------------------------------------------------
      
      Predicate Information (identified by operation id):
      ---------------------------------------------------
      
         1 - filter(ROWNUM<=10)
         3 - filter(ROWNUM<=10)
         6 - filter("HDRID" IS NULL AND "PROCESS_IND"='NEW' AND "PROCESS_DATE"<SYSDATE@!+30)
         9 - access("DTL"."PROCESS_IND"='NEW' AND "DTL"."PROCESS_DATE"<SYSDATE@!+30)
        10 - filter("HDR_IND"=0 OR "HDR_IND"=2)
        11 - access("DTL"."HDRID"="HDR"."HDRID")
      
      
      Statistics
      ----------------------------------------------------------
             1  recursive calls
             0  db block gets
            33606  consistent gets
             0  physical reads
             0  redo size
           442  bytes sent via SQL*Net to client
           349  bytes received via SQL*Net from client
             2  SQL*Net roundtrips to/from client
             1  sorts (memory)
             0  sorts (disk)
            10  rows processed
      
      SQL> select dtlid from (select dtl.dtlid, dtl.process_date from dtl, hdr where (dtl.hdrid = hdr.hdrid OR dtl.hdrid is null) 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 ;
      
      10 rows selected.
      
      
      Execution Plan
      ----------------------------------------------------------
      Plan hash value: 4216079878
      
      ---------------------------------------------------------------------------------
      | Id  | Operation           | Name | Rows     | Bytes | Cost (%CPU)| Time     |
      ---------------------------------------------------------------------------------
      |   0 | SELECT STATEMENT      |     |    10 |   130 | 11696   (1)| 00:02:21 |
      |*  1 |  COUNT STOPKEY           |     |     |     |          |          |
      |   2 |   VIEW                |     |   392 |  5096 | 11696   (1)| 00:02:21 |
      |*  3 |    SORT ORDER BY STOPKEY |     |   392 | 12152 | 11696   (1)| 00:02:21 |
      |   4 |     CONCATENATION      |     |     |     |          |          |
      |   5 |      MERGE JOIN CARTESIAN|     |   390 | 12090 |   293   (1)| 00:00:04 |
      |*  6 |       TABLE ACCESS FULL  | DTL     |   195 |  4680 |   249   (1)| 00:00:03 |
      |   7 |       BUFFER SORT      |     |     2 |    14 |    44   (0)| 00:00:01 |
      |*  8 |        TABLE ACCESS FULL | HDR     |     2 |    14 |     0   (0)| 00:00:01 |
      |*  9 |      HASH JOIN           |     |     2 |    62 |   252   (1)| 00:00:04 |
      |* 10 |       TABLE ACCESS FULL  | HDR     |     2 |    14 |     2   (0)| 00:00:01 |
      |* 11 |       TABLE ACCESS FULL  | DTL     | 19308 |   452K|   249   (1)| 00:00:03 |
      ---------------------------------------------------------------------------------
      
      Predicate Information (identified by operation id):
      ---------------------------------------------------
      
         1 - filter(ROWNUM<=10)
         3 - filter(ROWNUM<=10)
         6 - filter("DTL"."HDRID" IS NULL AND "DTL"."PROCESS_IND"='NEW' AND
                 "DTL"."PROCESS_DATE"<SYSDATE@!+30)
         8 - filter("HDR_IND"=0 OR "HDR_IND"=2)
         9 - access("DTL"."HDRID"="HDR"."HDRID")
        10 - filter("HDR_IND"=0 OR "HDR_IND"=2)
        11 - filter("DTL"."PROCESS_IND"='NEW' AND
                 "DTL"."PROCESS_DATE"<SYSDATE@!+30 AND LNNVL("DTL"."HDRID" IS NULL))
      
      
      Statistics
      ----------------------------------------------------------
             1  recursive calls
             0  db block gets
             2622  consistent gets
             0  physical reads
             0  redo size
           407  bytes sent via SQL*Net to client
           349  bytes received via SQL*Net from client
             2  SQL*Net roundtrips to/from client
             2  sorts (memory)
             0  sorts (disk)
            10  rows processed
      And, following are the actual UPDATE statements corresponding to 3 versions of SELECT above
      SQL> update dtl set process_ind = 'PROCESSED' where dtlid in (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) ;
      
      10 rows updated.
      
      
      Execution Plan
      ----------------------------------------------------------
      Plan hash value: 1321972013
      
      ----------------------------------------------------------------------------------------
      | Id  | Operation              | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
      ----------------------------------------------------------------------------------------
      |   0 | UPDATE STATEMENT         |            |     1 |    32 |   557      (1)| 00:00:07 |
      |   1 |  UPDATE               | DTL      |       |       |         |            |
      |   2 |   NESTED LOOPS              |            |     1 |    32 |   557      (1)| 00:00:07 |
      |   3 |    VIEW               | VW_NSO_1 |    10 |   130 |   555      (1)| 00:00:07 |
      |   4 |     SORT UNIQUE          |            |     1 |   130 |         |            |
      |*  5 |      COUNT STOPKEY         |            |       |       |         |            |
      |   6 |       VIEW              |            |  7055 | 91715 |   555      (1)| 00:00:07 |
      |*  7 |        SORT ORDER BY STOPKEY|            |  7055 |   151K|   555      (1)| 00:00:07 |
      |   8 |      VIEW              |            |  7055 |   151K|   554      (1)| 00:00:07 |
      |   9 |       UNION-ALL         |            |       |       |         |            |
      |* 10 |        TABLE ACCESS FULL | DTL      |   195 |  4680 |   249      (1)| 00:00:03 |
      |* 11 |        HASH JOIN         |            |  6860 |   207K|   305      (1)| 00:00:04 |
      |* 12 |         TABLE ACCESS FULL| HDR      |  6667 | 46669 |    55      (0)| 00:00:01 |
      |* 13 |         TABLE ACCESS FULL| DTL      | 19503 |   457K|   249      (1)| 00:00:03 |
      |* 14 |    INDEX UNIQUE SCAN         | DTL_PK   |     1 |    19 |     0      (0)| 00:00:01 |
      ----------------------------------------------------------------------------------------
      
      Predicate Information (identified by operation id):
      ---------------------------------------------------
      
         5 - filter(ROWNUM<=10)
         7 - filter(ROWNUM<=10)
        10 - filter("HDRID" IS NULL AND "PROCESS_IND"='NEW' AND
                 "PROCESS_DATE"<SYSDATE@!+30)
        11 - access("DTL"."HDRID"="HDR"."HDRID")
        12 - filter("HDR_IND"=0 OR "HDR_IND"=2)
        13 - filter("DTL"."PROCESS_IND"='NEW' AND "DTL"."PROCESS_DATE"<SYSDATE@!+30)
        14 - access("DTLID"="$nso_col_1")
      
      
      Statistics
      ----------------------------------------------------------
           663  recursive calls
            52  db block gets
             2593  consistent gets
             0  physical reads
             7688  redo size
           554  bytes sent via SQL*Net to client
           886  bytes received via SQL*Net from client
             3  SQL*Net roundtrips to/from client
            24  sorts (memory)
             0  sorts (disk)
            10  rows processed
      
      SQL> rollback ;
      
      Rollback complete.
      
      SQL> update dtl set process_ind = 'PROCESSED' where dtlid in (select dtlid from (select dtlid from (select dtlid, process_date, process_ind from dtl where hdrid is null UNION ALL select dtl.dtlid, dtl.process_date, dtl.process_ind from dtl, hdr where dtl.hdrid = hdr.hdrid and hdr_ind in (0, 2)) where process_date < (sysdate + 30) and process_ind = 'NEW' order by dtlid, process_date) where rownum <= 10) ;
      
      10 rows updated.
      
      
      Execution Plan
      ----------------------------------------------------------
      Plan hash value: 1321972013
      
      ------------------------------------------------------------------------------------------------
      | Id  | Operation              | Name     | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
      ------------------------------------------------------------------------------------------------
      |   0 | UPDATE STATEMENT         |            |     1 |    32 |       |   616      (1)| 00:00:08 |
      |   1 |  UPDATE               | DTL      |       |       |       |         |            |
      |   2 |   NESTED LOOPS              |            |     1 |    32 |       |   616      (1)| 00:00:08 |
      |   3 |    VIEW               | VW_NSO_1 |    10 |   130 |       |   614      (1)| 00:00:08 |
      |   4 |     SORT UNIQUE          |            |     1 |   130 |       |         |            |
      |*  5 |      COUNT STOPKEY         |            |       |       |       |         |            |
      |   6 |       VIEW              |            |  7055 | 91715 |       |   614      (1)| 00:00:08 |
      |*  7 |        SORT ORDER BY STOPKEY|            |  7055 |   199K|   288K|   614      (1)| 00:00:08 |
      |   8 |      VIEW              |            |  7055 |   199K|       |   554      (1)| 00:00:07 |
      |   9 |       UNION-ALL         |            |       |       |       |         |            |
      |* 10 |        TABLE ACCESS FULL | DTL      |   195 |  4680 |       |   249      (1)| 00:00:03 |
      |* 11 |        HASH JOIN         |            |  6860 |   207K|       |   305      (1)| 00:00:04 |
      |* 12 |         TABLE ACCESS FULL| HDR      |  6667 | 46669 |       |    55      (0)| 00:00:01 |
      |* 13 |         TABLE ACCESS FULL| DTL      | 19504 |   457K|       |   249      (1)| 00:00:03 |
      |* 14 |    INDEX UNIQUE SCAN         | DTL_PK   |     1 |    19 |       |     0      (0)| 00:00:01 |
      ------------------------------------------------------------------------------------------------
      
      Predicate Information (identified by operation id):
      ---------------------------------------------------
      
         5 - filter(ROWNUM<=10)
         7 - filter(ROWNUM<=10)
        10 - filter("HDRID" IS NULL AND "PROCESS_IND"='NEW' AND "PROCESS_DATE"<SYSDATE@!+30)
        11 - access("DTL"."HDRID"="HDR"."HDRID")
        12 - filter("HDR_IND"=0 OR "HDR_IND"=2)
        13 - filter("DTL"."PROCESS_IND"='NEW' AND "DTL"."PROCESS_DATE"<SYSDATE@!+30)
        14 - access("DTLID"="$nso_col_1")
      
      
      Statistics
      ----------------------------------------------------------
           366  recursive calls
            52  db block gets
             2530  consistent gets
             0  physical reads
             0  redo size
           554  bytes sent via SQL*Net to client
           852  bytes received via SQL*Net from client
             3  SQL*Net roundtrips to/from client
            15  sorts (memory)
             0  sorts (disk)
            10  rows processed
      
      SQL> rollback ;
      
      Rollback complete.
      
      SQL> update dtl set process_ind = 'PROCESSED' where dtlid in (select dtlid from (select dtl.dtlid, dtl.process_date from dtl, hdr where (dtl.hdrid = hdr.hdrid OR dtl.hdrid is null) 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) ;
      
      3 rows updated.
      
      
      Execution Plan
      ----------------------------------------------------------
      Plan hash value: 201283143
      
      -----------------------------------------------------------------------------------------
      | Id  | Operation               | Name     | Rows     | Bytes | Cost (%CPU)| Time     |
      -----------------------------------------------------------------------------------------
      |   0 | UPDATE STATEMENT          |          |    10 |   320 | 22156   (1)| 00:04:26 |
      |   1 |  UPDATE                | DTL     |     |     |          |          |
      |   2 |   NESTED LOOPS               |          |    10 |   320 | 22156   (1)| 00:04:26 |
      |   3 |    VIEW                | VW_NSO_1 |    10 |   130 | 22145   (1)| 00:04:26 |
      |   4 |     SORT UNIQUE           |          |    10 |   130 |          |          |
      |*  5 |      COUNT STOPKEY          |          |     |     |          |          |
      |   6 |       VIEW               |          |  1307K|    16M| 22145   (1)| 00:04:26 |
      |*  7 |        SORT ORDER BY STOPKEY |          |  1307K|    38M| 22145   (1)| 00:04:26 |
      |   8 |      CONCATENATION          |          |     |     |          |          |
      |   9 |       MERGE JOIN CARTESIAN|          |  1300K|    38M| 10688   (1)| 00:02:09 |
      |* 10 |        TABLE ACCESS FULL  | DTL     |   195 |  4680 |   249   (1)| 00:00:03 |
      |  11 |        BUFFER SORT          |          |  6667 | 46669 | 10439   (1)| 00:02:06 |
      |* 12 |         TABLE ACCESS FULL | HDR     |  6667 | 46669 |    54   (2)| 00:00:01 |
      |* 13 |       HASH JOIN          |          |  6857 |   207K|   305   (1)| 00:00:04 |
      |* 14 |        TABLE ACCESS FULL  | HDR     |  6667 | 46669 |    55   (0)| 00:00:01 |
      |* 15 |        TABLE ACCESS FULL  | DTL     | 19309 |   452K|   249   (1)| 00:00:03 |
      |* 16 |    INDEX UNIQUE SCAN          | DTL_PK     |     1 |    19 |     0   (0)| 00:00:01 |
      -----------------------------------------------------------------------------------------
      
      Predicate Information (identified by operation id):
      ---------------------------------------------------
      
         5 - filter(ROWNUM<=10)
         7 - filter(ROWNUM<=10)
        10 - filter("DTL"."HDRID" IS NULL AND "DTL"."PROCESS_IND"='NEW' AND
                 "DTL"."PROCESS_DATE"<SYSDATE@!+30)
        12 - filter("HDR_IND"=0 OR "HDR_IND"=2)
        13 - access("DTL"."HDRID"="HDR"."HDRID")
        14 - filter("HDR_IND"=0 OR "HDR_IND"=2)
        15 - filter("DTL"."PROCESS_IND"='NEW' AND "DTL"."PROCESS_DATE"<SYSDATE@!+30
                 AND LNNVL("DTL"."HDRID" IS NULL))
        16 - access("DTLID"="$nso_col_1")
      
      
      Statistics
      ----------------------------------------------------------
           316  recursive calls
            17  db block gets
             2707  consistent gets
             0  physical reads
             0  redo size
           554  bytes sent via SQL*Net to client
           763  bytes received via SQL*Net from client
             3  SQL*Net roundtrips to/from client
            15  sorts (memory)
             0  sorts (disk)
             3  rows processed
      
      SQL> rollback ;
      
      Rollback complete.
        • 1. Re: UPDATE with ROWNUM (or TOP-N UPDATE)
          user503699
          Hello,

          I am not sure if this is relevant (it had been a long day, so apologies in advance) but one of the things that I believe would help CBO in this case is if I am able to convey it the fact that I am only interested in getting up to 100 rows while/before joining/combining the results of the base tables involved. I am sure (??) that "knowledge" would allow the optimizer to choose a more efficient execution path/join method and should result in less resources consumed.
          At present, all the plans in OP suggest (and I believe that is how it is processed) that the joins/combining of the results from base tables is done first and only the subsequent SORT operation is "aware" of the "ROWNUM <= 100" predicate.

          BTW, I am not expecting CBO to automagically do this for me (although that would be idea ;) ) and I am prepared to rewrite the SQL or add hints if that will help.
          • 2. Re: UPDATE with ROWNUM (or TOP-N UPDATE)
            riedelme
            I'm not actually comfortable with the idea of only updating some of the rows - if the data needs to be updated then it needs to be updated, all of it. But I'm not on your project or at your site and I do not know your requirements nor the reasons behind those requriements. At least you are looking to control what gets updated with top-n :)

            Have you considered using parallel DML if you have the license to use parallel query? It is different than parallel query and must be turned on to be used. Check the docs on how to use it if it sounds promising. Also, if you are doing full table scans on large table parallel query might help that operation - again, if you have the license.

            The data you posted was interesting - I would investigate the issue the same way you are going about it. Your queries don't seem to be the same, though: the first two updated 10 rows while the third one only did 3. Does that matter?

            The cost on the third plan was 4-5 times higher on the other two, which may mean nothing. Did it run for a longer period of time?

            Sometimes complex subqueries in updates can be really slow. Not lately, but in ages past I found it worth checking out to use a select loop to get a row and then issue a singleton manual update taking advantage of indexes ias an alternative to situations were the same subquery occurred multiple times in a single SQL. Again, this hasn't been efficient lately but sometimes old tricks work in precise circumstances ...

            Oh, have you tried the FIRST_ROWS_N hint? Dunno if it works with UPDATE - probably not - but you might apply it to the underlying SQL

            Edited by: riedelme on Jun 25, 2012 1:05 PM
            • 3. Re: UPDATE with ROWNUM (or TOP-N UPDATE)
              Iordan Iotzov
              Did Oracle create a histogram on DTL/PROCESS_IND?

              Iordan Iotzov
              http://iiotzov.wordpress.com/
              • 4. Re: UPDATE with ROWNUM (or TOP-N UPDATE)
                user503699
                riedelme wrote:
                I'm not actually comfortable with the idea of only updating some of the rows - if the data needs to be updated then it needs to be updated, all of it. But I'm not on your project or at your site and I do not know your requirements nor the reasons behind those requriements. At least you are looking to control what gets updated with top-n :)
                I try :)
                Have you considered using parallel DML if you have the license to use parallel query? It is different than parallel query and must be turned on to be used. Check the docs on how to use it if it sounds promising. Also, if you are doing full table scans on large table parallel query might help that operation - again, if you have the license.
                Unfortunately can't use it. Running on SE.
                The data you posted was interesting - I would investigate the issue the same way you are going about it. Your queries don't seem to be the same, though: the first two updated 10 rows while the third one only did 3. Does that matter?
                Hmm. Thanks for pointing that out as I had obviously missed it. Although, at this point, I am not sure why it is different. My SELECT statements earlier all produced 10 rows each and I used exactly the same statements as a row-source for corresponding UPDATEs.
                The cost on the third plan was 4-5 times higher on the other two, which may mean nothing. Did it run for a longer period of time?
                As I said earlier, this test case is kind of a scaled-down version of the original table volumes. I have not generated as much data as is in live tables and although I have tried my best to replicate the patterns of data, it is not an exact match. However, I believe it is still good enough to prove the issue I am facing.
                Sometimes complex subqueries in updates can be really slow. Not lately, but in ages past I found it worth checking out to use a select loop to get a row and then issue a singleton manual update taking advantage of indexes ias an alternative to situations were the same subquery occurred multiple times in a single SQL. Again, this hasn't been efficient lately but sometimes old tricks work in precise circumstances ...
                Not tested it but the approach may not be the preferred one as it may involve more code changes.
                Oh, have you tried the FIRST_ROWS_N hint? Dunno if it works with UPDATE - probably not - but you might apply it to the underlying SQL
                Tried that but I guess UPDATE, internally, forces ALL_ROWS mode. At least that is what most of the posts I referred to (mentioned in OP) appear to suggest.
                • 5. Re: UPDATE with ROWNUM (or TOP-N UPDATE)
                  user503699
                  Iordan Iotzov wrote:
                  Did Oracle create a histogram on DTL/PROCESS_IND?
                  Yes it has.
                  • 6. Re: UPDATE with ROWNUM (or TOP-N UPDATE)
                    user503699
                    Can I do anything to interest anybody to help me out with this? ;)
                    • 7. Re: UPDATE with ROWNUM (or TOP-N UPDATE)
                      sb92075
                      user503699 wrote:
                      Can I do anything to interest anybody to help me out with this? ;)
                      what kind of help do you expect from here?
                      • 8. Re: UPDATE with ROWNUM (or TOP-N UPDATE)
                        user503699
                        sb92075 wrote:
                        user503699 wrote:
                        Can I do anything to interest anybody to help me out with this? ;)
                        what kind of help do you expect from here?
                        Would appreciate if anybody can suggest how I can reduce I/Os and improve performance of the UPDATE statement (as mentioned in my OP)
                        I am facing a performance issue with a UPDATE statement on one of our database. The UPDATE statement is written with the intention of updating only (up to) 100 records in the table that contains more than 2 million records. The UPDATE statement does enormous amount of I/Os (many thousands per row)
                        I am happy to answer any questions.
                        • 9. Re: UPDATE with ROWNUM (or TOP-N UPDATE)
                          Charles Hooper
                          user503699 wrote:
                          Would appreciate if anybody can suggest how I can reduce I/Os and improve performance of the UPDATE statement (as mentioned in my OP)
                          I am facing a performance issue with a UPDATE statement on one of our database. The UPDATE statement is written with the intention of updating only (up to) 100 records in the table that contains more than 2 million records. The UPDATE statement does enormous amount of I/Os (many thousands per row)
                          I am happy to answer any questions.
                          Narendra,

                          I tried your test case on 11.2.0.2, and on the second execution, cardinality feedback took place, dropping the consistent gets from 30,647 to 2,437 - yet the autotrace generated execution plan remained the same. When displaying the actual execution plan using DBMS_XPLAN.DISPLAY_CURSOR, the new plan was very similar to the plan that you show for the first UPDATE statement.

                          The 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 ;
                          On the first execution, this is the autotrace statistics and plan that appeared:
                          Plan hash value: 2201656677
                           
                          -----------------------------------------------------------------------------------------------
                          | Id  | Operation                          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
                          -----------------------------------------------------------------------------------------------
                          |   0 | SELECT STATEMENT                   |          |    10 |   130 |    45   (3)| 00:00:01 |
                          |*  1 |  COUNT STOPKEY                     |          |       |       |            |          |
                          |   2 |   VIEW                             |          |    20 |   260 |    45   (3)| 00:00:01 |
                          |*  3 |    SORT ORDER BY STOPKEY           |          |    20 |   440 |    45   (3)| 00:00:01 |
                          |   4 |     VIEW                           |          |    20 |   440 |    44   (0)| 00:00:01 |
                          |   5 |      UNION-ALL                     |          |       |       |            |          |
                          |*  6 |       TABLE ACCESS FULL            | DTL      |    10 |   230 |     6   (0)| 00:00:01 |
                          |   7 |       NESTED LOOPS                 |          |       |       |            |          |
                          |   8 |        NESTED LOOPS                |          |    10 |   300 |    38   (0)| 00:00:01 |
                          |   9 |         TABLE ACCESS BY INDEX ROWID| DTL      |    29 |   667 |    32   (0)| 00:00:01 |
                          |* 10 |          INDEX RANGE SCAN          | DTL_IDX1 |       |       |     2   (0)| 00:00:01 |
                          |* 11 |         INDEX UNIQUE SCAN          | HDR_PK   |     1 |       |     0   (0)| 00:00:01 |
                          |* 12 |        TABLE ACCESS BY INDEX ROWID | HDR      |     1 |     7 |     1   (0)| 00:00:01 |
                          -----------------------------------------------------------------------------------------------
                           
                          Predicate Information (identified by operation id):
                          ---------------------------------------------------
                             1 - filter(ROWNUM<=10)
                             3 - filter(ROWNUM<=10)
                             6 - filter("HDRID" IS NULL AND "PROCESS_IND"='NEW' AND "PROCESS_DATE"<SYSDATE@!+30)
                            10 - access("DTL"."PROCESS_IND"='NEW' AND "DTL"."PROCESS_DATE"<SYSDATE@!+30)
                            11 - access("DTL"."HDRID"="HDR"."HDRID")
                            12 - filter("HDR_IND"=0 OR "HDR_IND"=2)
                           
                          Statistics
                          ----------------------------------------------------------
                                    1  recursive calls
                                    0  db block gets
                                30647  consistent gets
                                    0  physical reads
                                    0  redo size
                                  453  bytes sent via SQL*Net to client
                                  359  bytes received via SQL*Net from client
                                    2  SQL*Net roundtrips to/from client
                                    1  sorts (memory)
                                    0  sorts (disk)
                                   10  rows processed
                          On the second execution, note that the displayed execution plan is the same, but the number of consistent gets dropped significantly:
                          Plan hash value: 2201656677
                           
                          -----------------------------------------------------------------------------------------------
                          | Id  | Operation                          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
                          -----------------------------------------------------------------------------------------------
                          |   0 | SELECT STATEMENT                   |          |    10 |   130 |    45   (3)| 00:00:01 |
                          |*  1 |  COUNT STOPKEY                     |          |       |       |            |          |
                          |   2 |   VIEW                             |          |    20 |   260 |    45   (3)| 00:00:01 |
                          |*  3 |    SORT ORDER BY STOPKEY           |          |    20 |   440 |    45   (3)| 00:00:01 |
                          |   4 |     VIEW                           |          |    20 |   440 |    44   (0)| 00:00:01 |
                          |   5 |      UNION-ALL                     |          |       |       |            |          |
                          |*  6 |       TABLE ACCESS FULL            | DTL      |    10 |   230 |     6   (0)| 00:00:01 |
                          |   7 |       NESTED LOOPS                 |          |       |       |            |          |
                          |   8 |        NESTED LOOPS                |          |    10 |   300 |    38   (0)| 00:00:01 |
                          |   9 |         TABLE ACCESS BY INDEX ROWID| DTL      |    29 |   667 |    32   (0)| 00:00:01 |
                          |* 10 |          INDEX RANGE SCAN          | DTL_IDX1 |       |       |     2   (0)| 00:00:01 |
                          |* 11 |         INDEX UNIQUE SCAN          | HDR_PK   |     1 |       |     0   (0)| 00:00:01 |
                          |* 12 |        TABLE ACCESS BY INDEX ROWID | HDR      |     1 |     7 |     1   (0)| 00:00:01 |
                          -----------------------------------------------------------------------------------------------
                           
                          Predicate Information (identified by operation id):
                          ---------------------------------------------------
                             1 - filter(ROWNUM<=10)
                             3 - filter(ROWNUM<=10)
                             6 - filter("HDRID" IS NULL AND "PROCESS_IND"='NEW' AND "PROCESS_DATE"<SYSDATE@!+30)
                            10 - access("DTL"."PROCESS_IND"='NEW' AND "DTL"."PROCESS_DATE"<SYSDATE@!+30)
                            11 - access("DTL"."HDRID"="HDR"."HDRID")
                            12 - filter("HDR_IND"=0 OR "HDR_IND"=2)
                           
                          Statistics
                          ----------------------------------------------------------
                                    1  recursive calls
                                    0  db block gets
                                 2437  consistent gets
                                    0  physical reads
                                    0  redo size
                                  453  bytes sent via SQL*Net to client
                                  359  bytes received via SQL*Net from client
                                    2  SQL*Net roundtrips to/from client
                                    1  sorts (memory)
                                    0  sorts (disk)
                                   10  rows processed
                          Something must explain the drop in consistent gets. Let's check the real execution plan:
                          SET AUTOTRACE OFF
                           
                          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 ;
                           
                          SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'TYPICAL'));
                          This is the output:
                           
                          PLAN_TABLE_OUTPUT
                          --------------------------------------------------------------------------------
                          SQL_ID  bf602b4pyd2h2, child number 1
                          -------------------------------------
                          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
                           
                          Plan hash value: 986254152
                            
                          --------------------------------------------------------------------------------
                          | Id  | Operation               | Name | Rows  | Bytes | Cost (%CPU)| Time     |
                          --------------------------------------------------------------------------------
                          |   0 | SELECT STATEMENT        |      |       |       |    32 (100)|          |
                          |*  1 |  COUNT STOPKEY          |      |       |       |            |          |
                          |   2 |   VIEW                  |      |  2023 | 26299 |    32   (7)| 00:00:01 |
                          |*  3 |    SORT ORDER BY STOPKEY|      |  2023 | 44506 |    32   (7)| 00:00:01 |
                          |   4 |     VIEW                |      |  2023 | 44506 |    31   (4)| 00:00:01 |
                          |   5 |      UNION-ALL          |      |       |       |            |          |
                          |*  6 |       TABLE ACCESS FULL | DTL  |   158 |  3634 |     7   (0)| 00:00:01 |
                          |*  7 |       HASH JOIN         |      |  1865 | 98845 |    24   (5)| 00:00:01 |
                          |*  8 |        TABLE ACCESS FULL| HDR  |  6667 | 46669 |    21   (0)| 00:00:01 |
                          |*  9 |        TABLE ACCESS FULL| DTL  | 14637 |   328K|     2   (0)| 00:00:01 |
                          --------------------------------------------------------------------------------
                           
                          Predicate Information (identified by operation id):
                          ---------------------------------------------------
                             1 - filter(ROWNUM<=10)
                             3 - filter(ROWNUM<=10)
                             6 - filter(("HDRID" IS NULL AND "PROCESS_IND"='NEW' AND
                                        "PROCESS_DATE"<SYSDATE@!+30))
                             7 - access("DTL"."HDRID"="HDR"."HDRID")
                             8 - filter(("HDR_IND"=0 OR "HDR_IND"=2))
                             9 - filter(("DTL"."PROCESS_IND"='NEW' AND
                                        "DTL"."PROCESS_DATE"<SYSDATE@!+30))
                            
                          Note
                          -----
                             - cardinality feedback used for this statement
                          Note that the above execute plan seems to be the same as what you are seeing in your UPDATE statement. Oracle Database 11.2.0.2 apparently switched from a FIRST_ROWS(10) optimization to an ALL_ROWS optimization - as suggested by the hash join operation.

                          You could try changing the SQL statement so that the DTL table is touched only a single time (I think that this is logically correct):
                          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 ;
                           
                          SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'TYPICAL'));
                          The autotrace generated output follows:
                          Execution Plan
                          ----------------------------------------------------------
                          Plan hash value: 2000376855
                           
                          ----------------------------------------------------------------------------------
                          | Id  | Operation                 | Name | Rows  | Bytes | Cost (%CPU)| Time     |
                          ----------------------------------------------------------------------------------
                          |   0 | SELECT STATEMENT          |      |    10 |   130 |   115   (4)| 00:00:01 |
                          |*  1 |  COUNT STOPKEY            |      |       |       |            |          |
                          |   2 |   VIEW                    |      |  8582 |   108K|   115   (4)| 00:00:01 |
                          |*  3 |    SORT ORDER BY STOPKEY  |      |  8582 |   251K|   115   (4)| 00:00:01 |
                          |*  4 |     FILTER                |      |       |       |            |          |
                          |*  5 |      HASH JOIN RIGHT OUTER|      |  8582 |   251K|   114   (3)| 00:00:01 |
                          |   6 |       TABLE ACCESS FULL   | HDR  | 10000 | 70000 |    21   (0)| 00:00:01 |
                          |*  7 |       TABLE ACCESS FULL   | DTL  | 19501 |   438K|    92   (3)| 00:00:01 |
                          ----------------------------------------------------------------------------------
                           
                          Predicate Information (identified by operation id):
                          ---------------------------------------------------
                             1 - filter(ROWNUM<=10)
                             3 - filter(ROWNUM<=10)
                             4 - filter(("HDR_IND"=0 OR "HDR_IND"=2) AND "DTL"."HDRID" IS NOT NULL
                                        OR "DTL"."HDRID" IS NULL)
                             5 - access("DTL"."HDRID"="HDR"."HDRID"(+))
                             7 - filter("DTL"."PROCESS_IND"='NEW' AND
                                        "DTL"."PROCESS_DATE"<SYSDATE@!+30)
                            
                          Statistics
                          ----------------------------------------------------------
                                    0  recursive calls
                                    0  db block gets
                                 1320  consistent gets
                                    0  physical reads
                                    0  redo size
                                  453  bytes sent via SQL*Net to client
                                  359  bytes received via SQL*Net from client
                                    2  SQL*Net roundtrips to/from client
                                    1  sorts (memory)
                                    0  sorts (disk)
                                   10  rows processed
                          As can be seen above, the number of consistent gets dropped from 2,437 down to 1,320.

                          Converting this SELECT into an UPDATE:
                          update dtl set process_ind = 'PROCESSED' where dtlid in (
                          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);
                          This is the autotrace generated output:
                          Execution Plan
                          ----------------------------------------------------------
                          Plan hash value: 404032855
                           
                          ------------------------------------------------------------------------------------------
                          | Id  | Operation                     | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
                          ------------------------------------------------------------------------------------------
                          |   0 | UPDATE STATEMENT              |          |    10 |   310 |   126   (4)| 00:00:01 |
                          |   1 |  UPDATE                       | DTL      |       |       |            |          |
                          |   2 |   NESTED LOOPS                |          |    10 |   310 |   126   (4)| 00:00:01 |
                          |   3 |    VIEW                       | VW_NSO_1 |    10 |   130 |   115   (4)| 00:00:01 |
                          |   4 |     SORT UNIQUE               |          |    10 |   130 |            |          |
                          |*  5 |      COUNT STOPKEY            |          |       |       |            |          |
                          |   6 |       VIEW                    |          | 12938 |   164K|   115   (4)| 00:00:01 |
                          |*  7 |        SORT ORDER BY STOPKEY  |          | 12938 |   379K|   115   (4)| 00:00:01 |
                          |*  8 |         FILTER                |          |       |       |            |          |
                          |*  9 |          HASH JOIN RIGHT OUTER|          | 12938 |   379K|   114   (3)| 00:00:01 |
                          |  10 |           TABLE ACCESS FULL   | HDR      | 10000 | 70000 |    21   (0)| 00:00:01 |
                          |* 11 |           TABLE ACCESS FULL   | DTL      | 19502 |   438K|    92   (3)| 00:00:01 |
                          |* 12 |    INDEX UNIQUE SCAN          | DTL_PK   |     1 |    18 |     0   (0)| 00:00:01 |
                          ------------------------------------------------------------------------------------------
                           
                          Predicate Information (identified by operation id):
                          ---------------------------------------------------
                             5 - filter(ROWNUM<=10)
                             7 - filter(ROWNUM<=10)
                             8 - filter(("HDR_IND"=0 OR "HDR_IND"=2) AND "DTL"."HDRID" IS NOT NULL OR
                                        "DTL"."HDRID" IS NULL)
                             9 - access("DTL"."HDRID"="HDR"."HDRID"(+))
                            11 - filter("DTL"."PROCESS_IND"='NEW' AND "DTL"."PROCESS_DATE"<SYSDATE@!+30)
                            12 - access("DTLID"="DTLID")
                           
                          Statistics
                          ----------------------------------------------------------
                                    1  recursive calls
                                   50  db block gets
                                 1329  consistent gets
                                    0  physical reads
                                 7444  redo size
                                  562  bytes sent via SQL*Net to client
                                  970  bytes received via SQL*Net from client
                                    3  SQL*Net roundtrips to/from client
                                    3  sorts (memory)
                                    0  sorts (disk)
                                   10  rows processed
                          As long as the SQL statement is equivalent, the above made one fewer full table scan of table DTL - the UNION ALL is gone also.

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

                            I tried your test case on 11.2.0.2, and on the second execution, cardinality feedback took place, dropping the consistent gets from 30,647 to 2,437 - yet the autotrace generated execution plan remained the same. When displaying the actual execution plan using DBMS_XPLAN.DISPLAY_CURSOR, the new plan was very similar to the plan that you show for the first UPDATE statement.

                            As long as the SQL statement is equivalent, the above made one fewer full table scan of table DTL - the UNION ALL is gone also.
                            Charles,

                            First, thanks for looking into the issue.
                            Few points that I realised I may not have clarified earlier:
                            1) I am facing this issue on 10.2.0.5 SE db so any query won't benefit from cardinality feedback
                            2) I must admit this process is not designed in the best way and my first thoughts, when asked to look into this, were to see if we can change the process/design. As expected, the design change was "not feasible". The DTL table is being used (by the java application) as a queuing table where records get added for being processed in future (indicated by PROCESS_DATE column value). It is not necessary that the records will be added to the table in the order of PROCESS_DATE for e.g. many thousands will be added to be processed on 15th day whereas thousands more will be added to be processed on 5th day and so on. My guess is that this, kind of, makes an index involving PROCESS_DATE (either on its own or as a leading column in a composite index) not very attractive (but I may be wrong).
                            In addition to that, this UPDATE is being used by the process to "mark" the ownership of the records for processing (so one records is not processed more than once...I know it screams out for a decent queuing mechanism but, as I said earlier, my hands are tied... :( ).
                            Another interesting part is, after processing the records in this table are deleted (again, apologies in advance for not-so-great design choices...).
                            3) I will try your suggestion but the problem is, because of a small mistake in the current UPDATE statement, the current UPDATE statement is actually following the execution plan that you have shown with your final example. The reasons that is not good enough is
                            a) while my example has a condition "PROCESS_DATE < (sysdate + 30)" the original query predicate is something like "PROCESS_DATE < sysdate". This means any run of the process is generally expected to process relatively small percentage of the records from the entire DTL table. This makes me believe that an index range scan on DTL_IDX1 would be better than a full table scan.
                            b) I may have been blinded by my narrow thinking but the fact that eventually only up to 10 rows (or 100 in case of original query) should be fed to the UPDATE statement is not being (probably inevitably) "considered" by the join. So the join ends up joining 2 relatively large row sources only to throw out most of them subsequently.
                            c) The pseudo code that (I believe) would best fit this situation will be something like this
                            for each record in (SELECT...FROM DTL WHERE PROCESS_DATE < sysdate and PROCESS_IND = 'NEW' and ... ORDER BY <col1, col2>)
                            if eligible records so far have reached the limit (e.g. 100) then
                            <get out of this loop>
                            end if
                            if HDRID is null then
                            <eligible for processing>
                            else
                            if HDRID in (SELECT...FROM HDR where where HDR_IND in (0,2)) then
                            <eligible for processing>
                            else
                            <discard record>
                            end if
                            end if

                            Finally, the original version of DTL table has more than 2 million records and is occupying more than 8000 blocks (standard 8KB block size). The current plan results in current UPDATE consuming about 8K LIOs and the pain is compounded by the fact that the java application spawns 5-6 threads, all issuing same query, with the intention of being able to process in parallel but on independent set of candidate records. The most common wait event is "read by other session" and at times the CPU spikes and load average on the 2-core CPU box goes above 4.
                            • 11. Re: UPDATE with ROWNUM (or TOP-N UPDATE)
                              Charles Hooper
                              Narendra,

                              Let's try another experiment, with a composite index on the DTLID, PROCESS_DATE, and PROCESS_IND columns of the DTL table to avoid the sort operation:
                              CREATE INDEX IND_DTL_TEST ON DTL(DTLID, PROCESS_DATE, PROCESS_IND);
                               
                              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 ;
                              The autotrace generated execution plan follows:
                              Execution Plan
                              ----------------------------------------------------------
                              Plan hash value: 1187173211
                               
                              ------------------------------------------------------------------------------------------------
                              | Id  | Operation                       | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
                              ------------------------------------------------------------------------------------------------
                              |   0 | SELECT STATEMENT                |              |    10 |   130 |     6   (0)| 00:00:01 |
                              |*  1 |  COUNT STOPKEY                  |              |       |       |            |          |
                              |   2 |   VIEW                          |              |    11 |   143 |     6   (0)| 00:00:01 |
                              |*  3 |    FILTER                       |              |       |       |            |          |
                              |   4 |     NESTED LOOPS OUTER          |              |    11 |   330 |     6   (0)| 00:00:01 |
                              |   5 |      TABLE ACCESS BY INDEX ROWID| DTL          | 19510 |   438K|     3   (0)| 00:00:01 |
                              |*  6 |       INDEX FULL SCAN           | IND_DTL_TEST |    17 |       |     2   (0)| 00:00:01 |
                              |   7 |      TABLE ACCESS BY INDEX ROWID| HDR          |     1 |     7 |     1   (0)| 00:00:01 |
                              |*  8 |       INDEX UNIQUE SCAN         | HDR_PK       |     1 |       |     0   (0)| 00:00:01 |
                              ------------------------------------------------------------------------------------------------
                               
                              Predicate Information (identified by operation id):
                              ---------------------------------------------------
                                 1 - filter(ROWNUM<=10)
                                 3 - filter(("HDR_IND"=0 OR "HDR_IND"=2) 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"(+))
                               
                              Statistics
                              ----------------------------------------------------------
                                        1  recursive calls
                                        0  db block gets
                                       83  consistent gets
                                       49  physical reads
                                        0  redo size
                                      453  bytes sent via SQL*Net to client
                                      359  bytes received via SQL*Net from client
                                        2  SQL*Net roundtrips to/from client
                                        0  sorts (memory)
                                        0  sorts (disk)
                                       10  rows processed
                              Notice that now we are down to just 83 consistent gets.

                              Let's try changing it to an UPDATE statement:
                              update dtl set process_ind = 'PROCESSED' where dtlid in (
                              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);
                               
                              ROLLBACK;
                              The autotrace generated execution plan follows:
                              Execution Plan
                              ----------------------------------------------------------
                              Plan hash value: 1199336142
                               
                              ----------------------------------------------------------------------------------------------
                              | Id  | Operation                     | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
                              ----------------------------------------------------------------------------------------------
                              |   0 | UPDATE STATEMENT              |              |    10 |   310 |   126   (4)| 00:00:01 |
                              |   1 |  UPDATE                       | DTL          |       |       |            |          |
                              |   2 |   NESTED LOOPS                |              |    10 |   310 |   126   (4)| 00:00:01 |
                              |   3 |    VIEW                       | VW_NSO_1     |    10 |   130 |   115   (4)| 00:00:01 |
                              |   4 |     SORT UNIQUE               |              |    10 |   130 |            |          |
                              |*  5 |      COUNT STOPKEY            |              |       |       |            |          |
                              |   6 |       VIEW                    |              | 12944 |   164K|   115   (4)| 00:00:01 |
                              |*  7 |        SORT ORDER BY STOPKEY  |              | 12944 |   379K|   115   (4)| 00:00:01 |
                              |*  8 |         FILTER                |              |       |       |            |          |
                              |*  9 |          HASH JOIN RIGHT OUTER|              | 12944 |   379K|   114   (3)| 00:00:01 |
                              |  10 |           TABLE ACCESS FULL   | HDR          | 10000 | 70000 |    21   (0)| 00:00:01 |
                              |* 11 |           TABLE ACCESS FULL   | DTL          | 19511 |   438K|    92   (3)| 00:00:01 |
                              |* 12 |    INDEX RANGE SCAN           | IND_DTL_TEST |     1 |    18 |     1   (0)| 00:00:01 |
                              ----------------------------------------------------------------------------------------------
                               
                              Predicate Information (identified by operation id):
                              ---------------------------------------------------
                                 5 - filter(ROWNUM<=10)
                                 7 - filter(ROWNUM<=10)
                                 8 - filter(("HDR_IND"=0 OR "HDR_IND"=2) AND "DTL"."HDRID" IS NOT NULL OR
                                            "DTL"."HDRID" IS NULL)
                                 9 - access("DTL"."HDRID"="HDR"."HDRID"(+))
                                11 - filter("DTL"."PROCESS_IND"='NEW' AND "DTL"."PROCESS_DATE"<SYSDATE@!+30)
                                12 - access("DTLID"="DTLID")
                               
                              Statistics
                              ----------------------------------------------------------
                                        1  recursive calls
                                       36  db block gets
                                     1328  consistent gets
                                        0  physical reads
                                     6976  redo size
                                      563  bytes sent via SQL*Net to client
                                      970  bytes received via SQL*Net from client
                                        3  SQL*Net roundtrips to/from client
                                        5  sorts (memory)
                                        0  sorts (disk)
                                       10  rows processed
                              Notice that the consistent gets are now 1,328 and the NESTED LOOPS OUTER operation has changed to a HASH JOIN RIGHT OUTER operation.

                              Let's get rid of the HASH JOIN RIGHT OUTER operation:
                              update dtl set process_ind = 'PROCESSED' where dtlid in (
                              select
                                dtlid
                              from
                                (select
                                   dtlid
                                 from
                                   (select /*+ INDEX(DTL) INDEX(HRD) NO_USE_HASH(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);
                               
                              Execution Plan
                              ----------------------------------------------------------
                              Plan hash value: 1025874300
                               
                              ------------------------------------------------------------------------------------------------------
                              | Id  | Operation                             | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
                              ------------------------------------------------------------------------------------------------------
                              |   0 | UPDATE STATEMENT                      |              |    10 |   310 |   630   (2)| 00:00:01 |
                              |   1 |  UPDATE                               | DTL          |       |       |            |          |
                              |   2 |   NESTED LOOPS                        |              |    10 |   310 |   630   (2)| 00:00:01 |
                              |   3 |    VIEW                               | VW_NSO_1     |    10 |   130 |   619   (1)| 00:00:01 |
                              |   4 |     SORT UNIQUE                       |              |    10 |   130 |            |          |
                              |*  5 |      COUNT STOPKEY                    |              |       |       |            |          |
                              |   6 |       VIEW                            |              | 12944 |   164K|   619   (1)| 00:00:01 |
                              |*  7 |        SORT ORDER BY STOPKEY          |              | 12944 |   379K|   619   (1)| 00:00:01 |
                              |*  8 |         FILTER                        |              |       |       |            |          |
                              |   9 |          MERGE JOIN OUTER             |              | 12944 |   379K|   618   (1)| 00:00:01 |
                              |  10 |           SORT JOIN                   |              | 19511 |   438K|   595   (1)| 00:00:01 |
                              |  11 |            TABLE ACCESS BY INDEX ROWID| DTL          | 19511 |   438K|   594   (1)| 00:00:01 |
                              |* 12 |             INDEX FULL SCAN           | IND_DTL_TEST | 19511 |       |   244   (1)| 00:00:01 |
                              |* 13 |           SORT JOIN                   |              | 10000 | 70000 |    22   (5)| 00:00:01 |
                              |  14 |            TABLE ACCESS FULL          | HDR          | 10000 | 70000 |    21   (0)| 00:00:01 |
                              |* 15 |    INDEX RANGE SCAN                   | IND_DTL_TEST |     1 |    18 |     1   (0)| 00:00:01 |
                              ------------------------------------------------------------------------------------------------------
                               
                              Predicate Information (identified by operation id):
                              ---------------------------------------------------
                                 5 - filter(ROWNUM<=10)
                                 7 - filter(ROWNUM<=10)
                                 8 - filter(("HDR_IND"=0 OR "HDR_IND"=2) AND "DTL"."HDRID" IS NOT NULL OR "DTL"."HDRID" IS
                                            NULL)
                                12 - access("DTL"."PROCESS_IND"='NEW' AND "DTL"."PROCESS_DATE"<SYSDATE@!+30)
                                     filter("DTL"."PROCESS_IND"='NEW' AND "DTL"."PROCESS_DATE"<SYSDATE@!+30)
                                13 - access("DTL"."HDRID"="HDR"."HDRID"(+))
                                     filter("DTL"."HDRID"="HDR"."HDRID"(+))
                                15 - access("DTLID"="DTLID")
                               
                              Statistics
                              ----------------------------------------------------------
                                        1  recursive calls
                                       34  db block gets
                                     1345  consistent gets
                                        0  physical reads
                                     6916  redo size
                                      563  bytes sent via SQL*Net to client
                                     1017  bytes received via SQL*Net from client
                                        3  SQL*Net roundtrips to/from client
                                        7  sorts (memory)
                                        0  sorts (disk)
                                       10  rows processed
                               
                              ROLLBACK;
                              The number of consistent gets jumped to 1,345 and now there is a MERGE JOIN OUTER operation.

                              One more time with an additional hint:
                              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);
                               
                              Execution Plan
                              ----------------------------------------------------------
                              Plan hash value: 1729303958
                               
                              ----------------------------------------------------------------------------------------------------
                              | Id  | Operation                           | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
                              ----------------------------------------------------------------------------------------------------
                              |   0 | UPDATE STATEMENT                    |              |    10 |   310 |  4470   (1)| 00:00:01 |
                              |   1 |  UPDATE                             | DTL          |       |       |            |          |
                              |   2 |   NESTED LOOPS                      |              |    10 |   310 |  4470   (1)| 00:00:01 |
                              |   3 |    VIEW                             | VW_NSO_1     |    10 |   130 |  4459   (1)| 00:00:01 |
                              |   4 |     SORT UNIQUE                     |              |    10 |   130 |            |          |
                              |*  5 |      COUNT STOPKEY                  |              |       |       |            |          |
                              |   6 |       VIEW                          |              | 12944 |   164K|  4459   (1)| 00:00:01 |
                              |*  7 |        FILTER                       |              |       |       |            |          |
                              |   8 |         NESTED LOOPS OUTER          |              | 12944 |   379K|  4459   (1)| 00:00:01 |
                              |   9 |          TABLE ACCESS BY INDEX ROWID| DTL          | 19511 |   438K|   594   (1)| 00:00:01 |
                              |* 10 |           INDEX FULL SCAN           | IND_DTL_TEST | 19511 |       |   244   (1)| 00:00:01 |
                              |  11 |          TABLE ACCESS BY INDEX ROWID| HDR          |     1 |     7 |     1   (0)| 00:00:01 |
                              |* 12 |           INDEX UNIQUE SCAN         | HDR_PK       |     1 |       |     0   (0)| 00:00:01 |
                              |* 13 |    INDEX RANGE SCAN                 | IND_DTL_TEST |     1 |    18 |     1   (0)| 00:00:01 |
                              ----------------------------------------------------------------------------------------------------
                                
                              Predicate Information (identified by operation id):
                              ---------------------------------------------------
                                 5 - filter(ROWNUM<=10)
                                 7 - filter(("HDR_IND"=0 OR "HDR_IND"=2) 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_IND"='NEW' AND "DTL"."PROCESS_DATE"<SYSDATE@!+30)
                                12 - access("DTL"."HDRID"="HDR"."HDRID"(+))
                                13 - access("DTLID"="DTLID")
                               
                              Statistics
                              ----------------------------------------------------------
                                        1  recursive calls
                                       36  db block gets
                                       88  consistent gets
                                        0  physical reads
                                        0  redo size
                                      563  bytes sent via SQL*Net to client
                                     1035  bytes received via SQL*Net from client
                                        3  SQL*Net roundtrips to/from client
                                        4  sorts (memory)
                                        0  sorts (disk)
                                       10  rows processed
                              Notice that the execution plan seems to match the one that we saw with the SELECT, and we dropped down to 88 consistent gets. So, in theory, this execution plan could produce a faster execution.

                              You could experiment with creating the additional index. Make certain that when all of the rows are deleted from the table, that you in fact use TRUNCATE TABLE, rather than a DELETE operation.

                              You stated that the most common wait event is "read by other session" - as I am sure that you know, that simply means that more than one session is trying to read the same block (or set of blocks) from disk at the same time.

                              I will withhold commenting on the design - it sounds as though that is out of your hands.

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

                                Let's try another experiment, with a composite index on the DTLID, PROCESS_DATE, and PROCESS_IND columns of the DTL table to avoid the sort operation:
                                Notice that the execution plan seems to match the one that we saw with the SELECT, and we dropped down to 88 consistent gets. So, in theory, this execution plan could produce a faster execution.
                                Charles,

                                Once again thanks for your patience. I must admit I did not think of that.
                                That does look interesting and, looking at the (final) plan, my first thoughts were that INDEX FULL SCAN will prove to be even worse than FULL TABLE SCAN as it will do all single-block reads and walk through entire index whereas FULL TABLE SCAN can, at least, perform multi-block reads and hence it may not be worth trying that. But obviously I was wrong. There might be just one issue in applying this to my original example as the ORDER BY clause in original UPDATE is based on a combination of an expression (DECODE) and PROCESS_DATE.
                                But this definitely looks like something I will try to use if it can reduce the resource consumption and post back my findings.
                                You could experiment with creating the additional index. Make certain that when all of the rows are deleted from the table, that you in fact use TRUNCATE TABLE, rather than a DELETE operation.
                                I can not use TRUNCATE as the process never deletes all records from the table but just the records that it processes successfully.
                                You stated that the most common wait event is "read by other session" - as I am sure that you know, that simply means that more than one session is trying to read the same block (or set of blocks) from disk at the same time.
                                I understand that but, as I said earlier, it is probably the attempt of JAVA application to process a bunch of records in parallel but in a particular order. I knew it is contradicting but now that I am writing it I am thinking of giving another try to see if I can push dev/design team on this.
                                Again, other sessions have to wait because any one session is doing a lot more I/O in order to update only small amount of rows. As DTL table is being accessed using FULL TABLE SCAN (and the table is quite large) during the UPDATE, it takes more time and since other threads will also eventually end up doing a FULL TABLE SCAN on the same table, they have no option but to wait. My theory was that if I can reduce the amount of blocks visited by the UPDATE statement (on DTL table), the impact will be of multiple magnitude since all "parallel" threads will be able to complete quickly and the process performance should improve overall.
                                I will withhold commenting on the design - it sounds as though that is out of your hands.
                                Thanks for being kind. I am hoping one day somewhere I would be able to win this battle ;)

                                Once again thank you very much for the suggestion.
                                • 13. Re: UPDATE with ROWNUM (or TOP-N UPDATE)
                                  Mohamed Houri
                                  Hi Charles,

                                  I have two questions
                                  Note that the above execute plan seems to be the same as what you are seeing in your UPDATE statement. Oracle Database 11.2.0.2 apparently switched from a FIRST_ROWS(10) optimization to an ALL_ROWS optimization - as suggested by the hash join operation
                                  Could you please help me understand how did you arrived to the that conclusion of a switch from FIRST_ROWS_10 to ALL_ROWS mode. Do you mean that a HASH JOIN operation will not occur under FIRST_ROWS mode?
                                  Let's try another experiment, with a composite index on the DTLID, PROCESS_DATE, and PROCESS_IND columns of the DTL table to avoid the sort operation:
                                  
                                  |   5 |      TABLE ACCESS BY INDEX ROWID| DTL          | 19510 |   438K|     3   (0)| 00:00:01 |
                                  |*  6 |       INDEX FULL SCAN           | IND_DTL_TEST |    17 |       |     2   (0)| 00:00:01 |
                                  I know that under FIRST_ROWS mode, in a presence of an adequat index, the CBO will prefer using an INDEX FULL SCAN in order to avoid a SORT OPERATION regardless of how costly is this INDEX FULL SCAN operation. This seems to happens under FIRST_ROWS mode only. However, I will be carreful also about FIRST_ROWS_N mode in this case.

                                  What do you think ?

                                  Thanks in advance

                                  Mohamed Houri
                                  • 14. Re: UPDATE with ROWNUM (or TOP-N UPDATE)
                                    Charles Hooper
                                    Hi Mohamed,
                                    Mohamed Houri wrote:
                                    Hi Charles,

                                    I have two questions
                                    Note that the above execute plan seems to be the same as what you are seeing in your UPDATE statement. Oracle Database 11.2.0.2 apparently switched from a FIRST_ROWS(10) optimization to an ALL_ROWS optimization - as suggested by the hash join operation
                                    Could you please help me understand how did you arrived to the that conclusion of a switch from FIRST_ROWS_10 to ALL_ROWS mode. Do you mean that a HASH JOIN operation will not occur under FIRST_ROWS mode?
                                    I should have looked at a 10053 trace before posting the above comment. Both execution plans were generated using FIRST_ROWS(10) optimization. However, there appears to be a bug in the cost calculation of the full table scan for table DTL, which caused the resc cost (cost for the serial execution of the step) of that full table scan to drop from 35.01 to just 2.02. That change, along with possibly over-stating the cost of the nested loop join caused the optimizer to switch from a nested loops join to a hash join. My guess regarding the apparent switch from an FIRST_ROWS(10) optimization to an ALL_ROWS optimization was incorrect.

                                    That said, I am not sure that a FIRST_ROWS(10) optimization should apply in this case because of the UNION ALL operation and the later ORDER BY clause. In fact, in this case it appears that without the correction made by cardinality feedback, that Oracle Database 11.2.0.2 is suffering from the same ROWNUM problem that affected Oracle Database versions prior to 11.2.0.1, where the affects of the ROWNUM are pushed too far into the execution plan. (See Optimizer choosing different plans when ROWNUM filter. [UPDATED: 11.2.0.1] for a demonstration of the ROWNUM problem that was supposed to be corrected in 11.2.0.1.)

                                    Here is a test script that starts by dropping the test composite index that I created, and then enables a 10053 trace, executes the SQL statement twice, and displays the execution plan for each execution:
                                    DROP INDEX IND_DTL_TEST;
                                     
                                    SET AUTOTRACE OFF
                                    SET LINESIZE 140
                                    SET PAGESIZE 1000
                                     
                                    ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT FOREVER, LEVEL 1';
                                    ALTER SESSION SET TRACEFILE_IDENTIFIER='TESTING 1';
                                     
                                    select /*+ GATHER_PLAN_STATISTICS */
                                      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;
                                     
                                    SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));
                                     
                                    ALTER SESSION SET TRACEFILE_IDENTIFIER='TESTING 2';
                                     
                                    select /*+ GATHER_PLAN_STATISTICS */
                                      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;
                                     
                                    SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));
                                     
                                    ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT OFF';
                                    For the first execution I received the following execution plan (note that the child number should be 0, not 1 - that number 1 was caused by failing to first drop by test index, dropping that index, and then re-excuting the script):
                                    PLAN_TABLE_OUTPUT
                                    ------------------------------------------------------------------------------------------------------------------------------------
                                    SQL_ID  4dwv2ktktv1ck, child number 1
                                    -------------------------------------
                                    select /*+ GATHER_PLAN_STATISTICS */   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
                                     
                                    Plan hash value: 2201656677
                                     
                                    ------------------------------------------------------------------------------------------------------------------------------------
                                    | Id  | Operation                          | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
                                    ------------------------------------------------------------------------------------------------------------------------------------
                                    |   0 | SELECT STATEMENT                   |          |      1 |        |     10 |00:00:00.05 |   31500 |       |       |          |
                                    |*  1 |  COUNT STOPKEY                     |          |      1 |        |     10 |00:00:00.05 |   31500 |       |       |          |
                                    |   2 |   VIEW                             |          |      1 |     22 |     10 |00:00:00.05 |   31500 |       |       |          |
                                    |*  3 |    SORT ORDER BY STOPKEY           |          |      1 |     22 |     10 |00:00:00.05 |   31500 |  2048 |  2048 | 2048  (0)|
                                    |   4 |     VIEW                           |          |      1 |     22 |   1367 |00:00:00.04 |   31500 |       |       |          |
                                    |   5 |      UNION-ALL                     |          |      1 |        |   1367 |00:00:00.04 |   31500 |       |       |          |
                                    |*  6 |       TABLE ACCESS FULL            | DTL      |      1 |     11 |    160 |00:00:00.01 |    1117 |       |       |          |
                                    |   7 |       NESTED LOOPS                 |          |      1 |        |   1207 |00:00:00.07 |   30383 |       |       |          |
                                    |   8 |        NESTED LOOPS                |          |      1 |     11 |   2838 |00:00:00.04 |   27566 |       |       |          |
                                    |   9 |         TABLE ACCESS BY INDEX ROWID| DTL      |      1 |     32 |  15034 |00:00:00.02 |   15068 |       |       |          |
                                    |* 10 |          INDEX RANGE SCAN          | DTL_IDX1 |      1 |        |  15034 |00:00:00.01 |      56 |       |       |          |
                                    |* 11 |         INDEX UNIQUE SCAN          | HDR_PK   |  15034 |      1 |   2838 |00:00:00.01 |   12498 |       |       |          |
                                    |* 12 |        TABLE ACCESS BY INDEX ROWID | HDR      |   2838 |      1 |   1207 |00:00:00.01 |    2817 |       |       |          |
                                    ------------------------------------------------------------------------------------------------------------------------------------
                                     
                                    Predicate Information (identified by operation id):
                                    ---------------------------------------------------
                                       1 - filter(ROWNUM<=10)
                                       3 - filter(ROWNUM<=10)
                                       6 - filter(("HDRID" IS NULL AND "PROCESS_IND"='NEW' AND "PROCESS_DATE"<SYSDATE@!+30))
                                      10 - access("DTL"."PROCESS_IND"='NEW' AND "DTL"."PROCESS_DATE"<SYSDATE@!+30)
                                      11 - access("DTL"."HDRID"="HDR"."HDRID")
                                      12 - filter(("HDR_IND"=0 OR "HDR_IND"=2))
                                    In the above, notice how far into the view the influence of the FIRST_ROWS(10) optimization caused by the ROWNUM<=10 predicate is pushed - it is not possible for the full table scan of DTL in the view to return just 11 rows - a later UNION ALL and an ORDER BY clause must be performed before the ROWNUM<=10.

                                    For the second execution I received the following execution plan (note that the child number should be 1, not 2 - that number 2 was caused by failing to first drop by test index, dropping that index, and then re-excuting the script):
                                    SQL_ID  4dwv2ktktv1ck, child number 2
                                    -------------------------------------
                                    select /*+ GATHER_PLAN_STATISTICS */   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
                                     
                                    Plan hash value: 986254152
                                     
                                    ---------------------------------------------------------------------------------------------------------------------
                                    | Id  | Operation               | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
                                    ---------------------------------------------------------------------------------------------------------------------
                                    |   0 | SELECT STATEMENT        |      |      1 |        |     10 |00:00:00.01 |    2437 |       |       |          |
                                    |*  1 |  COUNT STOPKEY          |      |      1 |        |     10 |00:00:00.01 |    2437 |       |       |          |
                                    |   2 |   VIEW                  |      |      1 |   2104 |     10 |00:00:00.01 |    2437 |       |       |          |
                                    |*  3 |    SORT ORDER BY STOPKEY|      |      1 |   2104 |     10 |00:00:00.01 |    2437 |  2048 |  2048 | 2048  (0)|
                                    |   4 |     VIEW                |      |      1 |   2104 |   1367 |00:00:00.01 |    2437 |       |       |          |
                                    |   5 |      UNION-ALL          |      |      1 |        |   1367 |00:00:00.01 |    2437 |       |       |          |
                                    |*  6 |       TABLE ACCESS FULL | DTL  |      1 |    160 |    160 |00:00:00.01 |    1117 |       |       |          |
                                    |*  7 |       HASH JOIN         |      |      1 |   1944 |   1207 |00:00:00.01 |    1320 |  1517K|  1517K| 1398K (0)|
                                    |*  8 |        TABLE ACCESS FULL| HDR  |      1 |   6667 |   4285 |00:00:00.01 |     203 |       |       |          |
                                    |*  9 |        TABLE ACCESS FULL| DTL  |      1 |  15034 |  15034 |00:00:00.01 |    1117 |       |       |          |
                                    ---------------------------------------------------------------------------------------------------------------------
                                     
                                    Predicate Information (identified by operation id):
                                    ---------------------------------------------------
                                       1 - filter(ROWNUM<=10)
                                       3 - filter(ROWNUM<=10)
                                       6 - filter(("HDRID" IS NULL AND "PROCESS_IND"='NEW' AND "PROCESS_DATE"<SYSDATE@!+30))
                                       7 - access("DTL"."HDRID"="HDR"."HDRID")
                                       8 - filter(("HDR_IND"=0 OR "HDR_IND"=2))
                                       9 - filter(("DTL"."PROCESS_IND"='NEW' AND "DTL"."PROCESS_DATE"<SYSDATE@!+30))
                                     
                                    Note
                                    -----
                                       - cardinality feedback used for this statement
                                    Let's take a look at a portion of the 10053 trace from the two executions - compare these sections side by side, possibly using a spreadsheet application:
                                    Without cardinality feedback:
                                    First K Rows: K = 10.00, N = 6846.00
                                    First K Rows: old pf = 0.9737479, new pf = 0.0016321
                                    Access path analysis for DTL
                                    ***************************************
                                    SINGLE TABLE ACCESS PATH (First K Rows)
                                      Single Table Cardinality Estimation for DTL[DTL] 
                                     
                                      Table: DTL  Alias: DTL
                                        Card: Original: 98.000000  Rounded: 32  Computed: 32.02  Non Adjusted: 32.02
                                      Access Path: TableScan
                                        Cost:  2.00  Resp: 2.00  Degree: 0
                                          Cost_io: 2.00  Cost_cpu: 62903
                                          Resp_io: 2.00  Resp_cpu: 62903
                                      
                                     
                                      Access Path: index (RangeScan)
                                        Index: DTL_IDX1
                                        resc_io: 35.00  resc_cpu: 262780
                                        ix_sel: 0.326777  ix_sel_with_filters: 0.326777 
                                        Cost: 35.01  Resp: 35.01  Degree: 1
                                      ****** trying bitmap/domain indexes ******
                                      ****** finished trying bitmap/domain indexes ******
                                      Best:: AccessPath: IndexRange
                                      Index: DTL_IDX1
                                             Cost: 35.01  Degree: 1  Resp: 35.01  Card: 32.02  Bytes: 30
                                     
                                    First K Rows: old pf = 1.0000000, new pf = 0.9844271
                                    Access path analysis for HDR
                                    ***************************************
                                    SINGLE TABLE ACCESS PATH (First K Rows)
                                      Single Table Cardinality Estimation for HDR[HDR] 
                                      Table: HDR  Alias: HDR
                                        Card: Original: 9845.000000  Rounded: 6563  Computed: 6563.33  Non Adjusted: 6563.33
                                      Access Path: TableScan
                                        Cost:  20.20  Resp: 20.20  Degree: 0
                                          Cost_io: 20.00  Cost_cpu: 4210334
                                          Resp_io: 20.00  Resp_cpu: 4210334
                                      ****** trying bitmap/domain indexes ******
                                      Access Path: index (FullScan)
                                        Index: HDR_PK
                                        resc_io: 19.00  resc_cpu: 2104307
                                        ix_sel: 1.000000  ix_sel_with_filters: 1.000000 
                                        Cost: 19.10  Resp: 19.10  Degree: 0
                                      ****** finished trying bitmap/domain indexes ******
                                      Best:: AccessPath: TableScan
                                             Cost: 20.20  Degree: 1  Resp: 20.20  Card: 6563.33  Bytes: 7
                                     
                                    First K Rows: unchanged join prefix len = 1
                                    Join order[2]:  DTL[DTL]#1  HDR[HDR]#0
                                     
                                    ***************
                                    Now joining: HDR[HDR]#0
                                    ***************
                                    NL Join
                                      Outer table: Card: 32.02  Cost: 35.01  Resp: 35.01  Degree: 1  Bytes: 23
                                    Access path analysis for HDR
                                      Inner table: HDR  Alias: HDR
                                      Access Path: TableScan
                                        NL Join:  Cost: 645.33  Resp: 645.33  Degree: 1
                                          Cost_io: 639.00  Cost_cpu: 134993459
                                          Resp_io: 639.00  Resp_cpu: 134993459
                                      Access Path: index (UniqueScan)
                                        Index: HDR_PK
                                        resc_io: 1.00  resc_cpu: 9275
                                        ix_sel: 0.000102  ix_sel_with_filters: 0.000102 
                                        NL Join : Cost: 41.32  Resp: 41.32  Degree: 1
                                          Cost_io: 41.30  Cost_cpu: 370035
                                          Resp_io: 41.30  Resp_cpu: 370035
                                      Access Path: index (AllEqUnique)
                                        Index: HDR_PK
                                        resc_io: 1.00  resc_cpu: 9275
                                        ix_sel: 0.000102  ix_sel_with_filters: 0.000102 
                                        NL Join : Cost: 41.32  Resp: 41.32  Degree: 1
                                          Cost_io: 41.30  Cost_cpu: 370035
                                          Resp_io: 41.30  Resp_cpu: 370035
                                      ****** trying bitmap/domain indexes ******
                                      ****** finished trying bitmap/domain indexes ******
                                     
                                      Best NL cost: 41.32
                                              resc: 41.32  resc_io: 41.30  resc_cpu: 370035
                                              resp: 41.32  resp_io: 41.30  resc_cpu: 370035
                                    Join Card:  11.009124 = = outer (32.024170) * inner (6563.333333) * sel (0.000052)
                                    Join Card - Rounded: 11 Computed: 11.01
                                      Outer table:  DTL  Alias: DTL
                                        resc: 91.77  card 19606.63  bytes: 23  deg: 1  resp: 91.77
                                      Inner table:  HDR  Alias: HDR
                                        resc: 21.20  card: 6666.67  bytes: 7  deg: 1  resp: 21.20
                                        using dmeth: 2  #groups: 1
                                        SORT ressource         Sort statistics
                                          Sort width:         598 Area size:     1048576 Max Area size:   104857600
                                          Degree:               1
                                          Blocks to Sort: 87 Row size:     36 Total Rows:          19607
                                          Initial runs:   1 Merge passes:  0 IO Cost / pass:          0
                                          Total IO sort cost: 0      Total CPU sort cost: 33908257
                                          Total Temp space used: 0
                                        SORT ressource         Sort statistics
                                          Sort width:         598 Area size:     1048576 Max Area size:   104857600
                                          Degree:               1
                                          Blocks to Sort: 15 Row size:     18 Total Rows:           6667
                                          Initial runs:   1 Merge passes:  0 IO Cost / pass:          0
                                          Total IO sort cost: 0      Total CPU sort cost: 25127658
                                          Total Temp space used: 0
                                      SM join: Resc: 115.74  Resp: 115.74  [multiMatchCost=0.00]
                                    SM Join
                                      SM cost: 115.74 
                                         resc: 115.74 resc_io: 111.00 resc_cpu: 101117761
                                         resp: 115.74 resp_io: 111.00 resp_cpu: 101117761
                                      Outer table:  DTL  Alias: DTL
                                        resc: 91.77  card 19606.63  bytes: 23  deg: 1  resp: 91.77
                                      Inner table:  HDR  Alias: HDR
                                        resc: 20.20  card: 6563.33  bytes: 7  deg: 1  resp: 20.20
                                        using dmeth: 2  #groups: 1
                                        Cost per ptn: 0.67  #ptns: 1
                                        hash_area: 256 (max=25600) buildfrag: 84  probefrag: 16  ppasses: 1
                                      Hash join: Resc: 112.64  Resp: 112.64  [multiMatchCost=0.00]
                                      Outer table:  HDR  Alias: HDR
                                        resc: 21.20  card 6666.67  bytes: 7  deg: 1  resp: 21.20
                                      Inner table:  DTL  Alias: DTL
                                        resc: 35.01  card: 32.02  bytes: 23  deg: 1  resp: 35.01
                                        using dmeth: 2  #groups: 1
                                        Cost per ptn: 0.55  #ptns: 1
                                        hash_area: 256 (max=25600) buildfrag: 16  probefrag: 1  ppasses: 1
                                      Hash join: Resc: 56.76  Resp: 56.76  [multiMatchCost=0.00]
                                    HA Join
                                      HA cost: 56.76 swapped 
                                         resc: 56.76 resc_io: 56.00 resc_cpu: 16192995
                                         resp: 56.76 resp_io: 56.00 resp_cpu: 16192995
                                    Best:: JoinMethod: NestedLoop
                                           Cost: 41.32  Degree: 1  Resp: 41.32  Card: 11.01 Bytes: 30
                                    Now compare with cardinality feedback (note the lines that I marked with ***):
                                    First K Rows: K = 10.00, N = 6671.00
                                    First K Rows: old pf = 0.9829571, new pf = 0.0015299
                                    Access path analysis for DTL
                                    ***************************************
                                    SINGLE TABLE ACCESS PATH (First K Rows)
                                      Single Table Cardinality Estimation for DTL[DTL] 
                                     
                                      Table: DTL  Alias: DTL
                                        Card: Original: 92.000000    >> Single Tab Card adjusted from:30.063507 to:15034.000000
                                      Rounded: 15034  Computed: 15034.00  Non Adjusted: 30.06
                                      Access Path: TableScan
                                        Cost:  2.02  Resp: 2.02  Degree: 0
                                          Cost_io: 2.00  Cost_cpu: 360003
                                          Resp_io: 2.00  Resp_cpu: 360003
                                     
                                      Access Path: index (RangeScan)
                                        Index: DTL_IDX1
                                        resc_io: 94.00  resc_cpu: 707135
                                        ix_sel: 1.000000  ix_sel_with_filters: 1.000000 
                                        Cost: 94.03  Resp: 94.03  Degree: 1
                                      ****** trying bitmap/domain indexes ******
                                      ****** finished trying bitmap/domain indexes ******
                                      Best:: AccessPath: TableScan
                                             Cost: 2.02  Degree: 1  Resp: 2.02  Card: 15034.00  Bytes: 30
                                      
                                     
                                    First K Rows: old pf = 1.0000000, new pf = 0.9799078
                                    Access path analysis for HDR
                                    ***************************************
                                    SINGLE TABLE ACCESS PATH (First K Rows)
                                      Single Table Cardinality Estimation for HDR[HDR] 
                                      Table: HDR  Alias: HDR
                                        Card: Original: 9800.000000  Rounded: 6533  Computed: 6533.33  Non Adjusted: 6533.33
                                      Access Path: TableScan
                                        Cost:  20.20  Resp: 20.20  Degree: 0
                                          Cost_io: 20.00  Cost_cpu: 4191812
                                          Resp_io: 20.00  Resp_cpu: 4191812
                                      ****** trying bitmap/domain indexes ******
                                      Access Path: index (FullScan)
                                        Index: HDR_PK
                                        resc_io: 19.00  resc_cpu: 2095307
                                        ix_sel: 1.000000  ix_sel_with_filters: 1.000000 
                                        Cost: 19.10  Resp: 19.10  Degree: 0
                                      ****** finished trying bitmap/domain indexes ******
                                      Best:: AccessPath: TableScan
                                             Cost: 20.20  Degree: 1  Resp: 20.20  Card: 6533.33  Bytes: 7
                                     
                                    First K Rows: unchanged join prefix len = 1
                                    Join order[2]:  DTL[DTL]#1  HDR[HDR]#0
                                     
                                    ***************
                                    Now joining: HDR[HDR]#0
                                    ***************
                                    NL Join
                                      Outer table: Card: 15034.00  Cost: 2.02  Resp: 2.02  Degree: 1  Bytes: 23
                                    Access path analysis for HDR
                                      Inner table: HDR  Alias: HDR
                                      Access Path: TableScan
                                        NL Join:  Cost: 284848.02  Resp: 284848.02  Degree: 1
                                          Cost_io: 281891.00  Cost_cpu: 63020065620
                                          Resp_io: 281891.00  Resp_cpu: 63020065620
                                      Access Path: index (UniqueScan)
                                        Index: HDR_PK
                                        resc_io: 1.00  resc_cpu: 9275
                                        ix_sel: 0.000102  ix_sel_with_filters: 0.000102 
                                        NL Join : Cost: 2950.27  Resp: 2950.27  Degree: 1
                                          Cost_io: 2947.89  Cost_cpu: 50649888
                                          Resp_io: 2947.89  Resp_cpu: 50649888
                                      Access Path: index (AllEqUnique)
                                        Index: HDR_PK
                                        resc_io: 1.00  resc_cpu: 9275
                                        ix_sel: 0.000102  ix_sel_with_filters: 0.000102 
                                        NL Join : Cost: 2950.27  Resp: 2950.27  Degree: 1
                                          Cost_io: 2947.89  Cost_cpu: 50649888  *** COST_IO increased from 41.30
                                          Resp_io: 2947.89  Resp_cpu: 50649888
                                      ****** trying bitmap/domain indexes ******
                                      ****** finished trying bitmap/domain indexes ******
                                     
                                      Best NL cost: 2950.27
                                              resc: 2950.27  resc_io: 2947.89  resc_cpu: 50649888
                                              resp: 2950.27  resp_io: 2947.89  resc_cpu: 50649888
                                    Join Card:  1944.487122 = = outer (15034.000000) * inner (6533.333333) * sel (0.000020)
                                    Join Card - Rounded: 1944 Computed: 1944.49
                                      Outer table:  DTL  Alias: DTL
                                        resc: 91.77  card 15034.00  bytes: 23  deg: 1  resp: 91.77
                                      Inner table:  HDR  Alias: HDR
                                        resc: 21.20  card: 6666.67  bytes: 7  deg: 1  resp: 21.20
                                        using dmeth: 2  #groups: 1
                                        SORT ressource         Sort statistics
                                          Sort width:         598 Area size:     1048576 Max Area size:   104857600
                                          Degree:               1
                                          Blocks to Sort: 67 Row size:     36 Total Rows:          15034
                                          Initial runs:   1 Merge passes:  0 IO Cost / pass:          0
                                          Total IO sort cost: 0      Total CPU sort cost: 30710874
                                          Total Temp space used: 0
                                        SORT ressource         Sort statistics
                                          Sort width:         598 Area size:     1048576 Max Area size:   104857600
                                          Degree:               1
                                          Blocks to Sort: 15 Row size:     18 Total Rows:           6667
                                          Initial runs:   1 Merge passes:  0 IO Cost / pass:          0
                                          Total IO sort cost: 0      Total CPU sort cost: 25127658
                                          Total Temp space used: 0
                                      SM join: Resc: 115.59  Resp: 115.59  [multiMatchCost=0.00]
                                    SM Join
                                      SM cost: 115.59 
                                         resc: 115.59 resc_io: 111.00 resc_cpu: 97828918
                                         resp: 115.59 resp_io: 111.00 resp_cpu: 97828918
                                      Outer table:  DTL  Alias: DTL
                                        resc: 91.77  card 15034.00  bytes: 23  deg: 1  resp: 91.77
                                      Inner table:  HDR  Alias: HDR
                                        resc: 20.20  card: 6533.33  bytes: 7  deg: 1  resp: 20.20
                                        using dmeth: 2  #groups: 1
                                        Cost per ptn: 0.64  #ptns: 1
                                        hash_area: 256 (max=25600) buildfrag: 65  probefrag: 16  ppasses: 1
                                      Hash join: Resc: 112.60  Resp: 112.60  [multiMatchCost=0.00]
                                      Outer table:  HDR  Alias: HDR
                                        resc: 21.20  card 6666.67  bytes: 7  deg: 1  resp: 21.20
                                      Inner table:  DTL  Alias: DTL
                                        resc: 2.02  card: 15034.00  bytes: 23  deg: 1  resp: 2.02  *** CARD increased from 32, RESP dropped from 35.01
                                        using dmeth: 2  #groups: 1
                                        Cost per ptn: 0.62  #ptns: 1
                                        hash_area: 256 (max=25600) buildfrag: 16  probefrag: 65  ppasses: 1
                                      Hash join: Resc: 23.83  Resp: 23.83  [multiMatchCost=0.00]
                                    HA Join
                                      HA cost: 23.83 swapped  *** HA COST was 56.76 
                                         resc: 23.83 resc_io: 23.00 resc_cpu: 17790418
                                         resp: 23.83 resp_io: 23.00 resp_cpu: 17790418
                                    First K Rows: copy A one plan, tab=HDR
                                    Best:: JoinMethod: Hash
                                           Cost: 23.83  Degree: 1  Resp: 23.83  Card: 1944.49 Bytes: 53
                                    So, the cost of the hash join dropped from 56.76 to 23.83, partially due to the IO cost of the full table scan of DTL dropping from 35.01 to just 2.02 - if the calculated cardinality increased, it would not seem that the cost of the full table scan should decrease unless there is some odd math due to the FIRST_ROWS(10) optimization... reading 10/32 of the table previously, compared to 10/15034 in the cardinality feedback influenced plan.
                                    Let's try another experiment, with a composite index on the DTLID, PROCESS_DATE, and PROCESS_IND columns of the DTL table to avoid the sort operation:
                                    
                                    |   5 |      TABLE ACCESS BY INDEX ROWID| DTL          | 19510 |   438K|     3   (0)| 00:00:01 |
                                    |*  6 |       INDEX FULL SCAN           | IND_DTL_TEST |    17 |       |     2   (0)| 00:00:01 |
                                    I know that under FIRST_ROWS mode, in a presence of an adequat index, the CBO will prefer using an INDEX FULL SCAN in order to avoid a SORT OPERATION regardless of how costly is this INDEX FULL SCAN operation. This seems to happens under FIRST_ROWS mode only. However, I will be carreful also about FIRST_ROWS_N mode in this case.

                                    What do you think ?

                                    Thanks in advance

                                    Mohamed Houri
                                    I think that you have correctly stated how the FIRST_ROWS optiization mode works. From http://docs.oracle.com/cd/B19306_01/server.102/b14237/initparams145.htm
                                    •first_rows_n: The optimizer uses a cost-based approach and optimizes with a goal of best response time to return the first n rows (where n = 1, 10, 100, 1000).
                                    •first_rows: The optimizer uses a mix of costs and heuristics to find a best plan for fast delivery of the first few rows.
                                    •all_rows: The optimizer uses a cost-based approach for all SQL statements in the session and optimizes with a goal of best throughput (minimum resource use to complete the entire statement).

                                    Charles Hooper
                                    http://hoopercharles.wordpress.com/
                                    IT Manager/Oracle DBA
                                    K&M Machine-Fabricating, Inc.
                                    1 2 3 Previous Next