1 2 3 Previous Next 32 Replies Latest reply: Feb 11, 2010 1:05 PM by Jonathan Lewis RSS

    Pagination query help needed for large table - force a different index

    751830
      I'm using a slight modification of the pagination query from over at Ask Tom's: [http://www.oracle.com/technology/oramag/oracle/07-jan/o17asktom.html]

      Mine looks like this when fetching the first 100 rows of all members with last name Smith, ordered by join date:
      SELECT members.*
      FROM members,
      (
          SELECT RID, rownum rnum
          FROM
          (
              SELECT rowid as RID 
              FROM members
              WHERE last_name = 'Smith'
              ORDER BY joindate
          ) 
          WHERE rownum <= 100 
      ) 
      WHERE rnum >= 1 
               and RID = members.rowid
      The difference between this and the one at Ask Tom's is that my innermost query just returns the ROWID. Then in the outermost query we join the ROWIDs returned to the members table, after we have pruned the ROWIDs down to only the chunk of 100 we want. This makes it MUCH faster (verifiably) on our large tables, as it is able to use the index on the innermost query (well... read on).
      The problem I have is this:
      SELECT rowid as RID 
      FROM members
      WHERE last_name = 'Smith'
      ORDER BY joindate
      This will use the index for the predicate column (last_name) instead of the unique index I have defined for the joindate column (joindate, sequence). (Verifiable with explain plan). It is much slower this way on a large table. So I can hint it using either of the following methods:
      SELECT /*+ index(members, joindate_idx) */ rowid as RID 
      FROM members
      WHERE last_name = 'Smith'
      ORDER BY joindate
      SELECT /*+ first_rows(100) */ rowid as RID 
      FROM members
      WHERE last_name = 'Smith'
      ORDER BY joindate
      Either way, it now uses the index of the ORDER BY column (joindate_idx), so now it is much faster as it does not have to do a sort (remember, VERY large table, millions of records). So that seems good. But now, on my outermost query, I join the rowid with the meaningful columns of data from the members table, as commented below:
      SELECT members.*      -- Select all data from members table
      FROM members,           -- members table added to FROM clause 
      (
          SELECT RID, rownum rnum
          FROM
          (
              SELECT /*+ index(members, joindate_idx) */ rowid as RID   -- Hint is ignored now that I am joining in the outer query
              FROM members
              WHERE last_name = 'Smith'
              ORDER BY joindate
          ) 
          WHERE rownum <= 100 
      ) 
      WHERE rnum >= 1 
              and RID = members.rowid           -- Merge the members table on the rowid we pulled from the inner queries
      Once I do this join, it goes back to using the predicate index (last_name) and has to perform the sort once it finds all matching values (which can be a lot in this table, there is high cardinality on some columns).

      So my question is, in the full query above, is there any way I can get it to use the ORDER BY column for indexing to prevent it from having to do a sort? The join is what causes it to revert back to using the predicate index, even with hints. Remove the join and just return the ROWIDs for those 100 records and it flies, even on 10 million records.

      It'd be great if there was some generic hint that could accomplish this, such that if we change the table/columns/indexes, we don't need to change the hint (the FIRST_ROWS hint is a good example of this, while the INDEX hint is the opposite), but any help would be appreciated. I can provide explain plans for any of the above if needed.

      Thanks!
        • 1. Re: Pagination query help needed for large table - force a different index
          user503699
          Jonathan has written something which you are probably looking for
          http://jonathanlewis.wordpress.com/2008/04/27/manual-optimisation/

          p.s. But, this is one approach I will be very sceptical to use...
          • 2. Re: Pagination query help needed for large table - force a different index
            751830
            Looks like that might be what I'm after, although after some preliminary attempts, I can't seem to get any different results, but I'm not sure I'm applying the hints properly. Thanks for the link though!
            • 3. Re: Pagination query help needed for large table - force a different index
              Lakmal Rajapakse
              I think a better solution is to change the last_name index to append the joindate column (modified index structure = last_name, joindate).
              • 4. Re: Pagination query help needed for large table - force a different index
                Centinul
                xaeryan wrote:
                The difference between this and the one at Ask Tom's is that my innermost query just returns the ROWID. Then in the outermost query we join the ROWIDs returned to the members table, after we have pruned the ROWIDs down to only the chunk of 100 we want. This makes it MUCH faster (verifiably) on our large tables, as it is able to use the index on the innermost query (well... read on).
                Have you tried a query like the following?
                SELECT  *
                FROM
                (
                        SELECT  MEMBERS.*
                        ,       ROW_NUMBER() OVER (ORDER BY JOINDATE) RN
                        FROM    MEMBERS
                        WHERE   LAST_NAME = 'Smith'
                )
                WHERE   RN BETWEEN 1 AND 100
                ORDER BY RN
                This avoids the second access to the MEMBERS table which if large could be expensive.

                As Lakmal Rajapakse said it may be beneficial to create a concatenated index of LAST_NAME and JOINDATE.

                If possible I like to stay away from hints, they should be the exception not the rule. I think with a proper query and index structure there should be no need to force a hint in this scenario with the exception of the FIRST_ROWS() hint. It is only asking for problems in the future especially between Oracle versions.
                • 5. Re: Pagination query help needed for large table - force a different index
                  Lakmal Rajapakse
                  Centinul,

                  I think going for an analytical funtion is the wrong thing to do.

                  The SQL that the OP provided is good and the second join to members is the right thing to do - basically by retrieving all the other columns later he avoids having to go to the table - everything can be satisfied by the index provided he has a concatenated index on last_name and joindate.

                  thanks
                  Lakmal...
                  • 6. Re: Pagination query help needed for large table - force a different index
                    Centinul
                    Lakmal Rajapakse wrote:
                    Centinul,

                    I think going for an analytical funtion is the wrong thing to do.
                    Not necessarily. There are cases where an analytical function produces a better result. See a thread I started: {thread:id=934895} If you read through the thread applying the ROW_NUMBER() analytical function actually produced better results.

                    Either way, it doesn't hurt to try.
                    The SQL that the OP provided is good and the second join to members is the right thing to do - basically by retrieving all the other columns later he avoids having to go to the table - everything can be satisfied by the index provided he has a concatenated index on last_name and joindate.
                    I disagree. I think it's ambiguous at best. In my opinion if I was reviewing this query during a code review it wouldn't be immediately noticeable to me why it was constructed this way.

                    If the OP is going to get the other column data from the table why perform a second join? Additionally, if an index was placed on the LAST_NAME, JOINDATE columns (and the CBO chose this as an access path) why would the second join be necessary? It was only implemented to try and achieve an indexed access path. In my opinion it seems like that is trying to circumvent what the optimizer is trying to achieve. In my opinion we should either look at adding an additional index as already suggested, or determining why the optimizer is making the decisions it is making by tracing the session using a 10053 trace.
                    • 7. Re: Pagination query help needed for large table - force a different index
                      Lakmal Rajapakse
                      Sorry - I do not agree - the user wants the first 100 rows returned as soon as possible - to do this he has to avoid doing a sort operation - I do not think you can achieve this using a row_number function.

                      However he does not need to join to members twice - but I don't think the impact of doing so will have any significant performance impact.

                      Edited by: Lakmal Rajapakse on 09-Feb-2010 08:47
                      • 8. Re: Pagination query help needed for large table - force a different index
                        Lakmal Rajapakse
                        Where joining twice will have a positive impact is when you attempt to retieive page 2, 3 and onwards. In this case the second join will mean you need to access the table to retreive at most 100 rows.
                        • 9. Re: Pagination query help needed for large table - force a different index
                          Centinul
                          Lakmal Rajapakse wrote:
                          Sorry - I do not agree - the user wants the first 100 rows returned as soon as possible - to do this he has to avoid doing a sort operation - I do not think you can achieve this using a row_number function.
                          Avoid a sort operation? That doesn't make sense. The only way it would make sense is if the OP wanted the first 100 random rows which doesn't appear to be the case. What I have done in the ROW_NUMBER() suggestion is transfer the ordering from the ORDER BY clause to the ORDER BY clause of the ROW_NUMBER() analytical function. It may provide an improvement in performance. See the thread I linked to for an example.

                          Either way a sort has to be done. However, with a proper index it may be done implicitly because the index is sorted by definition.
                          • 10. Re: Pagination query help needed for large table - force a different index
                            Lakmal Rajapakse
                            Ok my mistake - I tested it with a row_number and yes it does the same as rownum:
                            select * from (
                            select rowid, row_number() over (order by EVENT_DATETIME) x from aoswf.events
                            )
                            where x between 1 and 100
                            
                            Execution Plan
                            ----------------------------------------------------------
                            Plan hash value: 1417297245
                            
                            -------------------------------------------------------------------------------------
                            | Id  | Operation              | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
                            -------------------------------------------------------------------------------------
                            |   0 | SELECT STATEMENT       |            |   253M|  6032M|   260K  (1)| 00:52:06 |
                            |*  1 |  VIEW                  |            |   253M|  6032M|   260K  (1)| 00:52:06 |
                            |*  2 |   WINDOW NOSORT STOPKEY|            |   253M|  4826M|   260K  (1)| 00:52:06 |
                            |   3 |    INDEX FULL SCAN     | EVEN_IDX02 |   253M|  4826M|   260K  (1)| 00:52:06 |
                            -------------------------------------------------------------------------------------
                            
                            Predicate Information (identified by operation id):
                            ---------------------------------------------------
                            
                               1 - filter("X">=100 AND "X"<=200)
                               2 - filter(ROW_NUMBER() OVER ( ORDER BY "EVENT_DATETIME")<=200)
                            As you can see no sort is performed. But you must agree with me a second join is required.

                            Edited by: Lakmal Rajapakse on 09-Feb-2010 09:52
                            • 11. Re: Pagination query help needed for large table - force a different index
                              Centinul
                              Lakmal Rajapakse wrote:
                              ...But you must agree with me a second join is required.
                              No I do not agree. I don't see any point in selecting a ROWID from a table in sorted order, to join it to the SAME TABLE to retrieve the remainder of the columns. Do it in one step, less overhead, especially with the correctly indexed columns.

                              OP, do you think you could please provide the execution plans for both the poorly performing and better performing query? Please do something similar to the following:
                              SQL> set SERVEROUTPUT OFF
                              SQL> SELECT /*+ gather_plan_statistics */ * FROM DUAL; /* Your query here with the gather_plan_statistics hint */
                              SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));
                              Thanks!
                              • 12. Re: Pagination query help needed for large table - force a different index
                                Lakmal Rajapakse
                                OK here is an example to illustrate the advantage:
                                SQL> set autot traceonly
                                SQL> select * from (
                                  2  select a.*, rownum x  from
                                  3  (
                                  4  select a.* from aoswf.events a
                                  5  order by EVENT_DATETIME
                                  6  ) a
                                  7  where rownum <= 1200
                                  8  )
                                  9  where x >= 1100
                                 10  /
                                
                                101 rows selected.
                                
                                
                                Execution Plan
                                ----------------------------------------------------------
                                Plan hash value: 3711662397
                                
                                ---------------------------------------------------------------------------------------------
                                | Id  | Operation                      | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
                                ---------------------------------------------------------------------------------------------
                                |   0 | SELECT STATEMENT               |            |  1200 |   521K|   192   (0)| 00:00:03 |
                                |*  1 |  VIEW                          |            |  1200 |   521K|   192   (0)| 00:00:03 |
                                |*  2 |   COUNT STOPKEY                |            |       |       |            |          |
                                |   3 |    VIEW                        |            |  1200 |   506K|   192   (0)| 00:00:03 |
                                |   4 |     TABLE ACCESS BY INDEX ROWID| EVENTS     |   253M|    34G|   192   (0)| 00:00:03 |
                                |   5 |      INDEX FULL SCAN           | EVEN_IDX02 |  1200 |       |     2   (0)| 00:00:01 |
                                ---------------------------------------------------------------------------------------------
                                
                                Predicate Information (identified by operation id):
                                ---------------------------------------------------
                                
                                   1 - filter("X">=1100)
                                   2 - filter(ROWNUM<=1200)
                                
                                
                                Statistics
                                ----------------------------------------------------------
                                          0  recursive calls
                                          0  db block gets
                                        443  consistent gets
                                          0  physical reads
                                          0  redo size
                                      25203  bytes sent via SQL*Net to client
                                        281  bytes received via SQL*Net from client
                                          8  SQL*Net roundtrips to/from client
                                          0  sorts (memory)
                                          0  sorts (disk)
                                        101  rows processed
                                
                                SQL> 
                                SQL> 
                                SQL> select * from aoswf.events a, (
                                  2  select rid, rownum x  from
                                  3  (
                                  4  select rowid rid from aoswf.events a
                                  5  order by EVENT_DATETIME
                                  6  ) a
                                  7  where rownum <= 1200
                                  8  ) b
                                  9  where x >= 1100
                                 10  and a.rowid = rid
                                 11  /
                                
                                101 rows selected.
                                
                                
                                Execution Plan
                                ----------------------------------------------------------
                                Plan hash value: 2308864810
                                
                                ------------------------------------------------------------------------------------------
                                | Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
                                ------------------------------------------------------------------------------------------
                                |   0 | SELECT STATEMENT            |            |  1200 |   201K|   261K  (1)| 00:52:21 |
                                |   1 |  NESTED LOOPS               |            |  1200 |   201K|   261K  (1)| 00:52:21 |
                                |*  2 |   VIEW                      |            |  1200 | 30000 |   260K  (1)| 00:52:06 |
                                |*  3 |    COUNT STOPKEY            |            |       |       |            |          |
                                |   4 |     VIEW                    |            |   253M|  2895M|   260K  (1)| 00:52:06 |
                                |   5 |      INDEX FULL SCAN        | EVEN_IDX02 |   253M|  4826M|   260K  (1)| 00:52:06 |
                                |   6 |   TABLE ACCESS BY USER ROWID| EVENTS     |     1 |   147 |     1   (0)| 00:00:01 |
                                ------------------------------------------------------------------------------------------
                                
                                Predicate Information (identified by operation id):
                                ---------------------------------------------------
                                
                                   2 - filter("X">=1100)
                                   3 - filter(ROWNUM<=1200)
                                
                                
                                Statistics
                                ----------------------------------------------------------
                                          8  recursive calls
                                          0  db block gets
                                        117  consistent gets
                                          0  physical reads
                                          0  redo size
                                      27539  bytes sent via SQL*Net to client
                                        281  bytes received via SQL*Net from client
                                          8  SQL*Net roundtrips to/from client
                                          0  sorts (memory)
                                          0  sorts (disk)
                                        101  rows processed
                                • 13. Re: Pagination query help needed for large table - force a different index
                                  751830
                                  Centinul wrote:
                                  xaeryan wrote:
                                  The difference between this and the one at Ask Tom's is that my innermost query just returns the ROWID. Then in the outermost query we join the ROWIDs returned to the members table, after we have pruned the ROWIDs down to only the chunk of 100 we want. This makes it MUCH faster (verifiably) on our large tables, as it is able to use the index on the innermost query (well... read on).
                                  Have you tried a query like the following?
                                  SELECT  *
                                  FROM
                                  (
                                  SELECT  MEMBERS.*
                                  ,       ROW_NUMBER() OVER (ORDER BY JOINDATE) RN
                                  FROM    MEMBERS
                                  WHERE   LAST_NAME = 'Smith'
                                  )
                                  WHERE   RN BETWEEN 1 AND 100
                                  ORDER BY RN
                                  This avoids the second access to the MEMBERS table which if large could be expensive.

                                  As Lakmal Rajapakse said it may be beneficial to create a concatenated index of LAST_NAME and JOINDATE.

                                  If possible I like to stay away from hints, they should be the exception not the rule. I think with a proper query and index structure there should be no need to force a hint in this scenario with the exception of the FIRST_ROWS() hint. It is only asking for problems in the future especially between Oracle versions.
                                  I agree, I'd like to stay away from hints (with the exception of the FIRST ROWS hint) but here's the problem with the compound index: I'd like to create concatenated indexes, but this query was a very specific example. The user interface of our web application will be generating this query. Such that the user can perform a WHERE on any column and an ORDER BY on any column. Combined with 15+ columns, and you can see how compound indexes would be hard to define optimally. There's no real "typical" WHERE/ORDER BY the user will focus on.

                                  Yes, I have tried the query as you describe... here's the funny thing. I could swear a thousand times over I compared and pulling just the ROWID was faster because it could rely on the index ONLY until it narrowed it down to 100 rows. At that point, the join to the table is far less expensive, especially since it is done on ROWID. But now that I am trying to prove it, it seems the way you have described has a better explain plan. Not sure if that's because I've added the hint since I last tested, or maybe computed statistics on the table, but I'm going to test a bit more before I can say for sure it truly works better. I'll get back to you with results...
                                  • 14. Re: Pagination query help needed for large table - force a different index
                                    user503699
                                    Lakmal Rajapakse wrote:
                                    OK here is an example to illustrate the advantage:
                                    SQL> set autot traceonly
                                    SQL> select * from (
                                    2  select a.*, rownum x  from
                                    3  (
                                    4  select a.* from aoswf.events a
                                    5  order by EVENT_DATETIME
                                    6  ) a
                                    7  where rownum <= 1200
                                    8  )
                                    9  where x >= 1100
                                    10  /
                                    
                                    101 rows selected.
                                    
                                    
                                    Execution Plan
                                    ----------------------------------------------------------
                                    Plan hash value: 3711662397
                                    
                                    ---------------------------------------------------------------------------------------------
                                    | Id  | Operation                      | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
                                    ---------------------------------------------------------------------------------------------
                                    |   0 | SELECT STATEMENT               |            |  1200 |   521K|   192   (0)| 00:00:03 |
                                    |*  1 |  VIEW                          |            |  1200 |   521K|   192   (0)| 00:00:03 |
                                    |*  2 |   COUNT STOPKEY                |            |       |       |            |          |
                                    |   3 |    VIEW                        |            |  1200 |   506K|   192   (0)| 00:00:03 |
                                    |   4 |     TABLE ACCESS BY INDEX ROWID| EVENTS     |   253M|    34G|   192   (0)| 00:00:03 |
                                    |   5 |      INDEX FULL SCAN           | EVEN_IDX02 |  1200 |       |     2   (0)| 00:00:01 |
                                    ---------------------------------------------------------------------------------------------
                                    
                                    Predicate Information (identified by operation id):
                                    ---------------------------------------------------
                                    
                                    1 - filter("X">=1100)
                                    2 - filter(ROWNUM<=1200)
                                    
                                    
                                    Statistics
                                    ----------------------------------------------------------
                                    0  recursive calls
                                    0  db block gets
                                    443  consistent gets
                                    0  physical reads
                                    0  redo size
                                    25203  bytes sent via SQL*Net to client
                                    281  bytes received via SQL*Net from client
                                    8  SQL*Net roundtrips to/from client
                                    0  sorts (memory)
                                    0  sorts (disk)
                                    101  rows processed
                                    
                                    SQL> 
                                    SQL> 
                                    SQL> select * from aoswf.events a, (
                                    2  select rid, rownum x  from
                                    3  (
                                    4  select rowid rid from aoswf.events a
                                    5  order by EVENT_DATETIME
                                    6  ) a
                                    7  where rownum <= 1200
                                    8  ) b
                                    9  where x >= 1100
                                    10  and a.rowid = rid
                                    11  /
                                    
                                    101 rows selected.
                                    
                                    
                                    Execution Plan
                                    ----------------------------------------------------------
                                    Plan hash value: 2308864810
                                    
                                    ------------------------------------------------------------------------------------------
                                    | Id  | Operation                   | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
                                    ------------------------------------------------------------------------------------------
                                    |   0 | SELECT STATEMENT            |            |  1200 |   201K|   261K  (1)| 00:52:21 |
                                    |   1 |  NESTED LOOPS               |            |  1200 |   201K|   261K  (1)| 00:52:21 |
                                    |*  2 |   VIEW                      |            |  1200 | 30000 |   260K  (1)| 00:52:06 |
                                    |*  3 |    COUNT STOPKEY            |            |       |       |            |          |
                                    |   4 |     VIEW                    |            |   253M|  2895M|   260K  (1)| 00:52:06 |
                                    |   5 |      INDEX FULL SCAN        | EVEN_IDX02 |   253M|  4826M|   260K  (1)| 00:52:06 |
                                    |   6 |   TABLE ACCESS BY USER ROWID| EVENTS     |     1 |   147 |     1   (0)| 00:00:01 |
                                    ------------------------------------------------------------------------------------------
                                    
                                    Predicate Information (identified by operation id):
                                    ---------------------------------------------------
                                    
                                    2 - filter("X">=1100)
                                    3 - filter(ROWNUM<=1200)
                                    
                                    
                                    Statistics
                                    ----------------------------------------------------------
                                    8  recursive calls
                                    0  db block gets
                                    117  consistent gets
                                    0  physical reads
                                    0  redo size
                                    27539  bytes sent via SQL*Net to client
                                    281  bytes received via SQL*Net from client
                                    8  SQL*Net roundtrips to/from client
                                    0  sorts (memory)
                                    0  sorts (disk)
                                    101  rows processed
                                    Lakmal (and OP),

                                    Not sure what advantage you are trying to show here. But considering that we are talking about pagination query here and order of records is important, your 2 queries will not always generate output in same order. Here is the test case:
                                    SQL> select * from v$version ;
                                    
                                    
                                    
                                    BANNER
                                    
                                    ----------------------------------------------------------------
                                    
                                    Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
                                    
                                    PL/SQL Release 10.2.0.1.0 - Production
                                    
                                    CORE     10.2.0.1.0     Production
                                    
                                    TNS for Linux: Version 10.2.0.1.0 - Production
                                    
                                    NLSRTL Version 10.2.0.1.0 - Production
                                    
                                    
                                    
                                    SQL> show parameter optimizer
                                    
                                    
                                    
                                    NAME                                 TYPE        VALUE
                                    
                                    ------------------------------------ ----------- ------------------------------
                                    
                                    optimizer_dynamic_sampling           integer     2
                                    
                                    optimizer_features_enable            string      10.2.0.1
                                    
                                    optimizer_index_caching              integer     0
                                    
                                    optimizer_index_cost_adj             integer     100
                                    
                                    optimizer_mode                       string      ALL_ROWS
                                    
                                    optimizer_secure_view_merging        boolean     TRUE
                                    
                                    SQL> show parameter pga
                                    
                                    
                                    
                                    NAME                                 TYPE        VALUE
                                    
                                    ------------------------------------ ----------- ------------------------------
                                    
                                    pga_aggregate_target                 big integer 103M
                                    
                                    SQL> create table t nologging as select * from all_objects where 1 = 2 ;
                                    
                                    
                                    
                                    Table created.
                                    
                                    
                                    
                                    SQL> create index t_idx on t(last_ddl_time) nologging ;
                                    
                                    
                                    
                                    Index created.
                                    
                                    
                                    
                                    SQL> insert /*+ APPEND */ into t (owner, object_name, object_id, created, last_ddl_time) select owner, object_name, object_id, created, sysdate - dbms_random.value(1, 100) from all_objects order by dbms_random.random;
                                    
                                    
                                    
                                    40617 rows created.
                                    
                                    
                                    
                                    SQL> commit ;
                                    
                                    
                                    
                                    Commit complete.
                                    
                                    
                                    
                                    SQL> exec dbms_stats.gather_table_stats(user, 'T', cascade=>true);
                                    
                                    
                                    
                                    PL/SQL procedure successfully completed.
                                    
                                    
                                    
                                    SQL> select object_id, object_name, created from t, (select rid, rownum rn from (select rowid rid from t order by created desc) where rownum <= 1200) t1 where rn >= 1190 and t.rowid = t1.rid ;
                                    
                                    
                                    
                                     OBJECT_ID OBJECT_NAME                    CREATED
                                    
                                    ---------- ------------------------------ --------------------
                                    
                                         47686 ALL$OLAP2_JOIN_KEY_COLUMN_USES 28-JUL-2009 08:08:39
                                    
                                         47672 ALL$OLAP2_CUBE_DIM_USES        28-JUL-2009 08:08:39
                                    
                                         47681 ALL$OLAP2_CUBE_MEASURE_MAPS    28-JUL-2009 08:08:39
                                    
                                         47682 ALL$OLAP2_FACT_LEVEL_USES      28-JUL-2009 08:08:39
                                    
                                         47685 ALL$OLAP2_AGGREGATION_USES     28-JUL-2009 08:08:39
                                    
                                         47692 ALL$OLAP2_CATALOGS             28-JUL-2009 08:08:39
                                    
                                         47665 ALL$OLAPMR_FACTTBLKEYMAPS      28-JUL-2009 08:08:39
                                    
                                         47688 ALL$OLAP2_DIM_LEVEL_ATTR_MAPS  28-JUL-2009 08:08:39
                                    
                                         47689 ALL$OLAP2_DIM_LEVELS_KEYMAPS   28-JUL-2009 08:08:39
                                    
                                         47669 ALL$OLAP9I2_HIER_DIMENSIONS    28-JUL-2009 08:08:39
                                    
                                         47666 ALL$OLAP9I1_HIER_DIMENSIONS    28-JUL-2009 08:08:39
                                    
                                    
                                    
                                    11 rows selected.
                                    
                                    
                                    
                                    SQL> select object_id, object_name, last_ddl_time from t, (select rid, rownum rn from (select rowid rid from t order by last_ddl_time desc) where rownum <= 1200) t1 where rn >= 1190 and t.rowid = t1.rid ;
                                    
                                    
                                    
                                     OBJECT_ID OBJECT_NAME                    LAST_DDL_TIME
                                    
                                    ---------- ------------------------------ --------------------
                                    
                                         11749 /b9fe5b99_OraRTStatementComman 06-FEB-2010 03:43:49
                                    
                                         13133 oracle/jdbc/driver/OracleLog$3 06-FEB-2010 03:45:44
                                    
                                         37534 com/sun/mail/smtp/SMTPMessage  06-FEB-2010 03:46:14
                                    
                                         36145 /4e492b6f_SerProfileToClassErr 06-FEB-2010 03:11:09
                                    
                                         26815 /7a628fb8_DefaultHSBChooserPan 06-FEB-2010 03:26:55
                                    
                                         16695 /2940a364_RepIdDelegator_1_3   06-FEB-2010 03:38:17
                                    
                                         36539 sun/io/ByteToCharMacHebrew     06-FEB-2010 03:28:57
                                    
                                         14044 /d29b81e1_OldHeaders           06-FEB-2010 03:12:12
                                    
                                         12920 /25f8f3a5_BasicSplitPaneUI     06-FEB-2010 03:11:06
                                    
                                         42266 SI_GETCLRHSTGRFTR              06-FEB-2010 03:40:20
                                    
                                         15752 /2f494dce_JDWPThreadReference  06-FEB-2010 03:09:31
                                    
                                    
                                    
                                    11 rows selected.
                                    
                                    
                                    
                                    SQL> select object_id, object_name, last_ddl_time from (select t1.*, rownum rn from (select * from t order by last_ddl_time desc) t1 where rownum <= 1200) where rn >= 1190 ;
                                    
                                    
                                    
                                     OBJECT_ID OBJECT_NAME                    LAST_DDL_TIME
                                    
                                    ---------- ------------------------------ --------------------
                                    
                                         37534 com/sun/mail/smtp/SMTPMessage  06-FEB-2010 03:46:14
                                    
                                         13133 oracle/jdbc/driver/OracleLog$3 06-FEB-2010 03:45:44
                                    
                                         11749 /b9fe5b99_OraRTStatementComman 06-FEB-2010 03:43:49
                                    
                                         42266 SI_GETCLRHSTGRFTR              06-FEB-2010 03:40:20
                                    
                                         16695 /2940a364_RepIdDelegator_1_3   06-FEB-2010 03:38:17
                                    
                                         36539 sun/io/ByteToCharMacHebrew     06-FEB-2010 03:28:57
                                    
                                         26815 /7a628fb8_DefaultHSBChooserPan 06-FEB-2010 03:26:55
                                    
                                         14044 /d29b81e1_OldHeaders           06-FEB-2010 03:12:12
                                    
                                         36145 /4e492b6f_SerProfileToClassErr 06-FEB-2010 03:11:09
                                    
                                         12920 /25f8f3a5_BasicSplitPaneUI     06-FEB-2010 03:11:06
                                    
                                         15752 /2f494dce_JDWPThreadReference  06-FEB-2010 03:09:31
                                    
                                    
                                    
                                    11 rows selected.
                                    
                                    
                                    
                                    SQL> select object_id, object_name, last_ddl_time from t, (select rid, rownum rn from (select rowid rid from t order by last_ddl_time desc) where rownum <= 1200) t1 where rn >= 1190 and t.rowid = t1.rid order by last_ddl_time desc ;
                                    
                                    
                                    
                                     OBJECT_ID OBJECT_NAME                    LAST_DDL_TIME
                                    
                                    ---------- ------------------------------ --------------------
                                    
                                         37534 com/sun/mail/smtp/SMTPMessage  06-FEB-2010 03:46:14
                                    
                                         13133 oracle/jdbc/driver/OracleLog$3 06-FEB-2010 03:45:44
                                    
                                         11749 /b9fe5b99_OraRTStatementComman 06-FEB-2010 03:43:49
                                    
                                         42266 SI_GETCLRHSTGRFTR              06-FEB-2010 03:40:20
                                    
                                         16695 /2940a364_RepIdDelegator_1_3   06-FEB-2010 03:38:17
                                    
                                         36539 sun/io/ByteToCharMacHebrew     06-FEB-2010 03:28:57
                                    
                                         26815 /7a628fb8_DefaultHSBChooserPan 06-FEB-2010 03:26:55
                                    
                                         14044 /d29b81e1_OldHeaders           06-FEB-2010 03:12:12
                                    
                                         36145 /4e492b6f_SerProfileToClassErr 06-FEB-2010 03:11:09
                                    
                                         12920 /25f8f3a5_BasicSplitPaneUI     06-FEB-2010 03:11:06
                                    
                                         15752 /2f494dce_JDWPThreadReference  06-FEB-2010 03:09:31
                                    
                                    
                                    
                                    11 rows selected.
                                    
                                    
                                    
                                    SQL> set autotrace traceonly
                                    
                                    SQL> select object_id, object_name, last_ddl_time from t, (select rid, rownum rn from (select rowid rid from t order by last_ddl_time desc) where rownum <= 1200) t1 where rn >= 1190 and t.rowid = t1.rid order by last_ddl_time desc
                                    
                                      2  ;
                                    
                                    
                                    
                                    11 rows selected.
                                    
                                    
                                    
                                    
                                    
                                    Execution Plan
                                    
                                    ----------------------------------------------------------
                                    
                                    Plan hash value: 44968669
                                    
                                    
                                    
                                    -----------------------------------------------------------------------------------------
                                    
                                    | Id  | Operation                       | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
                                    
                                    -----------------------------------------------------------------------------------------
                                    
                                    |   0 | SELECT STATEMENT                |       |  1200 | 91200 |   180   (2)| 00:00:03 |
                                    
                                    |   1 |  SORT ORDER BY                  |       |  1200 | 91200 |   180   (2)| 00:00:03 |
                                    
                                    |*  2 |   HASH JOIN                     |       |  1200 | 91200 |   179   (2)| 00:00:03 |
                                    
                                    |*  3 |    VIEW                         |       |  1200 | 30000 |    98   (0)| 00:00:02 |
                                    
                                    |*  4 |     COUNT STOPKEY               |       |       |       |            |          |
                                    
                                    |   5 |      VIEW                       |       | 40617 |   475K|    98   (0)| 00:00:02 |
                                    
                                    |   6 |       INDEX FULL SCAN DESCENDING| T_IDX | 40617 |   793K|    98   (0)| 00:00:02 |
                                    
                                    |   7 |    TABLE ACCESS FULL            | T     | 40617 |  2022K|    80   (2)| 00:00:01 |
                                    
                                    -----------------------------------------------------------------------------------------
                                    
                                    
                                    
                                    Predicate Information (identified by operation id):
                                    
                                    ---------------------------------------------------
                                    
                                    
                                    
                                       2 - access("T".ROWID="T1"."RID")
                                    
                                       3 - filter("RN">=1190)
                                    
                                       4 - filter(ROWNUM<=1200)
                                    
                                    
                                    
                                    
                                    
                                    Statistics
                                    
                                    ----------------------------------------------------------
                                    
                                              1  recursive calls
                                    
                                              0  db block gets
                                    
                                            348  consistent gets
                                    
                                              0  physical reads
                                    
                                              0  redo size
                                    
                                           1063  bytes sent via SQL*Net to client
                                    
                                            385  bytes received via SQL*Net from client
                                    
                                              2  SQL*Net roundtrips to/from client
                                    
                                              1  sorts (memory)
                                    
                                              0  sorts (disk)
                                    
                                             11  rows processed
                                    
                                    
                                    
                                    SQL> select object_id, object_name, last_ddl_time from (select t1.*, rownum rn from (select * from t order by last_ddl_time desc) t1 where rownum <= 1200) where rn >= 1190 ;
                                    
                                    
                                    
                                    11 rows selected.
                                    
                                    
                                    
                                    
                                    
                                    Execution Plan
                                    
                                    ----------------------------------------------------------
                                    
                                    Plan hash value: 882605040
                                    
                                    
                                    
                                    ---------------------------------------------------------------------------------
                                    
                                    | Id  | Operation                | Name | Rows  | Bytes | Cost (%CPU)| Time     |
                                    
                                    ---------------------------------------------------------------------------------
                                    
                                    |   0 | SELECT STATEMENT         |      |  1200 | 62400 |    80   (2)| 00:00:01 |
                                    
                                    |*  1 |  VIEW                    |      |  1200 | 62400 |    80   (2)| 00:00:01 |
                                    
                                    |*  2 |   COUNT STOPKEY          |      |       |       |            |          |
                                    
                                    |   3 |    VIEW                  |      | 40617 |  1546K|    80   (2)| 00:00:01 |
                                    
                                    |*  4 |     SORT ORDER BY STOPKEY|      | 40617 |  2062K|    80   (2)| 00:00:01 |
                                    
                                    |   5 |      TABLE ACCESS FULL   | T    | 40617 |  2062K|    80   (2)| 00:00:01 |
                                    
                                    ---------------------------------------------------------------------------------
                                    
                                    
                                    
                                    Predicate Information (identified by operation id):
                                    
                                    ---------------------------------------------------
                                    
                                    
                                    
                                       1 - filter("RN">=1190)
                                    
                                       2 - filter(ROWNUM<=1200)
                                    
                                       4 - filter(ROWNUM<=1200)
                                    
                                    
                                    
                                    
                                    
                                    Statistics
                                    
                                    ----------------------------------------------------------
                                    
                                              0  recursive calls
                                    
                                              0  db block gets
                                    
                                            343  consistent gets
                                    
                                              0  physical reads
                                    
                                              0  redo size
                                    
                                           1063  bytes sent via SQL*Net to client
                                    
                                            385  bytes received via SQL*Net from client
                                    
                                              2  SQL*Net roundtrips to/from client
                                    
                                              1  sorts (memory)
                                    
                                              0  sorts (disk)
                                    
                                             11  rows processed
                                    
                                    
                                    
                                    SQL> select object_id, object_name, last_ddl_time from t, (select rid, rownum rn from (select rowid rid from t order by last_ddl_time desc) where rownum <= 1200) t1 where rn >= 1190 and t.rowid = t1.rid ;
                                    
                                    
                                    
                                    11 rows selected.
                                    
                                    
                                    
                                    
                                    
                                    Execution Plan
                                    
                                    ----------------------------------------------------------
                                    
                                    Plan hash value: 168880862
                                    
                                    
                                    
                                    ----------------------------------------------------------------------------------------
                                    
                                    | Id  | Operation                      | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
                                    
                                    ----------------------------------------------------------------------------------------
                                    
                                    |   0 | SELECT STATEMENT               |       |  1200 | 91200 |   179   (2)| 00:00:03 |
                                    
                                    |*  1 |  HASH JOIN                     |       |  1200 | 91200 |   179   (2)| 00:00:03 |
                                    
                                    |*  2 |   VIEW                         |       |  1200 | 30000 |    98   (0)| 00:00:02 |
                                    
                                    |*  3 |    COUNT STOPKEY               |       |       |       |            |          |
                                    
                                    |   4 |     VIEW                       |       | 40617 |   475K|    98   (0)| 00:00:02 |
                                    
                                    |   5 |      INDEX FULL SCAN DESCENDING| T_IDX | 40617 |   793K|    98   (0)| 00:00:02 |
                                    
                                    |   6 |   TABLE ACCESS FULL            | T     | 40617 |  2022K|    80   (2)| 00:00:01 |
                                    
                                    ----------------------------------------------------------------------------------------
                                    
                                    
                                    
                                    Predicate Information (identified by operation id):
                                    
                                    ---------------------------------------------------
                                    
                                    
                                    
                                       1 - access("T".ROWID="T1"."RID")
                                    
                                       2 - filter("RN">=1190)
                                    
                                       3 - filter(ROWNUM<=1200)
                                    
                                    
                                    
                                    
                                    
                                    Statistics
                                    
                                    ----------------------------------------------------------
                                    
                                              0  recursive calls
                                    
                                              0  db block gets
                                    
                                            349  consistent gets
                                    
                                              0  physical reads
                                    
                                              0  redo size
                                    
                                           1063  bytes sent via SQL*Net to client
                                    
                                            385  bytes received via SQL*Net from client
                                    
                                              2  SQL*Net roundtrips to/from client
                                    
                                              0  sorts (memory)
                                    
                                              0  sorts (disk)
                                    
                                             11  rows processed
                                    
                                    
                                    SQL> select object_id, object_name, last_ddl_time from (select t1.*, rownum rn from (select * from t order by last_ddl_time desc) t1 where rownum <= 1200) where rn >= 1190 order by last_ddl_time desc ;
                                    
                                    
                                    
                                    11 rows selected.
                                    
                                    
                                    
                                    
                                    
                                    Execution Plan
                                    
                                    ----------------------------------------------------------
                                    
                                    Plan hash value: 882605040
                                    
                                    
                                    
                                    ---------------------------------------------------------------------------------
                                    
                                    | Id  | Operation           | Name | Rows     | Bytes | Cost (%CPU)| Time     |
                                    
                                    ---------------------------------------------------------------------------------
                                    
                                    |   0 | SELECT STATEMENT      |     |  1200 | 62400 |    80   (2)| 00:00:01 |
                                    
                                    |*  1 |  VIEW                |     |  1200 | 62400 |    80   (2)| 00:00:01 |
                                    
                                    |*  2 |   COUNT STOPKEY       |     |     |     |          |          |
                                    
                                    |   3 |    VIEW            |     | 40617 |  1546K|    80   (2)| 00:00:01 |
                                    
                                    |*  4 |     SORT ORDER BY STOPKEY|     | 40617 |  2062K|    80   (2)| 00:00:01 |
                                    
                                    |   5 |      TABLE ACCESS FULL      | T     | 40617 |  2062K|    80   (2)| 00:00:01 |
                                    
                                    ---------------------------------------------------------------------------------
                                    
                                    
                                    
                                    Predicate Information (identified by operation id):
                                    
                                    ---------------------------------------------------
                                    
                                    
                                    
                                       1 - filter("RN">=1190)
                                    
                                       2 - filter(ROWNUM<=1200)
                                    
                                       4 - filter(ROWNUM<=1200)
                                    
                                    
                                    
                                    
                                    
                                    Statistics
                                    
                                    ----------------------------------------------------------
                                    
                                         175  recursive calls
                                    
                                           0  db block gets
                                    
                                         388  consistent gets
                                    
                                           0  physical reads
                                    
                                           0  redo size
                                    
                                           1063  bytes sent via SQL*Net to client
                                    
                                         385  bytes received via SQL*Net from client
                                    
                                           2  SQL*Net roundtrips to/from client
                                    
                                           4  sorts (memory)
                                    
                                           0  sorts (disk)
                                    
                                          11  rows processed
                                    
                                    
                                    
                                    SQL> set autotrace off
                                    
                                    SQL> spool off
                                    As you will see, the join query here has to have an ORDER BY clause at the end to ensure that records are correctly sorted. You can not rely on optimizer choosing NESTED LOOP join method and, as above example shows, when optimizer chooses HASH JOIN, oracle is free to return rows in no particular order.
                                    The query that does not involve join always returns rows in the desired order. Adding an ORDER BY does add a step in the plan for the query using join but does not affect the other query.
                                    1 2 3 Previous Next