| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Sort by indexed field in ORDER BY. Why so slow? (Oracle8)
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
[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
 |
 |