Oracle ORDER BY
Version 12.1.0.1

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 11.2.0.3 to 12.1.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.
Data Dictionary Objects
PLAN_TABLE$ V$SQLAREA V$SQLTEXT_WITH_NEWLINES
V$SQL V$SQLTEXT V$SQL_PLAN
Object Privileges Privileges to tables and views granted through roles may not be valid within a PL/SQL object. See the section on AUTHID.

GRANT select ON [owner.]<object_name> TO <user_name>;
conn scott/tiger@pdborcl

GRANT select ON emp TO uwclass;
System Privileges
SELECT ANY TABLE    
 
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;

Related Topics
Analytic Functions
Explain Plan
Select Statements
String Functions - NLSSORT
Tuning

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved