Oracle Explain Plan
Version 18.3.0.1

General Information
In many of the Explain Plan reports included below non-critical columns such as "Time" have been removed to fit the website format.
Data Dictionary Objects
PLAN_TABLE$ V$SQL_PLAN_STATISTICS V$SQL_PLAN_STATISTICS_ALL
V$SQL_PLAN    
 
Preparation
Create the plan table if it does not already exist SQL> conn sys as sysdba

SQL> @?\rdbms\admin\catplan.sql
-- the table is created by default at the time of installation ("create database") and should be owned by SYS in CDB$ROOT
Create test data if not already done Run the script servers.sql downloaded by [Clicking Here] into the directory c:\test or an equivalent and change the name below, if necessary, to match your choice.
SQL> @c:\test\servers.sql
Gather statistics for the CBO conn uwclass/uwclass@pdbdev

exec dbms_stats.gather_schema_stats(USER, cascade=>TRUE);
 
Syntax
Explain Plan Syntax EXPLAIN PLAN
[SET statement_id = <statement_identifier>]
[INTO <schma_name.table_name>[@db_link]]
FOR <SQL statement here>;
 
Report Syntax
Explain Plan Analysis Using DBMS_XPLAN

Explain Plans can also be generated using AUTOTRACE and other pipelined table functions in the DBMS_XPLAN package. Checks the links at page bottom.
dbms_xplan.display(
table_name   IN VARCHAR2 DEFAULT 'PLAN_TABLE',
statement_id IN VARCHAR2 DEFAULT NULL,
format       IN VARCHAR2 DEFAULT 'TYPICAL');


Format choices are:
BASIC ..... displays minimum information
TYPICAL ... displays most relevant information
SERIAL .... like TYPICAL but without parallel information
ALL ....... displays all information

Follow the link to dbms_stats.gather_system_statistics for information on CPU costing.
EXPLAIN PLAN
SET STATEMENT_ID = 'abc'
FOR
SELECT srvr_id
FROM servers
INTERSECT
SELECT srvr_id
FROM serv_inst;

set pagesize 25
set linesize 121

-- to display the last plan explained
SELECT * FROM TABLE(dbms_xplan.display);

-- to display a specific plan by name
SELECT * FROM TABLE(dbms_xplan.display('PLAN_TABLE','abc','ALL'));
Using A View To Display The DBMS_XPLAN Output CREATE OR REPLACE VIEW xpan AS
SELECT * FROM table(dbms_xplan.display);

SELECT * FROM plan_view;

GRANT select ON xplan TO public;
 
Test Statements
Test Statement # 1

INTERSECT
EXPLAIN PLAN FOR
SELECT srvr_id
FROM servers
INTERSECT
SELECT srvr_id
FROM serv_inst;

SELECT * FROM TABLE(dbms_xplan.display);
-- version 12.1.0.2
--------------------------------------------------------------------------
| Id | Operation              | Name         | Rows | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------
|  0 | SELECT STATEMENT       |              |  141 |  4560 |      4 (75)|
|  1 |  INTERSECTION          |              |      |       |            |
|  2 |   SORT UNIQUE NOSORT   |              |  141 |   564 |      1  (0)|
|  3 |    INDEX FULL SCAN     | PK_SERVERS   |  141 |   564 |      1  (0)|
|  4 |   SORT UNIQUE          |              |  999 |  3996 |      3  (0)|
|  5 |    INDEX FAST FULL SCAN| PK_SERV_INST |  999 |  3996 |      3  (0)|
--------------------------------------------------------------------------
-- version 18.3.0.1
--------------------------------------------------------------------------
| Id | Operation              | Name         | Rows | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------
|  0 | SELECT STATEMENT       |               | 141 |  4560 |      6 (34)|
|  1 |  INTERSECTION          |               |     |       |            |
|  2 |   SORT UNIQUE NOSORT   |               | 141 |   564 |      2 (50)|
|  3 |    INDEX FULL SCAN     | PK_SERVERS    | 141 |   564 |      1  (0)|
|  4 |   SORT UNIQUE          |               | 999 |  3996 |      4 (25)|
|  5 |    INDEX FAST FULL SCAN| PK_SERV_INST  | 999 |  3996 |      3  (0)|
--------------------------------------------------------------------------
Test Statement # 2

Simple IN
EXPLAIN PLAN FOR
SELECT srvr_id
FROM servers
WHERE srvr_id IN (
  SELECT srvr_id
  FROM serv_inst);

SELECT * FROM TABLE(dbms_xplan.display);
-- version 12.1.0.2
--------------------------------------------------------------------------
| Id | Operation              | Name         | Rows | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------
|  0 | SELECT STATEMENT       |              |   11 |    88 |      3  (0)|
|  1 |  NESTED LOOPS          |              |   11 |    88 |      3  (0)|
|  2 |   SORT UNIQUE          |              |  999 |  3996 |      3  (0)|
|  3 |    INDEX FAST FULL SCAN| PK_SERV_INST |  999 |  3996 |      3  (0)|
|* 4 |   INDEX UNIQUE SCAN    | PK_SERVERS   |    1 |     4 |      0  (0)|
--------------------------------------------------------------------------

-- version 18.3.0.1
--------------------------------------------------------------------------
| Id | Operation              | Name         | Rows | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------
|  0 | SELECT STATEMENT       |              |   11 |    88 |      4 (25)|
|  1 |  NESTED LOOPS          |              |   11 |    88 |      4 (25)|
|  2 |   SORT UNIQUE          |              |  999 |  3996 |      3  (0)|
|  3 |    INDEX FAST FULL SCAN| PK_SERV_INST |  999 |  3996 |      3  (0)|
|* 4 |   INDEX UNIQUE SCAN    | PK_SERVERS   |    1 |     4 |      0  (0)|
--------------------------------------------------------------------------
Test Statement # 3

IN with INNER JOIN
EXPLAIN PLAN FOR
SELECT srvr_id
FROM servers
WHERE srvr_id IN (
  SELECT i.srvr_id
  FROM serv_inst i, servers s
  WHERE i.srvr_id = s.srvr_id);

SELECT * FROM TABLE(dbms_xplan.display);
-- version 12.1.0.2
----------------------------------------------------------------------------
| Id | Operation                | Name         | Rows | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------
|  0 | SELECT STATEMENT         |              |   11 |   187 |      3  (0)|
|  1 |  NESTED LOOPS            |              |   11 |   187 |      3  (0)|
|  2 |   VIEW                   | VW_NSO_1     |  999 | 12987 |      3  (0)|
|  3 |    HASH UNIQUE           |              |   11 |  7992 |            |
|  4 |     NESTED LOOPS SEMI    |              |  999 |  7992 |      3  (0)|
|  5 |      INDEX FAST FULL SCAN| PK_SERV_INST |  999 |  3996 |      3  (0)|
|* 6 |      INDEX UNIQUE SCAN   | PK_SERVERS   |  141 |   564 |      0  (0)|
|* 7 |   INDEX UNIQUE SCAN      | PK_SERVERS   |    1 |     4 |      0  (0)|
----------------------------------------------------------------------------

