Re: Performance when using ORDER BY

In article <3506C398.70C4_at_ictgroup.com>, jgitomer_at_ictgroup.com wrote:

Hello,
Some correction ir additive informations

>Hi Steve,
>
>Yes.
>
>Create indexes on the columns you are specifying in your order by
>clause. You can either create two single column indexes or a composite
 

>index naming both columns. If you choose to create a composite index
>make sure that the first column in the create index statement is the one
>you name first in the select statement. (Oracle will only use an index
>if the first column in the index is in a WHERE clause in the query.)

To be a lilttle more precise,
the first column used in the 'WHERE' clause must be the first colum in the index. this is not enough thet the column be referenced in the 'select'.

>there are no other indexes on the table you will be able to omit the
>Order By clause since Oracle will retrieve the data in index order.

in a general way,
Oracle will not use any index order UNLESS you specify an 'ORDER BY' clause or if you have some 'WHERE' condition like '>',.... the operator should be a kind of ordering condition to make that oracle use one of the indexes

This can be also different if you have sub queires, correlate queries, ...

but if you do the following :

        create table aa (i number);
        create index ii on aa (i);

        insert into aa values (1);
        insert into aa values (2);
        insert into aa values (3);
        insert into aa values (9);
        insert into aa values (8);
        insert into aa values (7);
        commit;

SQL> select * from aa;

        I

        1
        2
        3
        9
        8
        7

6 rows selected.

SQL> select * from aa where i >0;

        I

        1
        2
        3
        7
        8
        9

6 rows selected.

SQL> select * from aa where i <>0;

        I

        1
        2
        3
        9
        8
        7

6 rows selected.

>>
>> I'm new to Oracle (and to databases in general). I've created a
>> web based application which searches a database of 90,000 records
>> and displays the results. A basic query executes very quickly --
>> records begin displaying in approx 3 seconds; however, I want
>> to sort the output on two columns. When I add
>>
>> ORDER BY COLUMN1 ASC, COLUMN2 DESC
>>
>> the first results of a query appear 30 - 60 seconds after
>> the query is submitted.
>>
>> I understand that without the ORDER BY clause the results of
>> the query are being displayed while the query is still being
>> executed. My question is: is there a way to achieve the
>> output sort I want without the devastating performance loss?
>>

Nicolas

Nissakid_at_planete.net
Microsoft Certified Product Specialist
AUI Member

The above represents my own opinion not my employer's and does not necessarily constitute the official policy of my employer. Any opinion developed through this account represent a private conversation. Received on Thu Mar 12 1998 - 00:00:00 CST