Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: How to optimize large ORDER BY queries

Re: How to optimize large ORDER BY queries

From: Keith Boulton <kboulton_at_ntlworldgoaway.com>
Date: Sat, 21 Jul 2001 13:12:16 GMT
Message-ID: <Ocf67.861$Icm.129368157@news.randori.com>

Barry Frank wrote:

> Can anyone offer a technique for the following scenario:
>
> I need to set up a "ticket" table that will serve as a queue. The
> query that gets tickets off the table needs to ORDER the records so
> that the tickets are processed FIFO. It is a composite ORDER BY
> clause, using CREATED_DAY, PRIORITY, and TICKET_ID.
>
> At times, records will backlog (to the tune of hundreds of thousands).
> When this happens, the dequeueing query takes too long, plus processor
> usage maxes out at 100%.
>
> My question is: can I somehow set it up so that the table's records
> are physically sorted, so I can simply use ROWNUM = 1 to pull the
> record I want? Or is there any technique I can use that specifically
> optimizes ordering? I need to use the index that affects the filter;
> i.e. the WHERE clause (on different fields). So an index on the ORDER
> BY fields is not helpful.
>
> Any help is appreciated.
>
> - Barry Frank

Create a test table containing .5m rows

        set autocommit on

        drop table ticket;

        create table ticket
        ( a number not null
        , b varchar2(100)
        , created_day date not null
        , priority number(1) not null
        , ticket_id number not null
        );


        insert into ticket( a , b , created_day , priority , ticket_id )
        values( 1, 'asdfasdf ;asdflisdvy cgasdfuoig pdsfluiapsdfiua sdf;lne lkaefef', 
trunc(sysdate), 1, 1);
        /
        /
        /
        /
        /
        /
        /
        /
        /
        /
        /
        /
        /
        /
        /
        /
        /
        /


        select count(*) from ticket;


Test query with column a being used as filter criteria, no index, does a full table scan and a sort. Execution time within a pl/sql block is .54 seconds

        SQL> truncate table plan_table;
        SQL> explain plan for
        2  select *
        3  from   ticket
        4  where  a = 6
        5  order by created_day, priority, ticket_id;
        SQL> set echo off

        Plan Table
        
----------------------------------------------------------------------------------------------------------------------
        | Operation                  |  Name              |  Rows | Bytes|  Cost  |  TQ  |IN-OUT| 
PQ Distrib | Pstart| Pstop |
        
----------------------------------------------------------------------------------------------------------------------
        | SELECT STATEMENT           |                    |       |      |        |      |      |   
         |       |       |
        |  SORT ORDER BY             |                    |       |      |        |      |      |   
         |       |       |
        |   TABLE ACCESS FULL        |TICKET              |       |      |        |      |      |   
         |       |       |
        
----------------------------------------------------------------------------------------------------------------------

        SQL>
        SQL> set timing on
        SQL> declare
        2       cursor c is
        3       select *
        4       from   ticket
        5       where  a = 6
        6       order by created_day, priority, ticket_id;
        7       r c%rowtype;
        8  begin
        9       open c;
        10      fetch c into r;
        11      close c;
        12  end;
        13  /
        Elapsed: 00:00:00.54


Create index on a, no statistics so rbo should use index on a. Does an index range scan and sort. Elapsed time .45 seconds

        SQL> create index ticket_a on ticket(a);
        SQL>
        SQL> truncate table plan_table;
        SQL> explain plan for
        2  select *
        3  from   ticket
        4  where  a = 6
        5  order by created_day, priority, ticket_id;
        SQL> set echo off

        Plan Table
        
----------------------------------------------------------------------------------------------------------------------
        | Operation                  |  Name              |  Rows | Bytes|  Cost  |  TQ  |IN-OUT| 
PQ Distrib | Pstart| Pstop |
        
----------------------------------------------------------------------------------------------------------------------
        | SELECT STATEMENT           |                    |       |      |        |      |      |   
         |       |       |
        |  SORT ORDER BY             |                    |       |      |        |      |      |   
         |       |       |
        |   TABLE ACCESS BY INDEX ROW|TICKET              |       |      |        |      |      |   
         |       |       |
        |    INDEX RANGE SCAN        |TICKET_A            |       |      |        |      |      |   
         |       |       |
        
