Order by Slowing down

[fossil]

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,

[pando]

Have you analyzed the table? Optimizer is clever enough to bypass your order by clause if it's using an indexed column

[chrisrlong]

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.
SELECT --+ INDEX(T1,IndexB)
*
FROM
TABLE1 T1
ORDER BY
COL2If 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

[slimdave]

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 ...

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 ...

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

[slimdave]

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.

[fossil]

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,

[slimdave]

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)?

[DaPi]

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.

[DaPi]

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.

[tamilselvan]

For your query tuning sort_area_size parameters and temp tablespace initial and next extents' sizes will boost perf.

[fossil]

Thanks for reply.

I have

"workarea_size_policy=auto"
"pga_aggregate_target=800M"
"parallel_max_servers=0"

-- All the sorts are happening in the memory.

-- No parallel servers/Parallel Query as the tablespaces
are created on some big Hard Drives.

-- Size of the table 21G

-- 30 Million rows in the table

-- WITH ORDER BY on the SELECT, the result comes back
after 1 hour.

-- WITHOUT ORDER BY the result starts immediately.

-- I don't have NOT NULL constraint on custnum, I am sorry for that
,however, I checked the plan of the table in other database
where the CUSTNUM has NOT NULL constraint has B*TREE index
on it and NSL_SORT='BINARY' but still doing the sort.



explain plan set statement_id='8' into plan_table for
select custnum from customer_source_mv
where g_type_cat='K' and dnm='Y' and g_Type_caller='S' and ind_list='0110' order by custnum;


SELECT STATEMENT Cost = 394
SORT ORDER BY
TABLE ACCESS BY INDEX ROWID CUSTOMER_SOURCE_MV
BITMAP CONVERSION TO ROWIDS
BITMAP AND
BITMAP INDEX SINGLE VALUE CUST_SRC_G_CALLER
BITMAP INDEX SINGLE VALUE CUST_SRC_IND_LIST
BITMAP INDEX SINGLE VALUE CUSTSRC_G_CAT
BITMAP INDEX SINGLE VALUE CUSTSRC_DNM


Please put some light on this situtation.



Thanks,

[chrisrlong]

Now make sure to compare apples and apples. The ORDER BY must first SELECT *all* the rows before it can do the sort. So how long does it take to SELECT *all* the records without the ORDER BY - not just the first record, but all of them?

Also, exactly how many records are being returned that it take a freaking hour to select and sort them!??! Of what possible use would this many records be? I'm thinking you have more of a requirements problem than a technical one.

- Chris