This discussion is archived
1 2 Previous Next 16 Replies Latest reply: Mar 7, 2013 3:17 AM by xarx RSS

How to avoid sort when using a composite index?

xarx Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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_Arp Guru
    Currently Being Moderated
    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_Arp Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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_Arp Guru
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    ---

    Deleted duplicate message
  • 14. Re: How to avoid sort when using a composite index?
    Paul Horth Expert
    Currently Being Moderated
    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

Legend

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