----------------------------------------------------------------------------------------------------------------------
        SQL>
        SQL> set timing on
        SQL> declare
        2       cursor c is
        3       select *
        4       from   ticket
        5       where  a = 6
        6       order by created_day, priority, ticket_id;
        7       r c%rowtype;
        8  begin
        9       open c;
        10      fetch c into r;
        11      close c;
        12  end;
        13  /
        Elapsed: 00:00:00.45


Analyse the table. Does a full table scan and a sort. Elapsed time is slightly faster at 0.48s, maybe because of caching.

        SQL> analyze table ticket compute statistics;
        SQL>
        SQL> truncate table plan_table;
        SQL> explain plan for
        2  select *
        3  from   ticket
        4  where  a = 6
        5  order by created_day, priority, ticket_id;
        SQL> set echo off

        Plan Table
        
----------------------------------------------------------------------------------------------------------------------
        | Operation                  |  Name              |  Rows | Bytes|  Cost  |  TQ  |IN-OUT| 
PQ Distrib | Pstart| Pstop |
        
----------------------------------------------------------------------------------------------------------------------
        | SELECT STATEMENT           |                    |     5K|  395K|   1040 |      |      |   
         |       |       |
        |  SORT ORDER BY             |                    |     5K|  395K|   1040 |      |      |   
         |       |       |
        |   TABLE ACCESS FULL        |TICKET              |     5K|  395K|    970 |      |      |   
         |       |       |
        
----------------------------------------------------------------------------------------------------------------------
        SQL>
        SQL> set timing on
        SQL> declare
        2       cursor c is
        3       select *
        4       from   ticket
        5       where  a = 6
        6       order by created_day, priority, ticket_id;
        7       r c%rowtype;
        8  begin
        9       open c;
        10      fetch c into r;
        11      close c;
        12  end;
        13  /
        Elapsed: 00:00:00.48


The optimiser is able to reduce the cost of the sort if the selection of one row only is included in the query itself, although it has little effect here. Elapsed

        SQL> truncate table plan_table;
        SQL> explain plan for
        2  select * from (
        3  select *
        4  from   ticket
        5  where  a = 6
        6  order by created_day, priority, ticket_id)
        7  where rownum = 1;
        SQL> set echo off

        Plan Table
        
----------------------------------------------------------------------------------------------------------------------
        | Operation                  |  Name              |  Rows | Bytes|  Cost  |  TQ  |IN-OUT| 
PQ Distrib | Pstart| Pstop |
        
----------------------------------------------------------------------------------------------------------------------
        | SELECT STATEMENT           |                    |     1 |  506K|   1040 |      |      |   
         |       |       |
        |  COUNT STOPKEY             |                    |       |      |        |      |      |   
         |       |       |
        |   VIEW                     |                    |     5K|  506K|   1040 |      |      |   
         |       |       |
        |    SORT ORDER BY STOPKEY   |                    |     5K|  395K|   1040 |      |      |   
         |       |       |
        |     TABLE ACCESS FULL      |TICKET              |     5K|  395K|    970 |      |      |   
         |       |       |
        
----------------------------------------------------------------------------------------------------------------------
        SQL>
        SQL> set timing on
        SQL> declare
        2       cursor c is
        3       select *
        4       from ( select *
        5              from   ticket
        6              where  a = 6
        7              order by created_day, priority, ticket_id )
        8       where rownum = 1;
        9       r c%rowtype;
        10  begin
        11      open c;
        12      fetch c into r;
        13      close c;
        14  end;
        15  /
        Elapsed: 00:00:00.44

Create an index on created_day, priority, ticket_id. The optimiser uses the index to avoid the sort. This might require a hint to be used for different query/data. Elapsed time 0.02s

        SQL> create index ticket_b on ticket( created_day, priority, ticket_id );
        SQL>
        SQL> truncate table plan_table;
        SQL> explain plan for
        2  select *
        3  from   ticket
        4  where  a = 6
        5  order by created_day, priority, ticket_id;
        SQL> set echo off

        Plan Table
        
