Playing with ORDER BY clause in oracle.

Most of us must be aware of “ORDER BY” clause and its importance to present data in a specific order (Ascending or Descending).
Order by clause is an optional Component of SELECT Query.

Oracle Say
“Use the ORDER BY clause to order rows returned by the statement.
Without an ORDER BY clause, no guarantee exists that the same query executed more than once will retrieve rows in the same order.”

Lets have a look at the Order by Syntax

Syntax
ORDER BY { column-Name | ColumnPosition | Expression }
[ ASC | DESC ]
[ NULLS FIRST | NULLS LAST ]
[ , column-Name | ColumnPosition | Expression
[ ASC | DESC ]
[ NULLS FIRST | NULLS LAST ]
] *

As per syntax, we can also define ordering on Expression.

1. Define our own Custom Ordering :

Through expression, we can define our own custom order, i.e. influencing standard ascending or descending order.

Lets try to understand it better with field work.
We will work on EMP Table.

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
7839 KING PRESIDENT 17-11-81 5000 10
7698 BLAKE MANAGER 7839 01-05-81 2850 30
7782 CLARK MANAGER 7839 09-06-81 2450 10
7566 JONES MANAGER 7839 02-04-81 2975 20
7788 SCOTT ANALYST 7566 19-04-87 3000 20
7902 FORD ANALYST 7566 03-12-81 3000 20
7369 SMITH CLERK 7902 17-12-80 800 20
7499 ALLEN SALESMAN 7698 20-02-81 1600 300 30
7521 WARD SALESMAN 7698 22-02-81 1250 500 30
7654 MARTIN SALESMAN 7698 28-09-81 1250 1400 30
7844 TURNER SALESMAN 7698 08-09-81 1500 0 30
7876 ADAMS CLERK 7788 23-05-87 1100 20
7900 JAMES CLERK 7698 03-12-81 950 30
7934 MILLER CLERK 7782 23-01-82 1300 10

Let’s define our own custom order.
1. For EMP Earning highest salary should be displayed as the first row.
2. For EMP Earning lowest salary should be displayed as Last row.
3. Remaining EMP should be in default order, i.e. Ascending.

Using DECODE AND Scalar Query we can achieve same.

SQL>
SELECT Empno ,
Ename ,
Sal
FROM Emp
ORDER BY DECODE(sal,
(SELECT MAX(sal) FROM emp
), -1,
(SELECT MIN(sal) FROM emp
),
(SELECT MAX(sal) FROM emp
)+1,sal );

Let’s understand it,
1. Through Decode expression, we imposed virtual values to actual values.

MAX(sal) –> -1
MIN(sal) –> MIN(sal) + 1

2. As we have not defined any Order, hence default will be ASCENDING.
Through imposing Values as above, will help us to implement our custom order.

Though Order defined is “ASCENDING” but as we are replacing MAX(SAL) to -1, it will appear as first record.
Corresponding MIN(SAL) to MAX(SAL) + 1 will appear as Last records.

Output of SQL:

EMPNO ENAME SAL
---------- ---------- ----------
7839 KING 5000
7900 JAMES 950
7876 ADAMS 1100
7654 MARTIN 1250
7521 WARD 1250
7934 MILLER 1300
7844 TURNER 1500
7499 ALLEN 1600
7782 CLARK 2450
7698 BLAKE 2850
7566 JONES 2975
7788 SCOTT 3000
7902 FORD 3000
7369 SMITH 800

Explain plan :
EXPLAIN_PLAN

Through Explain plan, we can infer, Order by Expression as custom was evaluated for each and every record set returned.

2. Dynamic Ordering Columns.
Using Expression we can define, Dynamic Ordering conditions.
Lets check at below SQL.

SELECT EMPNO,
ENAME ,
SAL ,
DEPTNO
FROM Emp
ORDER BY DECODE
(
:Input,
1,TO_CHAR( Empno, '0000000000009'), -- IF Input:=1 THEN ORDER BY EMPNO
2,Ename, -- IF Input:=2 THEN ORDER BY ENAME
3,TO_CHAR( Sal, '0000000000009') , -- IF Input:=3 THEN ORDER BY SAL
4, TO_CHAR( Deptno, '0000000000009')-- IF Input:=4 THEN ORDER BY DEPNO
);

Using Decode and Input variable, we can define ordering column at runtime.
Please check we have formatted and converted all NUMBER column to CHAR.

Reason is, DECODE by default assume datatype of value return will be as First Return value.So if you return NUMBER First it will assume to only return NUMBER.

SELECT DECODE(DUMMY,'A',1,'X','Y') from DUAL;

Error :
ORA-01722: invalid number
01722. 00000 - "invalid number"

Return type of the first condition is NUMBER (DUMMY := ‘A’ THEN 1) hence it assume that the default return value for Decode will be NUMBER. Hence, for Second condition (DUMMY := ‘X’ THEN ‘Y’) it is giving error as return datatype is VARCHAR.

Reference:
Please check here for more information.

3. ORDER BY Constant :
It has no effect on ordering, it is ignored by the Optimizer.

Let’s check some example.
All below SQL are valid and won’t give any error.

SELECT * FROM EMP ORDER BY 'ABC';

SELECT * FROM EMP ORDER BY decode(1,1,'A',NULL);

SELECT * FROM EMP ORDER BY TO_DATE('01-JAN-2015','DD-MON-YYYY');

Plan for all SQL are Same.
No Order By Clause.
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
| 1 | TABLE ACCESS FULL| EMP | 14 | 546 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------

We can’t defined constant as NUMBER greater than number of columns projected in SELECT.
SELECT * FROM Emp ORDER BY 1223;

Error :
ORA-01785: ORDER BY item must be the number of a SELECT-list expression
01785. 00000 - "ORDER BY item must be the number of a SELECT-list expression"

Reason is simple.
A NUMBER in Order by referring to column position in the Select clause.
As per syntax, ORDER BY { column-Name | ColumnPosition | Expression }

We can also mention “ColumnPosition” for ordering on basis of projection at that particular position.

4. Order by for Hierarchy SQL.

Standard Order by Clause is a valid statement for Hierarchy SQL, but it make output meaningless in terms of hierarchy information.

For demonstration, we will work on same Emp table.
lets write standard hierarchy SQL.

SELECT Rtrim(Lpad(' ', Level*2)
|| Ename) AS Name,
Empno ,
Mgr ,
Sal
FROM Emp
START WITH Empno = 7839
CONNECT BY Mgr = Prior Empno;

hierarchy_output

Lets add order by clause to above Hierarchy SQL on basis of Employee name and check the output.

SELECT Rtrim(Lpad(' ', Level*2)
|| Ename) AS Name,
Empno ,
Mgr ,
Sal
FROM Emp
START WITH Empno = 7839
CONNECT BY Mgr = Prior Empno
ORDER BY ename;

Output :
hierarchy_output1

We can clearly see, Order by clause was applied but it had make hierarchy information meaningless.

Oracle provide an alternative ordering option, “ORDER SIBLINGS BY” clause for hierarchy sql.
It perform ordering with in hierarchy level, hence output is more meaningful and make sense for conveying hierarchy information as ordered.

SQL modified with ORDER SIBLINGS BY,

SELECT Rtrim(Lpad(' ', Level*2)
|| Ename) AS Name,
Empno ,
Mgr ,
Sal
FROM Emp
START WITH Empno = 7839
Connect By Mgr = Prior Empno
ORDER SIBLINGS BY ename;

order_siblings_by

Hope information share was useful, any feedback most welcome.

About Deepak Mahto

Having 8+ Years of relevant/exciting/fruitful/challenging/learning in Oracle Technology. Currently working as Oracle Performance Consultant in an MNC. Hold Expertise in Performance Engineering, SQL Tuning, Database Tuning, SQL, PLSQL Development/Design Considerations and Oracle Internals. I enjoy reading on Oracle internals, posting some of my finding or observations and playing football.
This entry was posted in Oracle Internal and tagged , , , , , , , . Bookmark the permalink.

One Response to Playing with ORDER BY clause in oracle.

  1. Anonymous says:

    Very Nicely explained Deepak 🙂

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s