-
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
-
Have you analyzed the table? Optimizer is clever enough to bypass your order by clause if it's using an indexed column
-
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
-
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
-
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.
-
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
-
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)?
-
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
-
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
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|