----------------------------------------------------------------------------------------------------------------------
        | Operation                  |  Name              |  Rows | Bytes|  Cost  |  TQ  |IN-OUT| 
PQ Distrib | Pstart| Pstop |
        
----------------------------------------------------------------------------------------------------------------------
        | SELECT STATEMENT           |                    |     5K|  395K|    826 |      |      |   
         |       |       |
        |  TABLE ACCESS BY INDEX ROWI|TICKET              |     5K|  395K|    826 |      |      |   
         |       |       |
        |   INDEX FULL SCAN          |TICKET_B            |     5K|      |     26 |      |      |   
         |       |       |
        
----------------------------------------------------------------------------------------------------------------------
        SQL>
        SQL> set timing on
        SQL> declare
        2       cursor c is
        3       select *
        4       from   ticket
        5       where  a = 6
        6       order by created_day, priority, ticket_id;
        7       r c%rowtype;
        8  begin
        9       open c;
        10      fetch c into r;
        11      close c;
        12  end;
        13  /
        Elapsed: 00:00:00.02

Move the filter outside the order by ensures the access path. Elapsed 0.02
        SQL> truncate table plan_table;
        SQL> explain plan for
        2  select /*+ first_rows */ *
        3  from ( select *
        4           from   ticket
        5           order by created_day, priority, ticket_id ) orderticket
        6  where orderticket.a=6;
        SQL> set echo off

        Plan Table
        
----------------------------------------------------------------------------------------------------------------------
        | Operation                  |  Name              |  Rows | Bytes|  Cost  |  TQ  |IN-OUT| 
PQ Distrib | Pstart| Pstop |
        
----------------------------------------------------------------------------------------------------------------------
        | SELECT STATEMENT           |                    |     5K|  506K|    826 |      |      |   
         |       |       |
        |  VIEW                      |                    |     5K|  506K|    826 |      |      |   
         |       |       |
        |   TABLE ACCESS BY INDEX ROW|TICKET              |     5K|  395K|    826 |      |      |   
         |       |       |
        |    INDEX FULL SCAN         |TICKET_B            |     5K|      |     26 |      |      |   
         |       |       |
        
----------------------------------------------------------------------------------------------------------------------
        SQL>
        SQL> set timing on
        SQL> declare
        2       cursor c is
        3       select /*+ first_rows */ *
        4       from ( select *
        5              from   ticket
        6              order by created_day, priority, ticket_id ) orderticket
        7        where orderticket.a=6;
        8        r c%rowtype;
        9  begin
        10      open c;
        11      fetch c into r;
        12      close c;
        13  end;
        14  /
        Elapsed: 00:00:00.02


The filter can be moved entirely externally. This slows down processing because of extra fetches. Elapsed 0.04

        SQL> truncate table plan_table;
        SQL> explain plan for
        2  select /*+ first_rows */ *
        3  from   ticket
        4  order by created_day, priority, ticket_id;
        SQL> set echo off

        Plan Table
        
----------------------------------------------------------------------------------------------------------------------
        | Operation                  |  Name              |  Rows | Bytes|  Cost  |  TQ  |IN-OUT| 
PQ Distrib | Pstart| Pstop |
        
----------------------------------------------------------------------------------------------------------------------
        | SELECT STATEMENT           |                    |   524K|   39M|    826 |      |      |   
         |       |       |
        |  TABLE ACCESS BY INDEX ROWI|TICKET              |   524K|   39M|    826 |      |      |   
         |       |       |
        |   INDEX FULL SCAN          |TICKET_B            |   524K|      |     26 |      |      |   
         |       |       |
        
