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

Home -> Community -> Usenet -> c.d.o.misc -> Re: Sort by indexed field in ORDER BY. Why so slow? (Oracle8)

Re: Sort by indexed field in ORDER BY. Why so slow? (Oracle8)

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Fri, 09 Apr 1999 14:55:19 GMT
Message-ID: <370f1137.12680093@192.86.155.100>


A copy of this was sent to "Christopher Latta" <clatta_at_ozemail.com.auX> (if that email address didn't require changing) On Fri, 9 Apr 1999 13:54:44 +1000, you wrote:

>In Delphi 4 C/S, I have a simple form with a TQuery, a TDataSource, and a
>TDbGrid. The SQL reads like:
>
>Select * from MyTable order by MyIndexedField
>
>However, when the form comes up, the hard disk on the server goes wild, and
>it takes around 20 seconds to return a result set to the DBGrid. This is on
>a table of around 10,000 rows. It appears to be resorting the table before
>sending data back. This time delay is unacceptable..
>
>In my Oracle book, it states that indices are used in WHERE clauses, and not
>in ORDER BY which seems utterly bizarre to me. Why else would you use an
>ORDER BY on an indexed field if not to return a result set quickly without
>having to resort an already sorted table?. Anyway, if I use the following
>SQL:
>
>Select * from MyTable where MyIndexedField > ' ' order by
>MyIndexedField
>
>the result set comes back instaneously. Can I get Oracle to use my index in
>an ORDER BY without having to use a dummy where clause?
>

I bet MyIndexedField is NULLABLE. NULLS are not indexed. By using the where clause, you precluded any null values for myIndexedField hence the index could be used. Consider this example:

SQL> desc emp

 Name                            Null?    Type
 ------------------------------- -------- ----
 EMPNO                           NOT NULL NUMBER(4)
 ENAME                                    VARCHAR2(10)
 JOB                                      VARCHAR2(9)
 MGR                                      NUMBER(4)
 HIREDATE                                 DATE
 SAL                                      NUMBER(7,2)
 COMM                                     NUMBER(7,2)
 DEPTNO                                   NUMBER(2)

SQL> @indexes emp

Index                          Is
Name                           Unique Indexed Columns
------------------------------ ------ -----------------------------------
EMP_IDX2                       No     SAL
SYS_C0038798                   Yes    EMPNO


SQL> set autotrace on explain        

SQL> select * from emp order by empno;

[data snipped]

Execution Plan


   0 SELECT STATEMENT Optimizer=CHOOSE    1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMP'    2 1 INDEX (FULL SCAN) OF 'SYS_C0038798' (UNIQUE) So, the index on empno is in fact used (without a where clause). OTOH:

SQL> select * from emp order by sal;

[data snipped]

Execution Plan


   0 SELECT STATEMENT Optimizer=CHOOSE    1 0 SORT (ORDER BY)
   2 1 TABLE ACCESS (FULL) OF 'EMP' the index on SAL cannot be since sal allows NULLS. Lets modify sal:

SQL> alter table emp modify sal not null; SQL> select * from emp order by sal;

[data snipped]

Execution Plan


   0 SELECT STATEMENT Optimizer=CHOOSE    1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMP'    2 1 INDEX (FULL SCAN) OF 'EMP_IDX2' (NON-UNIQUE) and the index can in fact be used once again.

>I don't really want to have to use an index hint for this, I just want
>Oracle to return the results quickly from my index.
>
>Thanks,
>Christopher Latta
>
>
>
>
>
 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
http://govt.us.oracle.com/ -- downloadable utilities  



Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Fri Apr 09 1999 - 09:55:19 CDT

Original text of this message

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