Oracle Explain Plan
Version 19c

General Information
Library Note Morgan's Library Page Header
ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.
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    
Related Files in $ORACLE_HOME/rdbms/admin
UTLXPLAN UTLXPLP UTLXPLS
 
Preparation
Create the plan table if it does not already exist 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 uwclass;
 
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)|
--------------------------------------------------------------------------

-- versions 18.3 and 19.3
--------------------------------------------------------------------------
| 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 and 19.3
--------------------------------------------------------------------------
| 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 and 19.3
----------------------------------------------------------------------------
| 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 and 19.3
-------------------------------------------------------------------------
| 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 and 19.3
--------------------------------------------------------------------------
| 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 and 19.3
--------------------------------------------------------------------------
| 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 and 19.3
--------------------------------------------------------------------------
| 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 and 19.3
--------------------------------------------------------------------------
| 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 and 19.3
--------------------------------------------------------------------------
| 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 and 19.3
-------------------------------------------------------------------------
| 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 and 19.3
----------------------------------------------------------------------------
| 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 and 19.3
--------------------------------------------------------------------------
| 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 and 19.3
---------------------------------------------------------------------------
| 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 and 19.3
---------------------------------------------------------------------------
| 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 and 19.3
---------------------------------------------------------------------------------------------
| 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 and 19.3
----------------------------------------------------------------------------------------------
| 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 and 19.3
-----------------------------------------------------------------------------------------------
| 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
Built-in Functions
Built-in Packages
Database Security
AutoTrace
DBMS_ADVISOR
DBMS_SPM
DBMS_STATS
DBMS_SUPPORT
DBMS_XPLAN
TKPROF
Tuning
Trace
What's New In 21c
What's New In 23c

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