-- version 18.3.0.1
----------------------------------------------------------------------------
| Id | Operation                | Name         | Rows | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------
|  0 | SELECT STATEMENT         |              |   11 |   187 |      4 (25)|
|  1 |  NESTED LOOPS            |              |   11 |   187 |      4 (25)|
|  2 |   VIEW                   | VW_NSO_1     |  983 | 12779 |      3  (0)|
|  3 |    HASH UNIQUE           |              |   11 |  7864 |            |
|  4 |     NESTED LOOPS SEMI    |              |  983 |  7864 |      3  (0)|
|  5 |      INDEX FAST FULL SCAN| PK_SERV_INST |  999 |  3996 |      3  (0)|
|* 6 |       INDEX UNIQUE SCAN  | PK_SERVERS   |  141 |   564 |      0  (0)|
|* 7 |    INDEX UNIQUE SCAN     | PK_SERVERS   |    1 |     4 |      0  (0)|
----------------------------------------------------------------------------
Test Statement # 4A

Simple INNER JOIN
EXPLAIN PLAN FOR
SELECT DISTINCT s.srvr_id
FROM servers s, serv_inst i
WHERE s.srvr_id = i.srvr_id;

SELECT * FROM TABLE(dbms_xplan.display);
-- version 12.1.0.2
-------------------------------------------------------------------------
| Id | Operation              | Name         | Rows | Bytes | Cost(%CPU)|
-------------------------------------------------------------------------
|  0 | SELECT STATEMENT       |              |   11 |    88 |     3  (0)|
|  1 |  HASH UNIQUE           |              |   11 |    88 |     3  (0)|
|  2 |   NESTED LOOPS SEMI    |              |  999 |  7992 |     3  (0)|
|  3 |    INDEX FAST FULL SCAN| PK_SERV_INST |  999 |  3996 |     3  (0)|
|* 4 |    INDEX UNIQUE SCAN   | PK_SERVERS   |  141 |   564 |     0  (0)|
-------------------------------------------------------------------------

-- version 18.3.0.1
-------------------------------------------------------------------------
| Id | Operation              | Name         | Rows | Bytes | Cost(%CPU)|
-------------------------------------------------------------------------
|  0 | SELECT STATEMENT       |              |   11 |    88 |     4 (25)|
|  1 |  HASH UNIQUE           |              |   11 |    88 |     4 (25)|
|  2 |   NESTED LOOPS SEMI    |              |  983 |  7864 |     3  (0)|
|  3 |    INDEX FAST FULL SCAN| PK_SERV_INST |  999 |  3996 |     3  (0)|
|* 4 |    INDEX UNIQUE SCAN   | PK_SERVERS   |  141 |   564 |     0  (0)|
-------------------------------------------------------------------------
Test Statement # 4B

Simple INNER JOIN with HINT
EXPLAIN PLAN FOR
SELECT /*+ USE_HASH(s,i) */ DISTINCT s.srvr_id
FROM servers s, serv_inst i
WHERE s.srvr_id = i.srvr_id;

SELECT * FROM TABLE(dbms_xplan.display);
-- version 12.1.0.2
--------------------------------------------------------------------------
| Id | Operation              | Name         | Rows | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------
|  0 | SELECT STATEMENT       |              |    11 |    88 |     4  (0)|
|  1 |  HASH UNIQUE           |              |    11 |    88 |     4  (0)|
|* 2 |   HASH JOIN SEMI       |              |    11 |    88 |     4  (0)|
|  3 |    INDEX FULL SCAN     | PK_SERVERS   |   141 |   564 |     1  (0)|
|  4 |    INDEX FAST FULL SCAN| PK_SERV_INST |   999 |  3996 |     3  (0)|
--------------------------------------------------------------------------

-- version 18.3.0.1
--------------------------------------------------------------------------
| Id | Operation              | Name         | Rows | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------
|  0 | SELECT STATEMENT       |              |   11 |    88 |      5 (20)|
|  1 |  HASH UNIQUE           |              |   11 |    88 |      5 (20)|
|* 2 |   HASH JOIN SEMI       |              |   11 |    88 |      4  (0)|
|  3 |    INDEX FULL SCAN     | PK_SERVERS   |  141 |   564 |      1  (0)|
|  4 |    INDEX FAST FULL SCAN| PK_SERV_INST |  999 |  3996 |      3  (0)|
--------------------------------------------------------------------------
Test Statement # 4C

Simple INNER JOIN with HINT
EXPLAIN PLAN FOR
SELECT /*+ USE_MERGE(s,i) */ DISTINCT s.srvr_id
FROM servers s, serv_inst i
WHERE s.srvr_id = i.srvr_id;

SELECT * FROM TABLE(dbms_xplan.display);
-- version 12.1.0.2
--------------------------------------------------------------------------
| Id | Operation               | Name         | Rows | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------
|  0 | SELECT STATEMENT        |              |   11 |    88 |      4  (0)|
|  1 |  SORT UNIQUE NOSORT     |              |   11 |    88 |      4  (0)|
|  2 |   MERGE JOIN SEMI       |              |   11 |    88 |      4  (0)|
|  3 |    INDEX FULL SCAN      | PK_SERVERS   |  141 |   564 |      1  (0)|
|* 4 |    SORT UNIQUE          |              |  999 |  3996 |      3  (0)|
|  5 |     INDEX FAST FULL SCAN| PK_SERV_INST |  999 |  3996 |      3  (0)|
--------------------------------------------------------------------------

-- version 18.3.0.1
--------------------------------------------------------------------------
| Id | Operation               | Name         | Rows | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------
|  0 | SELECT STATEMENT        |              |   11 |    88 |      6 (34)|
|  1 |  SORT UNIQUE NOSORT     |              |   11 |    88 |      6 (34)|
|  2 |   MERGE JOIN SEMI       |              |   11 |    88 |      5 (20)|
|  3 |    INDEX FULL SCAN      | PK_SERVERS   |  141 |   564 |      1  (0)|
|* 4 |    SORT UNIQUE          |              |  999 |  3996 |      4 (25)|
|  5 |     INDEX FAST FULL SCAN| PK_SERV_INST |  999 |  3996 |      3  (0)|
---------------------------------------------------------------------------
Test Statement # 5

NOT IN with MINUS
EXPLAIN PLAN FOR
SELECT DISTINCT srvr_id
FROM servers
WHERE srvr_id NOT IN (
  SELECT srvr_id
  FROM servers
  MINUS
  SELECT srvr_id
  FROM serv_inst);

