TIP :- How to avoid order by clause by using indexes ?

By using HINTS in select clause with ascending or descending indexes , we can avoid order by clause.

Let us clear this with the following example :-

Example :-

Ascending index

ora816 SamSQL :> create index x1 on bonus(ename);

Index created.

Descending Index

ora816 SamSQL :> create index x2 on bonus(ename desc);

Index created.

Check the existence of index in user_ind_columns

ora816 SamSQL :> select substr(table_name,1,30),substr(index_name,1,30),

substr(column_name,1,30) from user_ind_columns

where table_name='BONUS';

SUBSTR(TABLE_NAME,1,30) SUBSTR(INDEX_NAME,1,30) SUBSTR(COLUMN_NAME,1,30)

------------------------------ ------------------------------ ------------------------------

BONUS X1 ENAME

BONUS X2 SYS_NC00007$

Note : SYS_NC0007$ is a system generated column name in case of descending.

The following is the SQL statement which uses index hint to avoid order by clause

Syntax of hint is /*+ INDEX(<TABLE ALIAS> <INDEX NAME>) */

Retrieving the rows in ascending order.

ora816 SamSQL :> select /*+ INDEX(T1 X1) */ * from bonus T1 where ename > '0';

ENAME JOB SAL COMM

---------- --------- ---------- ----------

ameer dba

blogic

sameer dba

Retrieving rows in descending order

ora816 SamSQL :> select /*+ INDEX(T1 X2) */ * from bonus T1 where ename > '0';

ENAME JOB SAL COMM

---------- --------- ---------- ----------

sameer dba

blogic

ameer dba

End of Tip	Welcome to send comments or feedback at Wadhwa_s@Hotmail.com
Sameer Wadhwa	Copyright © Oracle Techniques All Rights Reserved