Avoid Sorting with “ORDER BY”
Sorting is mostly expensive, if you
have an index, you can use it to avoid sorting. Is NOT NULL constraint required?
The following TEST CASES will answer
your question.
TEST CASE 1:-
Step1: Create a
table TESTEMP as
SQL> CREATE TABLE
TESTEMP AS SELECT * FROM SCOTT.EMP;
Table created.
Step2: Query the
TESTEMP
SQL> set autotrace
on explain;
SQL> SELECT * FROM SCOTT.TESTEMP ORDER BY EMPNO;
EMPNO ENAME JOB MGR HIREDATE SAL
---------- ---------- --------- ----------
--------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800
7499 ALLEN SALESMAN 7698 20-FEB-81 1600
7521 WARD SALESMAN 7698 22-FEB-81 1250
7566 JONES MANAGER 7839 02-APR-81 2975
……. <Partial Output….>
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT
(ORDER BY)
2 1
TABLE ACCESS (FULL) OF 'TESTEMP'
Observation: Full Table Scan of TESTEMP and SORTING IS
DOING WITH EMPNO
Step3: Create a
index TESTEMPIDX on EMPNO OF TESTEMP table as
SQL> CREATE INDEX TESTEMPIDX ON
TESTEMP(EMPNO);
Index created.
SQL> SELECT * FROM SCOTT.TESTEMP ORDER BY EMPNO;
EMPNO ENAME JOB MGR HIREDATE SAL
---------- ---------- --------- ----------
--------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800
7499 ALLEN SALESMAN 7698 20-FEB-81 1600
7521 WARD SALESMAN 7698 22-FEB-81 1250
7566 JONES MANAGER 7839 02-APR-81 2975
……. <Partial Output….>
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0
SORT (ORDER BY)
2 1
TABLE ACCESS (FULL) OF 'TESTEMP'
Observation: Full Table Scan of TESTEMP and SORTING (ORDER
BY)
Step4: Create a NOT
NULL Constraint on EMPNO of TESTEMP table as
SQL> ALTER TABLE SCOTT.TESTEMP MODIFY
(EMPNO CONSTRAINT NOT_NULL_CONS NOT NULL);
Table altered.
SQL> SELECT * FROM SCOTT.TESTEMP ORDER BY EMPNO;
EMPNO ENAME JOB MGR HIREDATE SAL
---------- ---------- --------- ----------
--------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800
7499 ALLEN SALESMAN 7698 20-FEB-81 1600
7521 WARD SALESMAN 7698 22-FEB-81 1250
7566 JONES MANAGER 7839 02-APR-81 2975
……. <Partial Output….>
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0
TABLE ACCESS (BY INDEX ROWID) OF 'TESTEMP'
2 1
INDEX (FULL SCAN) OF
'TESTEMPIDX' (NON-UNIQUE)
Observation: INDEX TESTEMPIDX of TESTEMP is USING, TABLE
ACCESS BY INDEX ROWID AND NOSORTING
Conclusion:
1)
Order by
column should have NOT NULL constraint.
2) Index column should be in the Order by
Clause
TEST
CASE 2:-
Assume that table TESTEMP having not null constraint on
EMPNO and an index TESTEMPIDX as follows:-
SQL :> desc testemp
Name Null? Type
------------------------------- --------
-------------------
EMPNO NOT NULL NUMBER(4)
<Partial…>
SQL :> select index_name,column_name
from user_ind_columns where table_name='TESTEMP';
INDEX_NAME COLUMN_NAME
-------------------- --------------------
TESTEMPIDX EMPNO
Step1: QUERY the TESTEMP as
SQL> SELECT * FROM SCOTT.TESTEMP ORDER BY ENAME;
EMPNO ENAME JOB MGR HIREDATE SAL
---------- ---------- --------- ----------
--------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800
7499 ALLEN SALESMAN 7698 20-FEB-81 1600
7521 WARD SALESMAN 7698 22-FEB-81 1250
7566 JONES MANAGER 7839 02-APR-81 2975
……. <Partial Output….>
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0
SORT (ORDER BY)
2 1
TABLE ACCESS (FULL) OF 'TESTEMP'
Observation:
After adding ENAME in Order by clause, index TESTEMPIDX is not using
Step1: Create a Composite index on EMPNO and
ENAME and run a SQL again as
SQL> CREATE INDEX TESTEMPIDX2 ON TESTEMP(EMPNO,ENAME);
Index created.
SQL> SELECT * FROM SCOTT.TESTEMP ORDER BY EMPNO,ENAME;
EMPNO ENAME JOB MGR HIREDATE SAL
---------- ---------- --------- ----------
--------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800
7499 ALLEN SALESMAN 7698 20-FEB-81 1600
7521 WARD SALESMAN 7698 22-FEB-81 1250
7566 JONES MANAGER 7839 02-APR-81 2975
……. <Partial Output….>
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0
TABLE ACCESS (BY INDEX ROWID) OF 'TESTEMP'
2 1
INDEX (FULL SCAN) OF 'TESTEMPIDX2' (NON-UNIQUE)
Observation: INDEX TESTEMPIDX2 of TESTEMP is USING, TABLE
ACCESS BY INDEX ROWID AND NOSORTING
Step2:
Recreate the Composite index with reverse order of EMPNO and ENAME and run a
SQL again as
SQL> drop index testempidx2;
Index dropped.
SQL> CREATE INDEX
TESTEMPIDX2 ON TESTEMP(ENAME,EMPNO);
Index created.
SQL> SELECT * FROM SCOTT.TESTEMP ORDER BY EMPNO,ENAME;
EMPNO ENAME JOB MGR HIREDATE SAL
---------- ---------- --------- ----------
--------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800
7499 ALLEN SALESMAN 7698 20-FEB-81 1600
7521 WARD SALESMAN 7698 22-FEB-81 1250
7566 JONES MANAGER 7839 02-APR-81 2975
……. <Partial Output….>
Execution Plan
----------------------------------------------------------
0
SELECT STATEMENT Optimizer=CHOOSE
1
0 SORT (ORDER BY)
2
1 TABLE ACCESS (FULL) OF
'TESTEMP'
Observation: Full Table Scan of TESTEMP and SORTING (ORDER
BY)
Step3: Run
the SQL statement to match the column order in order by clause same as
composite index TESTEMPIDX2
SQL> SELECT * FROM SCOTT.TESTEMP ORDER BY
ENAME,EMPNO;
EMPNO ENAME JOB MGR HIREDATE SAL
---------- ---------- --------- ----------
--------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800
7499 ALLEN SALESMAN 7698 20-FEB-81 1600
7521 WARD SALESMAN 7698 22-FEB-81 1250
7566 JONES MANAGER 7839 02-APR-81 2975
……. <Partial Output….>
Execution Plan
----------------------------------------------------------
0
SELECT STATEMENT Optimizer=CHOOSE
1
0 TABLE ACCESS (BY INDEX ROWID) OF
'TESTEMP'
2
1 INDEX (FULL SCAN) OF
'TESTEMPIDX2' (NON-UNIQUE)
Conclusion:
1.
All the Order by column should be in composite index
2.
Order by columns should be of the same order as of composite index
3.
At least one of the composite columns should have NOT NULL constraint.
*** End of Tip
Author
Disclaimer:
The author does not
guarantee that this information is error-free.
If any errors are found, please report them to
author at SamOracle@Yahoo.com
© Oracle Techniques