SELECT * FROM TABLE(dbms_xplan.display);
-- version 12.1.0.2
--------------------------------------------------------------------------
| Id | Operation                  | Name       | Rows | Bytes |Cost(%CPU)|
--------------------------------------------------------------------------
|  0 | SELECT STATEMENT         |              |  140 |  2380 |    5  (0)|
|* 1 |  HASH JOIN ANTI          |              |  140 |  2380 |    5  (0)|
|  2 |   INDEX FULL SCAN        | PK_SERVERS   |  141 |   564 |    1  (0)|
|  3 |    VIEW                  | VW_NSO_1     |  141 |  1833 |    4  (0)|
|  4 |    MINUS                 |              |      |       |          |
|  5 |     SORT UNIQUE          |              |  141 |   564 |          |
|  6 |      INDEX FULL SCAN     | PK_SERVERS   |  141 |   564 |    1  (0)|
|  7 |     SORT UNIQUE          |              |  999 |  3996 |          |
|  8 |      INDEX FAST FULL SCAN| PK_SERV_INST |  999 |  3996 |    3  (0)|
--------------------------------------------------------------------------

-- version 18.3.0.1
--------------------------------------------------------------------------
| Id | Operation                 | Name        | Rows | Bytes |Cost(%CPU)|
--------------------------------------------------------------------------
|  0 | SELECT STATEMENT          |             |  140 |  2380 |    7 (29)|
|* 1 |  HASH JOIN ANTI           |             |  140 |  2380 |    7 (29)|
|  2 |   INDEX FULL SCAN         | PK_SERVERS  |  141 |   564 |    1  (0)|
|  3 |    VIEW                   | VW_NSO_1    |  141 |  1833 |    6 (34)|
|  4 |    MINUS                  |             |      |       |          |
|  5 |     SORT UNIQUE           |             |  141 |   564 |          |
|  6 |      INDEX FULL SCAN      | PK_SERVERS  |  141 |   564 |    1  (0)|
|  7 |     SORT UNIQUE           |             |  999 |  3996 |          |
|  8 |      INDEX FAST FULL SCAN | PK_SERV_INST|  999 |  3996 |    3  (0)|
--------------------------------------------------------------------------
Test Statement # 6

EXISTS
EXPLAIN PLAN FOR
SELECT srvr_id
FROM servers s
WHERE EXISTS (
  SELECT srvr_id
  FROM serv_inst i
  WHERE s.srvr_id = i.srvr_id);

SELECT * FROM TABLE(dbms_xplan.display);
-- version 12.1.0.2
--------------------------------------------------------------------------
| Id | Operation              | Name         | Rows | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------
|  0 | SELECT STATEMENT       |              |   11 |    88 |      3  (0)|
|  1 |  NESTED LOOPS          |              |   11 |    88 |      3  (0)|
|  2 |   SORT UNIQUE          |              |  999 |  3996 |      3  (0)|
|  3 |    INDEX FAST FULL SCAN| PK_SERV_INST |  999 |  3996 |      3  (0)|
|* 4 |   INDEX UNIQUE SCAN    | PK_SERVERS   |    1 |     4 |      0  (0)|
--------------------------------------------------------------------------

-- version 18.3.0.1
--------------------------------------------------------------------------
| Id | Operation              | Name         | Rows | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------
|  0 | SELECT STATEMENT       |              |   11 |    88 |      4 (25)|
|  1 |  NESTED LOOPS          |              |   11 |    88 |      4 (25)|
|  2 |   SORT UNIQUE          |              |  999 |  3996 |      3  (0)|
|  3 |    INDEX FAST FULL SCAN| PK_SERV_INST |  999 |  3996 |      3  (0)|
|* 4 |   INDEX UNIQUE SCAN    | PK_SERVERS      | 1 |     4 |      0  (0)|
----------------------------------------------------------------------------
Test Statement # 7

Common Table Expression / WITH Clause
EXPLAIN PLAN FOR
WITH q AS (
  SELECT DISTINCT s.srvr_id
  FROM servers s, serv_inst i
  WHERE s.srvr_id = i.srvr_id)
SELECT * FROM q;

SELECT * FROM TABLE(dbms_xplan.display);
--version 12.1.0.2
--------------------------------------------------------------------------
| Id | Operation               | Name         | Rows | Bytes | Cost(%CPU)|
--------------------------------------------------------------------------
|  0 | SELECT STATEMENT        |              |   11 |   143 |     3  (0)|
|  1 |  VIEW                   |              |   11 |   143 |     3  (0)|
|  2 |   HASH UNIQUE           |              |   11 |    88 |     3  (0)|
|  3 |    NESTED LOOPS SEMI    |              |  999 |  7992 |     3  (0)|
|  4 |     INDEX FAST FULL SCAN| PK_SERV_INST |  999 |  3996 |     3  (0)|
|* 5 |     INDEX UNIQUE SCAN   | PK_SERVERS   |  141 |   564 |     0  (0)|
--------------------------------------------------------------------------

-- version 18.3.0.1
--------------------------------------------------------------------------
| Id | Operation               | Name         | Rows | Bytes | Cost(%CPU)|
--------------------------------------------------------------------------
|  0 | SELECT STATEMENT        |              |   11 |   143 |     4 (25)|
|  1 |  VIEW                   |              |   11 |   143 |     4 (25)|
|  2 |   HASH UNIQUE           |              |   11 |    88 |     4 (25)|
|  3 |    NESTED LOOPS SEMI    |              |  983 |  7864 |     3  (0)|
|  4 |     INDEX FAST FULL SCAN| PK_SERV_INST |  999 |  3996 |     3  (0)|
|* 5 |     INDEX UNIQUE SCAN   | PK_SERVERS   |  141 |   564 |     0  (0)|
--------------------------------------------------------------------------
Test Statement # 8

OUTER JOIN
EXPLAIN PLAN FOR
SELECT DISTINCT s.srvr_id
FROM servers s, serv_inst i
WHERE s.srvr_id(+) = i.srvr_id;

SELECT * FROM TABLE(dbms_xplan.display);
-- version 12.1.0.2
-------------------------------------------------------------------------
| Id | Operation              | Name         | Rows | Bytes | Cost(%CPU)|
-------------------------------------------------------------------------
|  0 | SELECT STATEMENT       |              |   11 |    88 |     3  (0)|
|  1 |  HASH UNIQUE           |              |   11 |    88 |     3  (0)|
|  2 |   NESTED LOOPS OUTER   |              |  999 |  7992 |     3  (0)|
|  3 |    INDEX FAST FULL SCAN| PK_SERV_INST |  999 |  3996 |     3  (0)|
|* 4 |    INDEX UNIQUE SCAN   | PK_SERVERS   |    1 |     4 |     0  (0)|
-------------------------------------------------------------------------

-- version 18.3.0.1
-------------------------------------------------------------------------
| Id | Operation              | Name         | Rows | Bytes | Cost(%CPU)|
-------------------------------------------------------------------------
|  0 | SELECT STATEMENT       |              |   11 |    88 |     4 (25)|
|  1 |  HASH UNIQUE           |              |   11 |    88 |     4 (25)|
|  2 |   NESTED LOOPS OUTER   |              |  999 |  7992 |     3  (0)|
|  3 |    INDEX FAST FULL SCAN| PK_SERV_INST |  999 |  3996 |     3  (0)|
|* 4 |    INDEX UNIQUE SCAN   | PK_SERVERS   |    1 |     4 |     0  (0)|
-------------------------------------------------------------------------
Test Statement # 9

