Advanced Oracle SQL: Syntax Review Oracle Tips by Laurent Schneider

 

Laurent Schneider is considered one of the top Oracle SQL experts, and he is the author of the book "Advanced SQL Programming" by Rampant TechPress.  The following is an excerpt from the book.

DUAL

In Oracle, a special table called DUAL is always available. This table has just one row and one column of one character. Its purpose is to enable one to select expressions; whereas other RDBMS engines do not require a FROM clause in a query, Oracle does.

The following statements display the contents of the table as shown below:

SELECT
   42
FROM
   DUAL;
       42
----------
        42

-----------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     1 |     2   (0)| 00:00:01 |
|   1 | FAST DUAL       |      |      1 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------       

The optimizer knows that the table contains only one row and in the query above, the table will not be accessed.  In Oracle 10g, the FAST DUAL operation does not select any data from the table.

DISTINCT

The DISTINCT or UNIQUE keyword removes duplicate values, as shown below:

SELECT
DISTINCT
   JOB
FROM
   EMP;

JOB
---------
CLERK
SALESMAN
PRESIDENT
MANAGER
ANALYST

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     5 |    40 |     4  (25)| 00:00:01 |
|   1 |  HASH UNIQUE       |      |     5 |    40 |     4  (25)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP  |    14 |   112 |     3   (0)| 00:00:01 |

Note: It is a bad practice to abuse the DISTINCT keyword. If the column is supposed to  be unique, using DISTINCT may affect performance since an additional operation             is required.

Note: It is also wrong to assume DISTINCT sorts the rows. Especially in 10gR2 and later, DISTINCT often uses hashing instead of sorting to remove duplicates.

WHERE

Conditions are added in the WHERE clause to restrict the number of rows returned.  The following statements will only return rows in which the salary is greater than 2900:

SELECT
   ENAME,
   SAL
FROM
   EMP
WHERE
   SAL > 2900;

ENAME             SAL
---------- ----------
JONES            2975
SCOTT            3000
KING             5000
FORD             3000

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     9 |    90 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |     9 |    90 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

   1 - filter("SAL">2900)

The following script will return only the information for employee number 7782:

SELECT
   ENAME
FROM
   EMP
WHERE
   EMPNO=7782;

ENAME    
----------
CLARK

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     1 |    10 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    10 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | PK_EMP |     1 |       |     0   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

   2 - access("EMPNO"=7782)

EMPNO is the primary key of EMP: table access is done using the primary key index.

ORDER BY

The clause ORDER BY sorts the rows returned by the query.  The following script orders by salary amount:

SELECT
   ENAME,
   SAL
FROM
   EMP
ORDER BY
   SAL;

ENAME             SAL
---------- ----------
SMITH             800
JAMES             950
ADAMS            1100
WARD             1250
MARTIN           1250
MILLER           1300
TURNER           1500
ALLEN            1600
CLARK            2450
BLAKE            2850
JONES            2975
SCOTT            3000
FORD             3000
KING             5000

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    14 |   140 |     4  (25)| 00:00:01 |
|   1 |  SORT ORDER BY     |      |    14 |   140 |     4  (25)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP  |    14 |   140 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

The rows are returned starting with the lowest salary and increasing from there. That is, by default, rows are returned in ascending order. When no ORDER BY clause is used, the rows are returned in no specific order.

Appending the DESC keyword to a field specification causes the sort to be in descending order. Using the ASC keyword is an explicit way of achieving ascending order.

SELECT
   DEPTNO,
   ENAME
FROM
   EMP
ORDER BY
   1 ASC,
   SAL DESC;

DEPTNO ENAME
------ ------
    10 KING
    10 CLARK
    10 MILLER
    20 SCOTT
    20 FORD
    20 JONES
    20 ADAMS
    20 SMITH
    30 BLAKE
    30 ALLEN
    30 TURNER
    30 WARD
    30 MARTIN
    30 JAMES

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    14 |   182 |     4  (25)| 00:00:01 |
|   1 |  SORT ORDER BY     |      |    14 |   182 |     4  (25)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP  |    14 |   182 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

In the ORDER BY clause, it is possible to use column positions (1), column aliases or any other expression (SAL). The rows are returned ordered by the first expression, 1, DEPTNO, ascending. For duplicate values of DEPTNO, the rows are ordered by the salary, descending.  This is a nested sort.

In the example query shown above, it is possible to order by a column that is not selected. However, this is not always the case. For instance, when using DISTINCT, all sort columns must be selected.

CONNECT BY

Hierarchical queries are characterized by the CONNECT BY clause. Chapter 5 is dedicated to CONNECT BY queries.

GROUP BY

The GROUP BY clause aggregates rows. Aggregation is detailed in Chapter 3.

MODEL

The MODEL clause allows inter-rows calculation and row generation. Chapter 6 covers modeling.