| 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 |
|
|
|
| Related Files in $ORACLE_HOME/rdbms/admin |
|
| |
| 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)|
---------------------------------------------------------------------------- |