UNION ALL
EXPLAIN PLAN FOR
SELECT srvr_id
FROM (
  SELECT srvr_id, SUM(cnt) SUMCNT
  FROM (
    SELECT DISTINCT srvr_id, 1 AS CNT
    FROM servers
    UNION ALL
    SELECT DISTINCT srvr_id, 1
    FROM serv_inst)
  GROUP BY srvr_id)
WHERE sumcnt = 2;

SELECT * FROM TABLE(dbms_xplan.display);
-- version 12.1.0.2
----------------------------------------------------------------------------
| Id | Operation                 | Name         | Rows | Bytes | Cost(%CPU)|
----------------------------------------------------------------------------
|  0 | SELECT STATEMENT          |              |    2 |    14 |     4  (0)|
|* 1 |  FILTER                   |              |      |       |           |
|  2 |   HASH GROUP BY           |              |    2 |    14 |     4  (0)|
|  3 |    VIEW                   |              |  152 |  1064 |     4  (0)|
|  4 |     UNION-ALL             |              |      |       |           |
|  5 |      INDEX FULL SCAN      | PK_SERVERS   |  141 |   564 |     1  (0)|
|  6 |      HASH UNIQUE          |              |   11 |    44 |     3  (0)|
|  7 |       INDEX FAST FULL SCAN| PK_SERV_INST |  999 |  3996 |     3  (0)|
----------------------------------------------------------------------------

-- version 18.3.0.1
----------------------------------------------------------------------------
| Id | Operation                 | Name         | Rows | Bytes | Cost(%CPU)|
----------------------------------------------------------------------------
|  0 | SELECT STATEMENT          |              |    2 |    14 |     6 (34)|
|* 1 |  FILTER                   |              |      |       |           |
|  2 |   HASH GROUP BY           |              |    2 |    14 |     6 (34)|
|  3 |    VIEW                   |              |  152 |  1064 |     5 (20)|
|  4 |     UNION-ALL             |              |      |       |           |
|  5 |      INDEX FULL SCAN      | PK_SERVERS   |  141 |   564 |     1  (0)|
|  6 |      HASH UNIQUE          |              |   11 |    44 |     4 (25)|
| 7  |       INDEX FAST FULL SCAN| PK_SERV_INST |  999 |  3996 |     3  (0)|
----------------------------------------------------------------------------
Test Statement # 10

Alter the WHERE clause
EXPLAIN PLAN FOR
SELECT DISTINCT s.srvr_id
FROM servers s, serv_inst i
WHERE s.srvr_id+0 = i.srvr_id+0;

SELECT * FROM TABLE(dbms_xplan.display);
-- version 12.1.0.2
--------------------------------------------------------------------------
| Id | Operation              | Name         | Rows | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------
|  0 | SELECT STATEMENT       |              |   11 |    88 |      4  (0)|
|  1 |  HASH UNIQUE           |              |   11 |    88 |      4  (0)|
|* 2 |   HASH JOIN SEMI       |              |   11 |    88 |      4  (0)|
|  3 |    INDEX FULL SCAN     | PK_SERVERS   |  141 |   564 |      1  (0)|
|  4 |    INDEX FAST FULL SCAN| PK_SERV_INST |  999 |  3996 |      3  (0)|
--------------------------------------------------------------------------

-- version 18.3.0.1
--------------------------------------------------------------------------
| Id | Operation              | Name         | Rows | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------
|  0 | SELECT STATEMENT       |              |   11 |    88 |      5 (20)|
|  1 |  HASH UNIQUE           |              |   11 |    88 |      5 (20)|
|* 2 |   HASH JOIN SEMI       |              |   11 |    88 |      4  (0)|
|  3 |    INDEX FULL SCAN     | PK_SERVERS   |  141 |   564 |      1  (0)|
|  4 |    INDEX FAST FULL SCAN| PK_SERV_INST |  999 |  3996 |      3  (0)|
--------------------------------------------------------------------------
Test Statement # 11

Join also a small unnecessary table
EXPLAIN PLAN FOR
SELECT DISTINCT s.srvr_id
FROM servers s, serv_inst i, dual d
WHERE s.srvr_id = i.srvr_id;

SELECT * FROM TABLE(dbms_xplan.display);
-- version 12.1.0.2
---------------------------------------------------------------------------
| Id | Operation               | Name         | Rows | Bytes | Cost (%CPU)|
---------------------------------------------------------------------------
|  0 | SELECT STATEMENT        |              |   11 |    88 |      5  (0)|
|  1 |  HASH UNIQUE            |              |   11 |    88 |      5  (0)|
|  2 |   NESTED LOOPS SEMI     |              |  999 |  7992 |      5  (0)|
|  3 |    NESTED LOOPS         |              |  999 |  3996 |      5  (0)|
|  4 |     FAST DUAL           |              |    1 |       |      2  (0)|
|  5 |     INDEX FAST FULL SCAN| PK_SERV_INST |  999 |  3996 |      3  (0)|
|* 6 |    INDEX UNIQUE SCAN    | PK_SERVERS   |  141 |   564 |      0  (0)|
---------------------------------------------------------------------------

-- version 18.3.0.1
---------------------------------------------------------------------------
| Id | Operation               | Name         | Rows | Bytes | Cost (%CPU)|
---------------------------------------------------------------------------
|  0 | SELECT STATEMENT        |              |   11 |    88 |      6 (17)|
|  1 |  HASH UNIQUE            |              |   11 |    88 |      6 (17)|
|  2 |   NESTED LOOPS SEMI     |              |  983 |  7864 |      5  (0)|
|  3 |    NESTED LOOPS         |              |  999 |  3996 |      5  (0)|
|  4 |     FAST DUAL           |              |    1 |       |      2  (0)|
|  5 |     INDEX FAST FULL SCAN| PK_SERV_INST |  999 |  3996 |      3  (0)|
|* 6 |    INDEX UNIQUE SCAN    | PK_SERVERS   |  141 |   564 |      0  (0)|
---------------------------------------------------------------------------
Test Statement # 12

Inline Views
EXPLAIN PLAN FOR
SELECT s.srvr_id
FROM
  (SELECT DISTINCT srvr_id FROM servers) s,
  (SELECT DISTINCT srvr_id FROM serv_inst) i
WHERE s.srvr_id = i.srvr_id;

SELECT * FROM TABLE(dbms_xplan.display);
-- version 12.1.0.2
---------------------------------------------------------------------------
| Id | Operation               | Name         | Rows | Bytes | Cost (%CPU)|
---------------------------------------------------------------------------
|  0 | SELECT STATEMENT        |              |   11 |   187 |      3  (0)|
|  1 |  NESTED LOOPS           |              |   11 |   187 |      3  (0)|
|  2 |   VIEW                  |              |   11 |   143 |      3  (0)|
|  3 |    HASH UNIQUE          |              |   11 |    44 |      3  (0)|
|  4 |     INDEX FAST FULL SCAN| PK_SERV_INST |  999 |  3996 |      3  (0)|
|* 5 |   INDEX UNIQUE SCAN     | PK_SERVERS   |    1 |     4 |      0  (0)|
---------------------------------------------------------------------------

-- version 18.3.0.1
---------------------------------------------------------------------------
| Id | Operation               | Name         | Rows | Bytes | Cost (%CPU)|
---------------------------------------------------------------------------
|  0 | SELECT STATEMENT        |              |   11 |   187 |      4 (25)|
|  1 |  NESTED LOOPS           |              |   11 |   187 |      4 (25)|
|  2 |   VIEW                  |              |   11 |   143 |      4 (25)|
|  3 |    HASH UNIQUE          |              |   11 |    44 |      4 (25)|
|  4 |     INDEX FAST FULL SCAN| PK_SERV_INST |  999 |  3996 |      3  (0)|
|* 5 |   INDEX UNIQUE SCAN     | PK_SERVERS   |    1 |     4 |      0  (0)|
---------------------------------------------------------------------------
Test Statement # 13

Joining a "small" superfluous view
EXPLAIN PLAN FOR
SELECT DISTINCT s.srvr_id
FROM servers s, serv_inst i, user_tables d
WHERE s.srvr_id = i.srvr_id;

SELECT * FROM TABLE(dbms_xplan.display);
-- version 12.1.0.2
---------------------------------------------------------------------------------------------
| Id | Operation                                     | Name       | Rows | Bytes|Cost (%CPU)|
---------------------------------------------------------------------------------------------
|   0|SELECT STATEMENT                               |            |   11 | 1320 | 2310   (8)|
|   1| HASH UNIQUE                                   |            |   11 | 1320 | 2310   (8)|
| * 2|  HASH JOIN                                    |            | 5768K|  660M| 2151   (1)|
|   3|   INDEX FAST FULL SCAN                        |PK_SERV_INST|  999 | 3996 |    3   (0)|
|   4|   MERGE JOIN CARTESIAN                        |            |  814K|   90M| 2133   (1)|
| * 5|    HASH JOIN OUTER                            |            | 5774 |  631K|  563   (1)|
| * 6|     HASH JOIN RIGHT OUTER                     |            | 5774 |  603K|  492   (1)|
|   7|      INDEX FULL SCAN                          |I_USER2     |  128 |  512 |    1   (0)|
| * 8|      HASH JOIN OUTER                          |            | 5774 |  580K|  491   (1)|
| * 9|       HASH JOIN                               |            | 5774 |  535K|  419   (1)|
|  10|        TABLE ACCESS FULL                      |TS$         |    5 |   15 |    3   (0)|
|* 11|        HASH JOIN RIGHT OUTER                  |            | 5774 |  518K|  416   (1)|
|  12|         TABLE ACCESS FULL                     |SEG$        | 3899 |42889 |   33   (0)|
|  13|         NESTED LOOPS                          |            | 2408 |  190K|  383   (1)|
|  14|          MERGE JOIN CARTESIAN                 |            | 2848 |  141K|  306   (1)|
|* 15|           HASH JOIN                           |            |    1 |   37 |    1 (100)|
|* 16|            FIXED TABLE FULL                   |X$KSPPI     |    1 |   31 |    0   (0)|
|* 17|            FIXED TABLE FULL                   |X$KSPPCV    | 3190 |19140 |    0   (0)|
|  18|           BUFFER SORT                         |            | 2848 |39872 |  305   (0)|
|* 19|            TABLE ACCESS BY INDEX ROWID BATCHED|OBJ$        | 2848 |39872 |  305   (0)|
|* 20|             INDEX SKIP SCAN                   |I_OBJ1      | 2848 |      |  258   (0)|
|* 21|          TABLE ACCESS CLUSTER                 |TAB$        |    1 |   30 |    1   (0)|
|* 22|           INDEX UNIQUE SCAN                   |I_OBJ#      |    1 |      |    0   (0)|
|  23|       INDEX FAST FULL SCAN                    |I_OBJ1      |91136 |  712K|   71   (0)|
|  24|     INDEX FAST FULL SCAN                      |I_OBJ1      |91136 |  445K|   71   (0)|
|  25|    BUFFER SORT                                |            |  141 |  564 | 2062   (1)|
|  26|     INDEX FAST FULL SCAN                      |PK_SERVERS  |  141 |  564 |    0   (0)|
---------------------------------------------------------------------------------------------

-- version 18.3.0.1
---------------------------------------------------------------------------------------------
| Id | Operation                               | Name             | Rows | Bytes|Cost (%CPU)|
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                  |   11 | 1958 |  1466 (15)|
|   1 |  HASH UNIQUE                           |                  |   11 | 1958 |  1466 (15)|
| * 2 |   FILTER                               |                  |      |      |           |
| * 3 |   HASH JOIN                            |                  | 2442K|  414M|  1287  (3)|
|   4 |    INDEX FAST FULL SCAN                | PK_SERV_INST     |  999 | 3996 |     3  (0)|
|   5 |    MERGE JOIN CARTESIAN                |                  |  350K|   58M|  1264  (2)|
| * 6 |     HASH JOIN OUTER                    |                  | 2486 |  412K|   585  (2)|
| * 7 |      HASH JOIN RIGHT OUTER             |                  | 2486 |  400K|   521  (2)|
|   8 |       INDEX FULL SCAN                  | I_USER2          |  140 |  560 |     1  (0)|
| * 9 |       HASH JOIN OUTER                  |                  | 2486 |  390K|   520  (2)|
|  10 |        NESTED LOOPS OUTER              |                  | 2486 |  371K|   457  (2)|
|* 11 |         HASH JOIN                      |                  | 2486 |  308K|   456  (2)|
|  12 |          TABLE ACCESS FULL             | TS$              |    8 |   24 |     5  (0)|
|* 13 |          HASH JOIN RIGHT OUTER         |                  | 2486 |  301K|   451  (2)|
|  14 |           TABLE ACCESS FULL            |SEG$             | 2552 | 30624|    22  (0)|
|  15 |           NESTED LOOPS                 |                  | 2239 |  244K|   429  (2)|
|* 16 |            HASH JOIN                   |                  | 2912 |  238K|   337  (2)|
|  17 |             INDEX FULL SCAN            | I_USER2         |  140 | 3360 |     1  (0)|
|  18 |              MERGE JOIN CARTESIAN      |                  | 2912 |  170K|   336  (2)|
|  19 |               NESTED LOOPS             |                  |    1 |   38 |     0  (0)|
|* 20 |                FIXED TABLE FIXED INDEX | X$KSPPI (ind:1)  |    1 |   31 |     0  (0)|
|* 21 |                FIXED TABLE FIXED INDEX | X$KSPPCV (ind:1) |    1 |    7 |     0  (0)|
|  22 |               BUFFER SORT              |                  | 2912 | 64064|   336  (2)|
|* 23 |                TABLE ACCESS FULL       | OBJ$             | 2912 | 64064|   336  (2)|
|* 24 |             TABLE ACCESS CLUSTER       | TAB$             |    1 |   28 |     1  (0)|
|* 25 |              INDEX UNIQUE SCAN         | I_OBJ#           |    1 |      |     0  (0)|
|* 26 |          INDEX RANGE SCAN              | I_IMSVC1         |    1 |   26 |     0  (0)|
|  27 |         INDEX FAST FULL SCAN           | I_OBJ1          | 72811|  568K|    63  (2)|
|  28 |       INDEX FAST FULL SCAN             | I_OBJ1           | 72811|  355K|    63  (2)|
|  29 |      BUFFER SORT                       |                  |  141 |  564 |  1202  (2)|
|  30 |       INDEX FAST FULL SCAN             | PK_SERVERS       |  141 |  564 |     0  (0)|
|* 31 |    TABLE ACCESS BY INDEX ROWID BATCHED | USER_EDITIONING$ |    1 |    7 |     2  (0)|
|* 32 |     INDEX RANGE SCAN                   | I_USER_EDITIONING|   12 |      |     1  (0)|
|* 33 |    TABLE ACCESS BY INDEX ROWID BATCHED | USER_EDITIONING$ |    1 |    7 |     2  (0)|
|* 34 |     INDEX RANGE SCAN                   | I_USER_EDITIONING|   12 |      |     1  (0)|
|  35 |    NESTED LOOPS SEMI                   |                  |    1 |   29 |     2  (0)|
|* 36 |     INDEX SKIP SCAN                    | I_USER2          |    1 |   20 |     1  (0)|
|* 37 |     INDEX RANGE SCAN                   | I_OBJ4           |    1 |    9 |     1  (0)|
---------------------------------------------------------------------------------------------
 
Demos
Index Join EXPLAIN PLAN FOR
SELECT COUNT(*)
FROM svc_merch.suborders so
WHERE so.suborder_status_id NOT IN (7, 14)
AND create_date > SYSDATE-90;

SELECT * FROM TABLE(dbms_xplan.display);
---------------------------------------------------------------------------------
| Id| Operation              | Name                    | Rows| Bytes| Cost(%CPU)|
---------------------------------------------------------------------------------
|  0| SELECT STATEMENT       |                         |   1 |    7 |  251K  (1)|
|  1|  SORT AGGREGATE        |                         |   1 |    7 |           |
|* 2|   VIEW                 | index$_join$_001        |1566K|   10M|  251K  (1)|
|* 3|   HASH JOIN            |                         |     |      |           |
|* 4|    INDEX RANGE SCAN    | SUBORDER_CREATE_DATE_IDX|1566K|   10M|  53330 (1)|
|* 5|    INDEX FAST FULL SCAN| IDX_DATE_STATUS_DC      |1566K|   10M|  190K  (1)|
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("CREATE_DATE">SYSDATE@!-90)
   3 - access(ROWID=ROWID)
   4 - access("CREATE_DATE">SYSDATE@!-90)
   5 - filter("SO"."SUBORDER_STATUS_ID"<>14 AND "SO"."SUBORDER_STATUS_ID"<>7)
Transitive Closure -- compare this in 11.2.0.3

EXPLAIN PLAN FOR
SELECT *
FROM servers
WHERE srvr_id < 0 AND srvr_id > 10;

SELECT * FROM TABLE(dbms_xplan.display);

-------------------------------------------------------------------------------
| Id | Operation                        | Name    | Rows | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                 |         | 8168 | 16336 |      21 (5)|
|  1 | COLLECTION ITERATOR PICKLER FETCH| DISPLAY |      |       |            |
-------------------------------------------------------------------------------

-- with this in 12.1.0.1 and 18.3.0.1

EXPLAIN PLAN FOR
SELECT * FROM servers WHERE srvr_id < 0 AND srvr_id > 10;

SELECT * FROM TABLE(dbms_xplan.display);

--------------------------------------------------------------------------------------
| Id | Operation                            | Name       | Rows | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                     |            |    1 |    37 |      0  (0)|
|* 1 |  FILTER                              |            |      |       |            |
|  2 |   TABLE ACCESS BY INDEX ROWID BATCHED| SERVERS    |    1 |    37 |      2  (0)|
|* 3 |    INDEX RANGE SCAN                  | PK_SERVERS |    7 |       |      1  (0)|
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(NULL IS NOT NULL)
3 - access("SRVR_ID">10 AND "SRVR_ID"<0)

-- in both 11.2.0.3 and 12.1.0.1 the autotrace is the same as shown below

set autotrace traceonly explain

SELECT *
FROM servers
WHERE srvr_id < 0 AND srvr_id > 10;

--------------------------------------------------------------------------------------
| Id | Operation                            | Name       | Rows | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                     |            |    1 |    37 |      0  (0)|
|* 1 |  FILTER                              |            |    | |       |            |
|  2 |   TABLE ACCESS BY INDEX ROWID BATCHED| SERVERS    |    1 |    37 |      2  (0)|
|* 3 |    INDEX RANGE SCAN                  | PK_SERVERS |    7 |       |      1  (0)|
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(NULL IS NOT NULL)
3 - access("SRVR_ID">10 AND "SRVR_ID"<0)

set autotrace off
Demo with NULLABLE column CREATE TABLE nullable AS
SELECT * FROM serv_inst;

INSERT INTO nullable
SELECT * FROM serv_inst;
COMMIT;

exec dbms_stats.gather_table_stats('UWCLASS', 'NULLABLE');

col table_name format a12
col column_name format a14
col low_value format a20
col high_value format a20

SELECT table_name, column_name, nullable, num_distinct, low_value, high_value, density, num_nulls
FROM dba_tab_cols
WHERE table_name in ('SERV_INST','NULLABLE')
ORDER BY 2,1;

EXPLAIN PLAN FOR
SELECT srvr_id
FROM servers
WHERE srvr_id IN (
  SELECT srvr_id
  FROM serv_inst);

SELECT plan_table_output FROM table(dbms_xplan.display);

--------------------------------------------------------------------------
| Id | Operation              | Name         | Rows | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------
|  0 | SELECT STATEMENT       |              |   11 |    88 |      4 (25)|
|  1 |  NESTED LOOPS          |              |   11 |    88 |      4 (25)|
|  2 |   SORT UNIQUE          |              |  999 |  3996 |      3  (0)|
|  3 |    INDEX FAST FULL SCAN| PK_SERV_INST |  999 |  3996 |      3  (0)|
|* 4 |   INDEX UNIQUE SCAN    | PK_SERVERS   |    1 |     4 |      0  (0)|
--------------------------------------------------------------------------

EXPLAIN PLAN FOR
SELECT srvr_id
FROM servers
WHERE srvr_id IN (
  SELECT srvr_id
  FROM nullable);

SELECT plan_table_output FROM table(dbms_xplan.display);

