1 2 Previous Next 16 Replies Latest reply: Mar 7, 2013 5:17 AM by xarx RSS

    How to avoid sort when using a composite index?

    xarx
      Create a test table:
      create table customer (first_name varchar2(20), last_name varchar2(20) not null, address varchar(20));
      
      insert into customer select dbms_random.string('U', 20), dbms_random.string('U', 20), dbms_random.string('U', 20) from dual connect by level <= 100000;
      commit;
      
      create index i_ln_fn_0 on customer(last_name, first_name,0); — just to be sure that all rows are indexed
      Now the explain plan:
      explain plan for
      select /*+ FIRST_ROWS(20) */ *
        from CUSTOMER
        where first_name like 'AB%'
          and first_name is not null
        order by last_name;
      
      select * from table(dbms_xplan.display);
       
      -------------------------------------------------------------------------------
      | Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
      -------------------------------------------------------------------------------
      |   0 | SELECT STATEMENT   |          |   197 | 12411 |   275   (2)| 00:00:04 |
      |   1 |  SORT ORDER BY     |          |   197 | 12411 |   275   (2)| 00:00:04 |
      |*  2 |   TABLE ACCESS FULL| CUSTOMER |   197 | 12411 |   274   (1)| 00:00:04 |
      -------------------------------------------------------------------------------
       
      Predicate Information (identified by operation id):
      ---------------------------------------------------
       
         2 - filter("FIRST_NAME" LIKE 'AB%' AND "FIRST_NAME" IS NOT NULL)
      But as I want only the first rows, I'd like to avoid the sort of the whole table. I'd like to have a plan like this:
      SELECT STATEMENT
          TABLE ACCESS BY ROWID (customer)
              INDEX FULL SCAN (i_ln_fn_0)
      How to persuade the db to avoid the sort?

      The problem is even worse. Even when I use the last_name only, everywhere:
      explain plan for
      select /*+ FIRST_ROWS(20) */ last_name
        from CUSTOMER
        where last_name like 'AB%'
          and last_name is not null
        order by last_name;
      
      select * from table(dbms_xplan.display);
      
      -------------------------------------------------------------------------------
      | Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
      -------------------------------------------------------------------------------
      |   0 | SELECT STATEMENT  |           |    17 |   357 |     4  (25)| 00:00:01 |
      |   1 |  SORT ORDER BY    |           |    17 |   357 |     4  (25)| 00:00:01 |
      |*  2 |   INDEX RANGE SCAN| I_LN_FN_0 |    17 |   357 |     3   (0)| 00:00:01 |
      -------------------------------------------------------------------------------
       
      Predicate Information (identified by operation id):
      ---------------------------------------------------
       
         2 - access("LAST_NAME" LIKE 'AB%')
             filter("LAST_NAME" LIKE 'AB%')
      Here the sort is really not necessary, but the db still uses it. Why?

      Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
      PL/SQL Release 11.2.0.1.0 - Production
      +"CORE     11.2.0.1.0     Production"+
      TNS for 64-bit Windows: Version 11.2.0.1.0 - Production
      NLSRTL Version 11.2.0.1.0 - Production
        • 1. Re: How to avoid sort when using a composite index?
          jeneesh
          For me it is not showing Sort..

          Are the stats updated?
          Plan hash value: 150617738
           
          ------------------------------------------------------------------------------
          | Id  | Operation        | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
          ------------------------------------------------------------------------------
          |   0 | SELECT STATEMENT |           |   152 |  1824 |     3   (0)| 00:00:01 |
          |*  1 |  INDEX RANGE SCAN| I_LN_FN_0 |   152 |  1824 |     3   (0)| 00:00:01 |
          ------------------------------------------------------------------------------
           
          Predicate Information (identified by operation id):
          ---------------------------------------------------
           
             1 - access("LAST_NAME" LIKE 'AB%')
                 filter("LAST_NAME" LIKE 'AB%')
           
          Note
          -----
             - dynamic sampling used for this statement
          Edited by: jeneesh on Mar 6, 2013 3:26 PM
          • 2. Re: How to avoid sort when using a composite index?
            _Karthick_
            Did you gather the stats? It does not sort for me

            SQL> explain plan for
              2  select /*+ FIRST_ROWS(20) */ last_name
              3    from CUSTOMER
              4   where last_name like 'AB%'
              5     and last_name is not null
              6   order by last_name; 
             
            Explained.
             
            SQL> select * from table(dbms_xplan.display);
             
            PLAN_TABLE_OUTPUT
            ------------------------------------------------------------------------------
            Plan hash value: 2032719711
             
            ------------------------------------------------------------------------------
            | Id  | Operation        | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
            ------------------------------------------------------------------------------
            |   0 | SELECT STATEMENT |           |    17 |   357 |     1   (0)| 00:00:01 |
            |*  1 |  INDEX RANGE SCAN| I_LN_FN_0 |    17 |   357 |     1   (0)| 00:00:01 |
            ------------------------------------------------------------------------------
             
            Predicate Information (identified by operation id):
            ---------------------------------------------------
             
               1 - access("LAST_NAME" LIKE 'AB%')
                   filter("LAST_NAME" LIKE 'AB%')
             
            14 rows selected.
            My version Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
            • 3. Re: How to avoid sort when using a composite index?
              _Karthick_
              jeneesh wrote:
              FIRST_ROWS hint is used to Tell the optimizer that - Choose a plan which will return the first few rows (20 rows in your case) in the least time..

              It will not restrict the output to 20 rows..

              So, ORACLE has to order the full data set ..
              If it scans throught the index it will avoid the sort, as the index is already sorted.
              • 4. Re: How to avoid sort when using a composite index?
                jeneesh
                Karthick_Arp wrote:
                jeneesh wrote:
                FIRST_ROWS hint is used to Tell the optimizer that - Choose a plan which will return the first few rows (20 rows in your case) in the least time..

                It will not restrict the output to 20 rows..

                So, ORACLE has to order the full data set ..
                If it scans throught the index it will avoid the sort, as the index is already sorted.
                Thanks, I had already updated before seeing your posts..

                I read it wrongly, initially..
                • 5. Re: How to avoid sort when using a composite index?
                  xarx
                  jeneesh wrote:
                  FIRST_ROWS hint is used to Tell the optimizer that - Choose a plan which will return the first few rows (20 rows in your case) in the least time..

                  It will not restrict the output to 20 rows..
                  Yes, I know. The application uses a select like this in a cursor to get about 20 first rows. So
                  select * from (
                    select /*+ FIRST_ROWS(20) */ last_name
                      from CUSTOMER
                      where last_name like 'AB%'
                        and last_name is not null
                      order by last_name
                  ) where rownum <20;
                  is not what I need to optimize.
                  • 6. Re: How to avoid sort when using a composite index?
                    jeneesh
                    xarx wrote:
                    jeneesh wrote:
                    FIRST_ROWS hint is used to Tell the optimizer that - Choose a plan which will return the first few rows (20 rows in your case) in the least time..

                    It will not restrict the output to 20 rows..
                    Yes, I know. The application uses a select like this in a cursor to get about 20 first rows. So
                    select * from (
                    select /*+ FIRST_ROWS(20) */ last_name
                    from CUSTOMER
                    where last_name like 'AB%'
                    and last_name is not null
                    order by last_name
                    ) where rownum <20;
                    is not what I need to optimize.
                    It was my mistake, I had updated my post..
                    • 7. Re: How to avoid sort when using a composite index?
                      xarx
                      Did you gather the stats? It does not sort for me
                      Yes, I did. I re-gathered stats:
                      exec dbms_stats.gather_schema_stats('TEST');
                      but the exec plan is still the same. The stats gathering procedure is using the default params.
                      • 8. Re: How to avoid sort when using a composite index?
                        _Karthick_
                        xarx wrote:
                        Did you gather the stats? It does not sort for me
                        Yes, I did. I re-gathered stats:
                        exec dbms_stats.gather_schema_stats('TEST');
                        but the exec plan is still the same. The stats gathering procedure is using the default params.
                        Try like this
                        exec dbms_stats.gather_table_stats(user, 'CUSTOMER', cascade=>TRUE)
                        • 9. Re: How to avoid sort when using a composite index?
                          BEDE
                          Try:

                          select/*+ index(t i_ln_fn_0) */ *
                          from CUSTOMER t
                          where first_name like 'AB%'
                          and first_name is not null
                          order by last_name;

                          Edited by: BEDE on Mar 6, 2013 11:20 AM
                          • 10. Re: How to avoid sort when using a composite index?
                            Paul  Horth
                            No sort for me.

                            BANNER
                            --------------------------------------------------------------------------------
                            Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
                            PL/SQL Release 11.2.0.2.0 - Production
                            CORE 11.2.0.2.0 Production
                            TNS for Linux: Version 11.2.0.2.0 - Production
                            NLSRTL Version 11.2.0.2.0 - Production
                            SQL> explain plan for
                            select /*+ FIRST_ROWS(20) */ last_name
                              from CUSTOMER
                              where last_name like 'AB%'
                                and last_name is not null
                              order by last_name;  2    3    4    5    6  
                            
                            Explained.
                            
                            SQL> select * from table(dbms_xplan.display);
                            
                            PLAN_TABLE_OUTPUT
                            --------------------------------------------------------------------------------
                            Plan hash value: 150617738
                            
                            ------------------------------------------------------------------------------
                            | Id  | Operation        | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
                            ------------------------------------------------------------------------------
                            |   0 | SELECT STATEMENT |           |   148 |  1776 |     3   (0)| 00:00:01 |
                            |*  1 |  INDEX RANGE SCAN| I_LN_FN_0 |   148 |  1776 |     3   (0)| 00:00:01 |
                            ------------------------------------------------------------------------------
                            
                            Predicate Information (identified by operation id):
                            ---------------------------------------------------
                            
                            PLAN_TABLE_OUTPUT
                            --------------------------------------------------------------------------------
                            
                               1 - access("LAST_NAME" LIKE 'AB%')
                                   filter("LAST_NAME" LIKE 'AB%')
                            
                            Note
                            -----
                               - dynamic sampling used for this statement (level=2)
                            
                            18 rows selected.
                            • 11. Re: How to avoid sort when using a composite index?
                              xarx
                              OK, I dropped everything and recreated it from the scripts I'd posted here. I also re-gathered the table stats using
                              exec dbms_stats.gather_table_stats('TEST','CUSTOMER',cascade=>true);
                              as suggested. But the result is still the same, oracle uses still the same plans including explicit sorting, as before. :-(
                              Using the hint
                              /*+ index(CUSTOMER I_LN_FN_0) */
                              has no sense as the index is used already. I've tried also <tt>index_asc</tt> etc. hints but with no change.

                              Why are my plans so different from yours?

                              Edited by: xarx on 06-Mar-2013 03:26
                              • 12. Re: How to avoid sort when using a composite index?
                                xarx
                                I tested the scripts on our production server:

                                Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
                                PL/SQL Release 11.2.0.2.0 - Production
                                +"CORE 11.2.0.2.0Production"+
                                TNS for IBM/AIX RISC System/6000: Version 11.2.0.2.0 - Production
                                NLSRTL Version 11.2.0.2.0 - Production

                                The result is the same as before, i.e. the database uses explicit sorts.

                                How is it possible that none of you is able to reproduce the behaviour, while on both of my I get the bad execution plan?

                                The first server (11.2.0.1.0) where I tested yesterday was a clean install of the database on my notebook, without any tuning. I don't know the exact settings of the production server (11.2.0.2.0) where I tested today, but I haven't found any strange non-default settings in V$PARAMETER.
                                • 13. Re: How to avoid sort when using a composite index?
                                  xarx
                                  ---

                                  Deleted duplicate message
                                  • 14. Re: How to avoid sort when using a composite index?
                                    Paul  Horth
                                    xarx wrote:
                                    I tested the scripts on our production server:

                                    Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
                                    PL/SQL Release 11.2.0.2.0 - Production
                                    +"CORE 11.2.0.2.0Production"+
                                    TNS for IBM/AIX RISC System/6000: Version 11.2.0.2.0 - Production
                                    NLSRTL Version 11.2.0.2.0 - Production

                                    The result is the same as before, i.e. the database uses explicit sorts.

                                    How is it possible that none of you is able to reproduce the behaviour, while on both of my I get the bad execution plan?

                                    The first server (11.2.0.1.0) where I tested yesterday was a clean install of the database on my notebook, without any tuning. I don't know the exact settings of the production server (11.2.0.2.0) where I tested today, but I haven't found any strange non-default settings in V$PARAMETER.
                                    It may be to do with the NLS_SORT (and/or NLS_COMP) parameter. From http://docs.oracle.com/cd/E24693_01/server.11203/e24448/initparams152.htm

                                    >
                                    The value of NLS_SORT affects execution plans of queries. Because a standard index cannot be used as a source of values sorted in a linguistic order, an explicit sort operation must usually be performed instead of an index range scan. A functional index on the NLSSORT function may be defined to provide values sorted in a linguistic order and reintroduce the index range scan to the execution plan.
                                    >

                                    Edited by: Paul Horth on Mar 7, 2013 10:55 AM
                                    Added NLS_COMP
                                    1 2 Previous Next