Oracle ORDER BY
Version 21c

General Information
Library Note Morgan's Library Page Header
Which has the higher priority in your organization: Deploying a new database or securing the ones you already have? Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.
Be sure to view the full listing of monographs in Morgan's Library
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@pdbdev

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@pdbdev

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@pdbdev

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
What's New In 19c
What's New In 20c-21c

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-2019 Daniel A. Morgan All Rights Reserved
  DBSecWorx