Order by Slowing down
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Order by Slowing down

Hybrid View

  1. #1
    Join Date
    Dec 2002
    Posts
    36

    Order by Slowing down

    Hello All,

    I have a table with presorted data on cusynum. The queries with the
    full table scan do not have any problems, the output comes as already sorted by custnum. The queries using the indexes don't produce the sorted output so ORDER BY CUSTNUM is added to sort the output, which makes the performance horrible. The CUSTNUM column is CHAr(17) and there is a non unique B*TREE index on it.

    IS there a way to avoid the sorting ?.

    I would appreciate any help on this issue.



    Thanks,
    fossil

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    Have you analyzed the table? Optimizer is clever enough to bypass your order by clause if it's using an indexed column

  3. #3
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Several points here.

    - why do you have a column called CustNum that is a string and not a number?

    - There is no such thing as pre-sorted data. Granted, you may be *extremely* lucky in that your data simply happens to be physically ordered on the disk *at this moment*. There is never, ever any guarantee that such an order will be maintained across even a single INSERT or UPDATE. The concept of physically ordered data (short of an IOT), simply does not exist. If you want the data sorted, you *have* to use an ORDER BY.

    - You are using a WHERE clause restricts by a column in index A. The optimizer uses index A (Col1) to solve the query. It therefore reads through index A (in that index's order) and gets the records from your table, which are now not in the order you like, which happens to be the order in index B (Col2). Too bad. Either force the use of index B, which will be no help at all in eliminating records but will remove the necessity of sorting OR use index A to quickly eliminate records but then pay the price of the sort OR create index C (Col2,Col1), which will somewhat split the difference. But there is no real magic here.

    - As pando noted, if Oracle sees that it is already using (or can efficiently use) an index that matches the ORDER BY, it will realize that it will not need to sort.
    Code:
    SELECT --+ INDEX(T1,IndexB)
       *
    FROM
       TABLE1 T1
    ORDER BY
       COL2
    If Oracle actually uses the index to get to the table, it will skip the sorting step. If not, and it full scans the table, it will have to sort. It is intelligent enough to handle this.

    - Chris
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Originally posted by pando
    Have you analyzed the table? Optimizer is clever enough to bypass your order by clause if it's using an indexed column
    Weeeeeeelllllllll that's true if you are ordering by a numeric or date, but don't forget that the index order is ASCII for char and varchar2, wheras the linguistic sort order is not.

    For example ...
    Code:
    SQL> alter session set nls_sort = 'FRENCH';
    SQL> /
    SQL> select * from my_table order by my_col;
    
    M
    -
    A
    a
    B
    b
    E
    e
    An index is not going to give the correct order here.

    However ...
    Code:
    SQL> alter session set nls_sort = 'BINARY';
    SQL> /
    SQL> select * from my_table order by my_col;
    
    M
    -
    A
    B
    E
    a
    b
    e
    Here an index is going to help.

    realistically, this means that if you only have either upper or lower case characters in each of your order by char columns, then setting nls_sort to binary will allow the optimizer to use an index to satisfy the order by clause
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  5. #5
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    and also if the column is nullable, then the null entries may not be in the index - if the query has to include the nulls n the sort then the index may be disqualified on that basis also.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  6. #6
    Join Date
    Dec 2002
    Posts
    36
    Thank you all for the reply.

    Here is the query :

    SELECT custnum, media_cd, ext_price, lst_ord_ss, ltd_dol_ss,
    ______ daycare_ty, dec_sc_is, cont_lvl, pr_daychur, fac_type,
    ______ aft_sch_pr, ss_line, grade_rng, ss_subgrp, ss_group, list_pri,
    ______ site_upd_dt, sic_prim8, dec_sc_ss, group_no
    ___ FROM ssw.multi_mv
    ___ WHERE country_cd = :sys_b_0
    _____ AND mail_cont IN (:sys_b_1, :sys_b_2)
    _____ AND mail_site = :sys_b_3
    _____ AND dsf_index IN (:sys_b_4, :sys_b_5)
    _____ AND list_pri BETWEEN :sys_b_6 AND :sys_b_7
    ___ ORDER BY custnum


    CUSTNUM is not null, has non unique B*tree index on it. Other columns in the where clause
    have bitmap indexes.

    So, should the optimizer be using the index for the order by clause, provided nls_sort='BINARY' has been set ?.

    Is nls_sort should be set separately for client ?.

    Chrisrlong's point of presorted data has raised another question ?.

    Should the data be sorted in the table which is
    created by this :

    CREATE table A
    as select * from B order by custnum ;

    This table is not having any inserts or updates.

    I will appreciate any response.

    Thanks,
    fossil

  7. #7
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    NLS_SORT would be best set at the session level, I would guess.

    A few questions:

    How many rows in the table?

    How many rows are returned by the query?

    What is your sort_area_size set to?

    What kind of performance difference are you getting between order by and no order by for the complete return of the result set (not just first row)?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  8. #8
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Originally posted by chrisrlong
    - why do you have a column called CustNum that is a string and not a number?
    Actually it can be a very good idea! Couple of real-life cases:

    - Customer Number has a modulo 11 check-digit, so a valid "number" may have a T in it.

    - Following several mergers, I need to incorporate some old Account Numbers without the risk of clashing with an existing one - I am going to map them 1=>A 2=>B etc.

    Strictly speaking these aren't numbers - but that's what people call them.
    "The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman

  9. #9
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142

    Re: Order by Slowing down

    Originally posted by fossil
    The queries with the full table scan do not have any problems, the output comes as already sorted by custnum.
    As Chris says, there is no such thing as pre-sorted data. This "accident" with FTS will certainly break down if you try to speed things up with Parallel Query.
    "The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman

  10. #10
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    For your query tuning sort_area_size parameters and temp tablespace initial and next extents' sizes will boost perf.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width