----------------------------------------------------------------------------------------------------------------------
        SQL>
        SQL> set timing on
        SQL> declare
        2       cursor c is
        3       select /*+ first_rows */ *
        4       from   ticket
        5       order by created_day, priority, ticket_id;
        6  begin
        7       for r in c loop
        8         if r.a=6 then
        9            dbms_output.put_line( c%rowcount );
        10        end if;
        11        exit when r.a=6;
        12      end loop;
        13  end;
        14  /
        Elapsed: 00:00:00.04


The best approach so far is to add an index containing the filter columns and the order by columns This requires the order by to include the filter columns and can only be used for equality filters. Elapsed 0.01

        SQL> create index ticket_c on ticket( a, created_day, priority, ticket_id );
        SQL> truncate table plan_table;
        SQL> explain plan for
        2  select     *
        3  from   ticket
        4  where  a = 6
        5  order by a, created_day, priority, ticket_id;
        SQL> set echo off

        Plan Table
        
----------------------------------------------------------------------------------------------------------------------
        | Operation                  |  Name              |  Rows | Bytes|  Cost  |  TQ  |IN-OUT| 
PQ Distrib | Pstart| Pstop |
        
----------------------------------------------------------------------------------------------------------------------
        | SELECT STATEMENT           |                    |     5K|  395K|     10 |      |      |   
         |       |       |
        |  TABLE ACCESS BY INDEX ROWI|TICKET              |     5K|  395K|     10 |      |      |   
         |       |       |
        |   INDEX RANGE SCAN         |TICKET_C            |     5K|      |      2 |      |      |   
         |       |       |
        
----------------------------------------------------------------------------------------------------------------------
        SQL>
        SQL> set timing on
        SQL> declare
        2     cursor c is
        3     select  *
        4     from   ticket
        5     order by a, created_day, priority, ticket_id;
        6     r c%rowtype;
        7  begin
        8     open c;
        9     fetch c into r;
        10     close c;
        11  end;
        12  /
        Elapsed: 00:00:00.01


This approach is also not so sensitive to data distribution. Access paths with use an index on created_day etc to perform ordering will run quickly only if a row matching the selection criteria can be found in the first few hundred rows examined. This is shown by populating the table with a different query:

        insert into ticket
        ( a
        , b
        , created_day
        , priority
        , ticket_id
        )
        select mod(rownum, 100),
        b,
        created_day,
        mod(rownum, 10),
        rownum
        from   ticket
        /

Here there is a non-uniform data distribution because the values of priority are factors of a. This results in the queries
using an index on created_day being much, much slower e.g. elapsed time 3.46 seconds:

        SQL> create index ticket_b on ticket( created_day, priority, ticket_id );
        SQL>
        SQL> truncate table plan_table;
        SQL> explain plan for
        2  select *
        3  from   ticket
        4  where  a = 6
        5  order by created_day, priority, ticket_id;
        SQL> set echo off

        Plan Table
        
----------------------------------------------------------------------------------------------------------------------
        | Operation                  |  Name              |  Rows | Bytes|  Cost  |  TQ  |IN-OUT| 
PQ Distrib | Pstart| Pstop |
        
----------------------------------------------------------------------------------------------------------------------
        | SELECT STATEMENT           |                    |     5K|  399K|    826 |      |      |   
         |       |       |
        |  TABLE ACCESS BY INDEX ROWI|TICKET              |     5K|  399K|    826 |      |      |   
         |       |       |
        |   INDEX FULL SCAN          |TICKET_B            |     5K|      |     26 |      |      |   
         |       |       |
        
----------------------------------------------------------------------------------------------------------------------
        SQL>
        SQL> set timing on
        SQL> declare
        2     cursor c is
        3     select *
        4     from   ticket
        5     where  a = 6
        6     order by created_day, priority, ticket_id;
        7     r c%rowtype;
        8  begin
        9     open c;
        10     fetch c into r;
        11     close c;
        12  end;
        13  /
        Elapsed: 00:00:03.46

This doesn't affect the query with the criteria columns forming the leading edge of the index. This situation is also artificial and may be unlikely to occur in reality.

I hope this helps. Without knowing more about the data in your table and the other selection criteria, I cannot do any more Received on Sat Jul 21 2001 - 08:12:16 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US