Oracle Explain Plan
Version 12.1.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);
 
Generation 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 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);
--------------------------------------------------------------------------
| 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)|
--------------------------------------------------------------------------
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);
--------------------------------------------------------------------------
| 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)|
--------------------------------------------------------------------------
4 - access("SRVR_ID"="SRVR_ID")
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);
----------------------------------------------------------------------------
| 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)|
----------------------------------------------------------------------------
6 - access("I"."SRVR_ID"="S"."SRVR_ID")
7 - access("SRVR_ID"="SRVR_ID")
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);
-------------------------------------------------------------------------
| 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)|
-------------------------------------------------------------------------
4 - access("S"."SRVR_ID"="I"."SRVR_ID")
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);
--------------------------------------------------------------------------
| 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)|
--------------------------------------------------------------------------
2 - access("S"."SRVR_ID"="I"."SRVR_ID")
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);
--------------------------------------------------------------------------
| 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)|
--------------------------------------------------------------------------
4 - access("S"."SRVR_ID"="I"."SRVR_ID")
    filter("S"."SRVR_ID"="I"."SRVR_ID")
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);
--------------------------------------------------------------------------
| 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)|
--------------------------------------------------------------------------
1 - access("SRVR_ID"="SRVR_ID")
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);
--------------------------------------------------------------------------
| 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)|
--------------------------------------------------------------------------
4 - access("S"."SRVR_ID"="I"."SRVR_ID")
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);
--------------------------------------------------------------------------
| 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)|
--------------------------------------------------------------------------
5 - access("S"."SRVR_ID"="I"."SRVR_ID")
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);
-------------------------------------------------------------------------
| 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)|
-------------------------------------------------------------------------
4 - access("S"."SRVR_ID"(+)="I"."SRVR_ID")
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);
----------------------------------------------------------------------------
| 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)|
----------------------------------------------------------------------------
1 - filter(SUM("CNT")=2)
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);
--------------------------------------------------------------------------
| 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)|
--------------------------------------------------------------------------
2 -access("S"."SRVR_ID"+0="I"."SRVR_ID"+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);
---------------------------------------------------------------------------
| 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)|
---------------------------------------------------------------------------
6 - access("S"."SRVR_ID"="I"."SRVR_ID")
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);
---------------------------------------------------------------------------
| 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)|
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("SRVR_ID"="I"."SRVR_ID")
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);
---------------------------------------------------------------------------------------------
| 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)|
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
 2 - access("S"."SRVR_ID"="I"."SRVR_ID")
5 - access("T"."BOBJ#"="CO"."OBJ#"(+))
6 - access("CX"."OWNER#"="CU"."USER#"(+))
8 - access("T"."DATAOBJ#"="CX"."OBJ#"(+))
9 - access("T"."TS#"="TS"."TS#")
11 - access("T"."FILE#"="S"."FILE#"(+) AND "T"."BLOCK#"="S"."BLOCK#"(+) AND "T"."TS#"="S"."TS#"(+))
15 - access("INDX"="INDX")
16 - filter("KSPPINM"='_dml_monitoring_enabled' AND ("CON_ID"=0 OR "CON_ID"=3))
17 - filter("CON_ID"=0 OR "CON_ID"=3)
19 - filter(BITAND("O"."FLAGS",128)=0)
20 - access("O"."OWNER#"=USERENV('SCHEMAID'))
filter("O"."OWNER#"=USERENV('SCHEMAID'))
21 - filter(BITAND("T"."PROPERTY",1)=0)
22 - access("O"."OBJ#"="T"."OBJ#")
 
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

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 autotraceis 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(USER, '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)| Time     |
-------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT       |              |   11 |    88 |     3   (0)| 00:00:01 |
|  1 |  NESTED LOOPS          |              |   11 |    88 |     3   (0)| 00:00:01 |
|  2 |   SORT UNIQUE          |              |  999 |  3996 |     3   (0)| 00:00:01 |
|  3 |    INDEX FAST FULL SCAN| PK_SERV_INST |  999 |  3996 |     3   (0)| 00:00:01 |
|* 4 |   INDEX UNIQUE SCAN    | PK_SERVERS   |    1 |     4 |     0   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

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)| Time     |
--------------------------------------------------------------------------------
|  0 | SELECT STATEMENT    |            |   11 |    88 |    10   (0)| 00:00:01 |
|  1 |  NESTED LOOPS       |            |   11 |    88 |    10   (0)| 00:00:01 |
|  2 |   SORT UNIQUE       |            | 1998 |  7992 |    10   (0)| 00:00:01 |
|  3 |    TABLE ACCESS FULL| NULLABLE   | 1998 |  7992 |    10   (0)| 00:00:01 |
|* 4 |   INDEX UNIQUE SCAN | PK_SERVERS |    1 |     4 |     0   (0)| 00:00:01 |
--------------------------------------------------------------------------------

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)| Time     |
------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT      |              |  130 |  1040 |     4   (0)| 00:00:01 |
|* 1 |  HASH JOIN ANTI       |              |  130 |  1040 |     4   (0)| 00:00:01 |
|  2 |   INDEX FULL SCAN     | PK_SERVERS   |  141 |   564 |     1   (0)| 00:00:01 |
|  3 |   INDEX FAST FULL SCAN| PK_SERV_INST |  999 |  3996 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------

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)| Time     |
-------------------------------------------------------------------------------
|  0 | SELECT STATEMENT   |            |  130 |  1040 |    11   (0)| 00:00:01 |
|* 1 |  HASH JOIN ANTI NA |            |  130 |  1040 |    11   (0)| 00:00:01 |
|  2 |   INDEX FULL SCAN  | PK_SERVERS |  141 |   564 |     1   (0)| 00:00:01 |
|  3 |   TABLE ACCESS FULL| NULLABLE   |  999 |  7992 |    10   (0)| 00:00:01 |
-------------------------------------------------------------------------------
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);

---------------------------------------------------------------------------------------------
| 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 |          |
---------------------------------------------------------------------------------------------
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);

---------------------------------------------------------------------------------------------
| 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|    |     |          |
----------------------------------------------------------------------------------------------
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
STORE IN (part1, part2, part3);

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);

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

SELECT * FROM TABLE(dbms_xplan.display);

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

SELECT * FROM table(dbms_xplan.display);
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)| Time     |
-------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT |         | 19060 |   763K|         |   250   (1)| 00:00:01 |
|  1 | SORT ORDER BY    |         | 19060 |   763K|    912K |   250   (1)| 00:00:01 |
|  2 | TABLE ACCESS FULL| SOURCE$ | 19060 |   763K|         |    46   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Related Topics
AutoTrace
DBMS_ADVISOR
DBMS_SPM
DBMS_STATS
DBMS_SUPPORT
DBMS_XPLAN
TKPROF
Tuning
Trace

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