---------------------------------------------------------------------
| Id | Operation           | Name       | Rows | Bytes | Cost (%CPU)|
---------------------------------------------------------------------
|  0 | SELECT STATEMENT    |            |   11 |    88 |     11  (0)|
|* 1 |  HASH JOIN SEMI     |            |   11 |    88 |     11  (0)|
|  2 |   INDEX FULL SCAN   | PK_SERVERS |  141 |   564 |      1  (0)|
|  3 |   TABLE ACCESS FULL | NULLABLE   | 1998 |  7992 |     10  (0)|
---------------------------------------------------------------------

EXPLAIN PLAN FOR
SELECT srvr_id
FROM servers
WHERE srvr_id NOT IN (
  SELECT srvr_id
  FROM serv_inst);

SELECT plan_table_output FROM table(dbms_xplan.display);

-------------------------------------------------------------------------
| Id | Operation             | Name         | Rows | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------
|  0 | SELECT STATEMENT      |              |  130 |  1040 |      4  (0)|
|* 1 |  HASH JOIN ANTI       |              |  130 |  1040 |      4  (0)|
|  2 |   INDEX FULL SCAN     | PK_SERVERS   |  141 |   564 |      1  (0)|
|  3 |   INDEX FAST FULL SCAN| PK_SERV_INST |  999 |  3996 |      3  (0)|
-------------------------------------------------------------------------

EXPLAIN PLAN FOR
SELECT srvr_id
FROM servers
WHERE srvr_id NOT IN (
  SELECT srvr_id
  FROM nullable);

SELECT plan_table_output FROM table(dbms_xplan.display);

---------------------------------------------------------------------
| Id | Operation           | Name       | Rows | Bytes | Cost (%CPU)|
---------------------------------------------------------------------
|  0 | SELECT STATEMENT    |            |  130 |  1040 |     11  (0)|
|* 1 |  HASH JOIN ANTI NA  |            |  130 |  1040 |     11  (0)|
|  2 |   INDEX FULL SCAN   | PK_SERVERS |  141 |   564 |      1  (0)|
|  3 |   TABLE ACCESS FULL | NULLABLE   | 1998 |  7992 |     10  (0)|
---------------------------------------------------------------------
Demo with Parallel Query CREATE TABLE airparallel AS
SELECT * FROM airplanes;

ALTER TABLE airparallel PARALLEL 2;

EXPLAIN PLAN FOR
SELECT program_id, SUM(line_number)
FROM airparallel
GROUP BY program_id;

SELECT plan_table_output FROM table(dbms_xplan.display);

-- version 12.1.0.2
---------------------------------------------------------------------------------------------
| Id | Operation               | Name       | Rows |Bytes|Cost(%CPU)|TQ   |IN-OUT|PQ Distrib|
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT         |            |    5 |  45 |   186 (3)|     |      |          |
| 1 |  PX COORDINATOR          |            |      |     |          |     |      |          |
| 2 |   PX SEND QC (RANDOM)    | :TQ10001   |    5 |  45 |   186 (3)|Q1,01| P->S | QC(RAND) |
| 3 |    HASH GROUP BY         |            |    5 |  45 |   186 (3)|Q1,01| PCWP |          |
| 4 |     PX RECEIVE           |            |    5 |  45 |   186 (3)|Q1,01| PCWP |          |
| 5 |      PX SEND HASH        | :TQ10000   |    5 |  45 |   186 (3)|Q1,00| P->P |     HASH |
| 6 |       HASH GROUP BY      |            |    5 |  45 |   186 (3)|Q1,00| PCWP |          |
| 7 |        PX BLOCK ITERATOR |            |  250K|2197K|   183 (1)|Q1,00| PCWC |          |
| 8 |         TABLE ACCESS FULL| AIRPARALLEL|  250K|2197K|   183 (1)|Q1,00| PCWP |          |
---------------------------------------------------------------------------------------------

-- version 18.3.0.1
----------------------------------------------------------------------------------------------
| Id | Operation               | Name       | Rows |Bytes|Cost(%CPU)|TQ    |IN-OUT|PQ Distrib|
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT         |            |    5 |  45 |  194  (7)|      |      |          |
| 1 |  PX COORDINATOR          |            |      |     |          |      |      |          |
| 2 |   PX SEND QC (RANDOM)    | :TQ10001   |    5 |  45 |  194  (7)|Q1,01 | P->S | QC (RAND)|
| 3 |    HASH GROUP BY         |            |    5 |  45 |  194  (7)|Q1,01 | PCWP |          |
| 4 |     PX RECEIVE           |            |    5 |  45 |  194  (7)|Q1,01 | PCWP |          |
| 5 |      PX SEND HASH        | :TQ10000   |    5 |  45 |  194  (7)|Q1,00 | P->P |     HASH |
| 6 |       HASH GROUP BY      |            |    5 |  45 |  194  (7)|Q1,00 | PCWP |          |
| 7 |        PX BLOCK ITERATOR |            |  250K|2197K|  184  (2)|Q1,00 | PCWC |          |
| 8 |         TABLE ACCESS FULL| AIRPARALLEL|  250K|2197K|  184  (2)|Q1,00 | PCWP |          |
----------------------------------------------------------------------------------------------
Demo with Bitmap Index EXPLAIN PLAN FOR
SELECT *
FROM serv_inst
WHERE location_code = 30386
OR ws_id BETWEEN 326 AND 333;

SELECT * FROM table(dbms_xplan.display);

-- version 12.1.0.2
---------------------------------------------------------------------------------------------
| Id| Operation                           | Name                      |Rows|Bytes|Cost(%CPU)|
---------------------------------------------------------------------------------------------
|  0| SELECT STATEMENT                    |                           |  30| 1260|   7   (0)|
|  1|  TABLE ACCESS BY INDEX ROWID BATCHED|SERV_INST                  |  30| 1260|   7   (0)|
|  2|   BITMAP CONVERSION TO ROWIDS       |                           |    |     |          |
|  3|    BITMAP OR                        |                           |    |     |          |
|  4|     BITMAP MERGE                    |                           |    |     |          |
|* 5|      BITMAP INDEX RANGE SCAN        |BIX_SERV_INST_WS_ID        |    |     |          |
|* 6|     BITMAP INDEX SINGLE VALUE       |BIX_SERV_INST_LOCATION_CODE|    |     |          |
---------------------------------------------------------------------------------------------

-- version 18.3.0.1
-----------------------------------------------------------------------------------------------
| Id| Operation                           | Name                       |Rows|Bytes|Cost(%CPU)|
-----------------------------------------------------------------------------------------------
|  0| SELECT STATEMENT                    |                            | 12 | 504 |    5  (0)|
|  1|  TABLE ACCESS BY INDEX ROWID BATCHED| SERV_INST                  | 12 | 504 |    5  (0)|
|  2|   BITMAP CONVERSION TO ROWIDS       |                            |    |     |          |
|  3|    BITMAP OR                        |                            |    |     |          |
|* 4|     BITMAP INDEX SINGLE VALUE       | BIX_SERV_INST_LOCATION_CODE|    |     |          |
|  5|     BITMAP MERGE                    |                            |    |     |          |
|* 6|      BITMAP INDEX RANGE SCAN        | BIX_SERV_INST_WS_ID        |    |     |          |
----------------------------------------------------------------------------------------------
Demo with IOT conn uwclass/uwclass@pdbdev

