Oracle ORDER BY

Oracle ORDER BY Version 11.2.0.3

General Information

notes

Data Dictionary Objects

PLAN_TABLE$	V$SQLAREA	V$SQLTEXT_WITH_NEWLINES
V$SQL	V$SQLTEXT	V$SQL_PLAN

By Column Name

Single Column Ascending

SELECT <column_name>
FROM <table_name>
ORDER BY <column_name>;

SELECT table_name
FROM all_tables;

SELECT table_name
FROM all_tables
ORDER BY table_name;

Single Column Descending

SELECT <column_name>
FROM <table_name>
ORDER BY <column_name> DESC;

SELECT table_name
FROM all_tables
ORDER BY table_name DESC;

Order By Multiple Columns

SELECT <column_name>, <column_name
FROM <table_name>
ORDER BY <column_name>, <column_name>';

SELECT table_name, tablespace_name
FROM all_tables
ORDER BY tablespace_name, table_name;

Order By Multiple Columns With Mixed Ascending And Descending Orders

SELECT <column_name>, column_name
FROM <table_name>
ORDER BY <column_name>, <column_name> DESC;

SELECT table_name, tablespace_name
FROM all_tables
ORDER BY tablespace_name DESC, table_name;

By Column Position

Single Column Ascending

SELECT <column_name>
FROM <table_name>
ORDER BY <position_number>;

SELECT table_name
FROM all_tables
ORDER BY 1;

Single Column Descending

SELECT <column_name>
FROM <table_name>
ORDER BY <position_number> DESC;

SELECT table_name, tablespace_name
FROM all_tables
ORDER BY 2 DESC;

Order By Multiple Columns

SELECT <column_name>
FROM <table_name>
ORDER BY <position_number>, <position_number>;

SELECT table_name, tablespace_name
FROM all_tables
ORDER BY 2, 1;

Order By Multiple Columns With Mixed Ascending And Descending Orders

SELECT <column_name>
FROM <table_name>
ORDER BY <position_number> ASC, <position_number> DESC;

SELECT table_name, tablespace_name
FROM all_tables
ORDER BY 1 ASC, 2 DESC;

Order Nulls

Nulls First

SELECT <column_name>
FROM <table_name>
ORDER BY <column_name_or_position_number> NULLS FIRST;

SELECT tablespace_name
FROM all_tables
ORDER BY tablespace_name NULLS FIRST;

Nulls Last

SELECT <column_name>
FROM <table_name>
ORDER BY <column_name_or_position_number> NULLS LAST;

SELECT table_name, tablespace_name
FROM all_tables
ORDER BY 2, 1 NULLS LAST;

The Cost of Ordering

ORDER BY Overhead

conn uwclass/uwclass@pdborcl

CREATE TABLE t AS
SELECT * FROM all_objects;

EXPLAIN PLAN FOR
SELECT * FROM t;

set linesize 121

SELECT * FROM TABLE(dbms_xplan.display);

EXPLAIN PLAN FOR
SELECT * FROM t
ORDER BY 1;

SELECT * FROM TABLE(dbms_xplan.display);

Non-Default Ordering

Ordering With A Decode

spool c:\temp\recompile.sql

SELECT 'ALTER ' || object_type || ' '|| object_name ||' COMPILE;'
FROM user_objects
WHERE object_type IN ('FUNCTION', 'PACKAGE', 'PROCEDURE', 'TRIGGER', 'VIEW')
ORDER BY DECODE(object_type, 'VIEW','A', 'FUNCTION','B', 'PROCEDURE', 'C', 'PACKAGE','D', 'Z');

spool off

ORDER BY Demos

Ordering Takes Place After Selection

conn uwclass/uwclass@pdborcl

CREATE TABLE ob_demo (
datecol DATE,
numbcol NUMBER(3));

BEGIN
FOR i IN 1 .. 10
LOOP
    INSERT INTO ob_demo
    (datecol, numbcol)
    VALUES
    (TRUNC(SYSDATE + i), MOD(i*11,13));
END LOOP;
COMMIT;
END;
/

SELECT * FROM ob_demo;

-- invalid
SELECT datecol, numbcol
FROM ob_demo
WHERE ROWNUM = 1
ORDER BY datecol;

-- this does not work because rownum is evaluated before the order by

-- valid
SELECT datecol, numbcol
FROM (
SELECT row_number() OVER (ORDER BY datecol DESC) r,
datecol, numbcol
FROM ob_demo)
WHERE r=1;

/* the inner select orders datecol descending and assigns a a value to 'r'. This value starts with 1 and is incremented by 1 for each row. The outer select then selects the row where r = 1 */

SELECT row_number() OVER (ORDER BY datecol DESC) r, datecol, numbcol
FROM ob_demo;