Oracle Explain Plan

set serveroutput on;
clear;

explain plan for
   select
   d.id,
   --d.xml,
   extractValue(d.xml,'/Account/StarId')"StarID",
   extractValue(d.xml,'/Account/Name') "DistributorName",
   NVL(extractValue(d.xml,'/Account/FranchiseCode'),'< Null >') "FranchiseCode"
   from
   genesis_dist_to_ent d
   where
   -- rownum <= 5
   extractValue(d.xml,'/Account/StarId') = '16534' ;

SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

Results:

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
| Id  | Operation                    | Name                          | Rows  | B
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                               | 29002 |
|*  1 |  TABLE ACCESS BY INDEX ROWID | GENESIS_XMLINDEX_PATHTABLE    |   371 |
|*  2 |   INDEX RANGE SCAN           | SYS3119198_GENESIS__PATHID_IX |     4 |
|*  3 |  TABLE ACCESS BY INDEX ROWID | GENESIS_XMLINDEX_PATHTABLE    |   371 |
|*  4 |   INDEX RANGE SCAN           | SYS3119198_GENESIS__PATHID_IX |     4 |
|*  5 |  TABLE ACCESS BY INDEX ROWID | GENESIS_XMLINDEX_PATHTABLE    |   371 |
|*  6 |   INDEX RANGE SCAN           | SYS3119198_GENESIS__PATHID_IX |     4 |
|*  7 |  FILTER                      |                               |       |
|   8 |   INDEX FAST FULL SCAN       | PK_GENESIS_DIST_TO_ENT        | 29002 |
|*  9 |   TABLE ACCESS BY INDEX ROWID| GENESIS_XMLINDEX_PATHTABLE    |   371 |
|* 10 |    INDEX RANGE SCAN          | SYS3119198_GENESIS__PATHID_IX |     4 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
   1 - filter(SYS_XMLI_LOC_ISNODE("SYS_P0"."LOCATOR")=1)
   2 - access("SYS_P0"."PATHID"=HEXTORAW('4B1D')  AND "SYS_P0"."RID"=:B1)
   3 - filter(SYS_XMLI_LOC_ISNODE("SYS_P2"."LOCATOR")=1)
   4 - access("SYS_P2"."PATHID"=HEXTORAW('7F13')  AND "SYS_P2"."RID"=:B1)
   5 - filter(SYS_XMLI_LOC_ISNODE("SYS_P4"."LOCATOR")=1)
   6 - access("SYS_P4"."PATHID"=HEXTORAW('35BE')  AND "SYS_P4"."RID"=:B1)
   7 - filter( (SELECT "SYS_P6"."VALUE" FROM "PROD"."GENESIS_XMLINDEX_PATHTABLE"
              WHERE "SYS_P6"."RID"=:B1 AND "SYS_P6"."PATHID"=HEXTORAW('4B1D')  A
              SYS_XMLI_LOC_ISNODE("SYS_P6"."LOCATOR")=1)='16534')
   9 - filter(SYS_XMLI_LOC_ISNODE("SYS_P6"."LOCATOR")=1)
  10 - access("SYS_P6"."PATHID"=HEXTORAW('4B1D')  AND "SYS_P6"."RID"=:B1)
Note
-----
   - 'PLAN_TABLE' is old version
 
35 rows selected