Advanced Oracle SQL: Table Expressions 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.

1.      Table expression

Table expressions could be a view, a table, a collection or a remote table:

SELECT
   *
FROM
   EMP; 

EMP is a table: 

SELECT
   *
FROM
   TABLE
   (
      SYS.ODCINUMBERLIST(1,2,3)
   );

COLUMN_VALUE
------------
           1
           2
           3
-------------------------------------------------------------------------------------------
| Id  | Operation                             | Name | Rows  | Bytes | Cost (%CPU)|  Time     |
-------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT                      |      |  8168 | 16336 |    29   (0)| 00:00:01 |
|   1 |  COLLECTION ITERATOR CONSTRUCTOR FETCH|      |       |       |           
|          |
-------------------------------------------------------------------------------------------

SYS.ODCINUMBERLIST is a varying array (or varray) of NUMBER. ODCIDATELIST (10g) is an array of DATE, ODCIRAWLIST (10g) is an array of RAW(2000) and ODCIVARCHAR2LIST (10g) is an array of VARCHAR2(4000).

Note the inaccurate row count!

TABLE is a function that transforms the collection in a table. It can be used with NESTED TABLES or VARRAY columns, with type constructs, and with functions returning a collection:

SELECT
   COUNT(*)
FROM
   EMP@DB02;
  COUNT(*)
----------
        14

----------------------------------------------------------------------------------
| Id  | Operation              | Name   | Rows  | Cost (%CPU)| Time     | Inst   |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT REMOTE|        |     1 |     1   (0)| 00:00:01 |        |
|   1 |  SORT AGGREGATE        |        |     1 |            |          |        |
|   2 |   INDEX FULL SCAN      | PK_EMP |    14 |     1   (0)| 00:00:01 |  LSC01 |
----------------------------------------------------------------------------------

DB02 is a database link. EMP@DB02 is a remote table. Note the Inst column and the SELECT STATEMENT REMOTE operation:

SELECT
   ENAME
FROM
   EMP_PART
PARTITION
(
   EMP_P10
);

ENAME
----------
CLARK
KING
MILLER

-------------------------------------------------------------------------------------------
| Id  | Operation             | Name     | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |          |     3 |   108 |     3   (0)| 00:00:01 |       |       |
|   1 |  PARTITION LIST SINGLE|          |     3 |   108 |     3   (0)| 00:00:01 |     1
|     1 |
|   2 |   TABLE ACCESS FULL   | EMP_PART |     3 |   108 |     3   (0)| 00:00:01 |     1
|     1 |
-------------------------------------------------------------------------------------------

EMP_P10 is a partition of the EMP_PART table.

Summary

The SELECT syntax contains the expressions to be selected, the tables and subquery to select from, the conditions where rows are returned, the group by clause that aggregate rows, the connect by clause to use hierarchy and the model clause. The join can be written in Oracle or in ANSI syntax and the outer join can select columns of one table even when there is no matching row. The commonly used set operators are UNION, UNION ALL and MINUS.