CREATE TABLE reg_tab (
state   VARCHAR2(2),
city    VARCHAR2(30),
zipcode VARCHAR2(5));

ALTER TABLE reg_tab
ADD CONSTRAINT pk_reg_tab
PRIMARY KEY (zipcode)
USING INDEX;

INSERT INTO reg_tab VALUES ('WA', 'Seattle', '98101');
INSERT INTO reg_tab VALUES ('WA', 'Seattle', '98102');
INSERT INTO reg_tab VALUES ('WA', 'Seattle', '98103');
INSERT INTO reg_tab VALUES ('WA', 'Seattle', '98104');
INSERT INTO reg_tab VALUES ('WA', 'Seattle', '98105');

CREATE TABLE iot_tab (
state   VARCHAR2(2),
city    VARCHAR2(30),
zipcode VARCHAR2(5),
CONSTRAINT pk_iot_tab
PRIMARY KEY (zipcode))
ORGANIZATION INDEX;

INSERT INTO iot_tab VALUES ('WA', 'Seattle', '98101');
INSERT INTO iot_tab VALUES ('WA', 'Seattle', '98102');
INSERT INTO iot_tab VALUES ('WA', 'Seattle', '98103');
INSERT INTO iot_tab VALUES ('WA', 'Seattle', '98104');
INSERT INTO iot_tab VALUES ('WA', 'Seattle', '98105');
COMMIT;

exec dbms_stats.gather_schema_stats(USER, CASCADE=>TRUE);

EXPLAIN PLAN FOR
SELECT * FROM reg_tab WHERE zipcode = '98004';
-----------------------------------------------------------------------------
| Id | Operation                   | Name       | Rows | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------
|  0 | SELECT STATEMENT            |            |    1 |    17 |    1    (0)|
|  1 |  TABLE ACCESS BY INDEX ROWID| REG_TAB    |    1 |    17 |    1    (0)|
|* 2 |  INDEX UNIQUE SCAN          | PK_REG_TAB |    1 |       |    0    (0)|
-----------------------------------------------------------------------------

SELECT * FROM table(dbms_xplan.display);

EXPLAIN PLAN FOR
SELECT * FROM iot_tab WHERE zipcode = '98004';

SELECT * FROM table(dbms_xplan.display);
-------------------------------------------------------------------
| Id | Operation         | Name       | Rows | Bytes | Cost (%CPU)|
-------------------------------------------------------------------
|  0 | SELECT STATEMENT  |            |    1 |    17 |     0   (0)|
|* 1 |  INDEX UNIQUE SCAN| PK_IOT_TAB |    1 |    17 |     0   (0)|
-------------------------------------------------------------------
Demo with Partitions and Local Indexes

-- tablespace build on the Partitions page

CREATE TABLE part_zip (
state   VARCHAR2(2),
city    VARCHAR2(30),
zipcode VARCHAR2(5))
PARTITION BY HASH (state)
PARTITIONS 3;

INSERT INTO part_zip VALUES ('WA', 'Seattle', '98101');
INSERT INTO part_zip VALUES ('WA', 'Seattle', '98102');
INSERT INTO part_zip VALUES ('WA', 'Seattle', '98103');
INSERT INTO part_zip VALUES ('WA', 'Seattle', '98104');
INSERT INTO part_zip VALUES ('WA', 'Seattle', '98105');
INSERT INTO part_zip VALUES ('CA', 'San Francisco', '94105');
INSERT INTO part_zip VALUES ('CA', 'San Francisco', '94107');
INSERT INTO part_zip VALUES ('CA', 'San Francisco', '94111');
INSERT INTO part_zip VALUES ('HI', 'Honolulu', '96813');
INSERT INTO part_zip VALUES ('HI', 'Honolulu', '96817');
INSERT INTO part_zip VALUES ('HI', 'Honolulu', '96822');
COMMIT;

EXPLAIN PLAN FOR
SELECT *
FROM part_zip
WHERE state = 'HI';

SELECT * FROM table(dbms_xplan.display);

-------------------------------------------------------------------------------------
| Id | Operation            | Name      | Rows | Bytes | Cost (%CPU)| Pstart| Pstop |
-------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT      |          |    3 |    72 |      9  (0)|       |       |
|  1 |  PARTITION HASH SINGLE|          |    3 |    72 |      9  (0)|     1 |     1 |
|* 2 |   TABLE ACCESS FULL   | PART_ZIP |    3 |    72 |      9  (0)|     1 |     1 |
-------------------------------------------------------------------------------------

EXPLAIN PLAN FOR
SELECT *
FROM part_zip
WHERE state IN ('HI', 'WA');

SELECT * FROM TABLE(dbms_xplan.display);

---------------------------------------------------------------------------------------
| Id | Operation             | Name     | Rows | Bytes | Cost (%CPU)| Pstart | Psto p |
---------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT      |          |    8 |   192 |     17  (0)|        |        |
|  1 |  PARTITION HASH INLIST|          |    8 |   192 |     17  (0)| KEY(I) | KEY(I) |
|* 2 |   TABLE ACCESS FULL   | PART_ZIP |    8 |   192 |     17  (0)| KEY(I) | KEY(I) |
---------------------------------------------------------------------------------------

EXPLAIN PLAN FOR
SELECT *
FROM part_zip
WHERE zipcode LIKE '%5%';

SELECT * FROM table(dbms_xplan.display);

-----------------------------------------------------------------------------------
| Id | Operation           | Name     | Rows | Bytes | Cost (%CPU)| Pstart| Pstop |
-----------------------------------------------------------------------------------
|  0 | SELECT STATEMENT    |          |    3 |    72 |     25  (0)|       |       |
|  1 |  PARTITION HASH ALL |          |    3 |    72 |     25  (0)|     1 |     3 |
|* 2 |   TABLE ACCESS FULL | PART_ZIP |    3 |    72 |     25  (0)|     1 |     3 |
-----------------------------------------------------------------------------------

TEMP Tablespace Usage Required -- with thanks to Jonathan Lewis

EXPLAIN PLAN FOR
SELECT source
FROM sys.source$
ORDER BY source;

SELECT * FROM TABLE(dbms_xplan.display);

----------------------------------------------------------------------------
| Id | Operation          | Name    | Rows  | Bytes | TempSpc | Cost (%CPU)|
----------------------------------------------------------------------------
|  0 | SELECT STATEMENT   |         | 13916 |  1997K|         |   603   (1)|
|  1 |  SORT ORDER BY     |         | 13916 |  1997K|   2152K |   603   (1)|
|  2 |   TABLE ACCESS FULL| SOURCE$ | 13916 |  1997K|         |   147   (0)|
----------------------------------------------------------------------------

Related Topics
AutoTrace
DBMS_ADVISOR
DBMS_SPM
DBMS_STATS
DBMS_SUPPORT
DBMS_XPLAN
TKPROF
Tuning
Trace
What's New In 12cR2
What's New In 18cR3

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