title: Oracle Explain Plan for Query with Collection using Table function
body: <p>How to generate Plan in Oracle for the query using some Collection in exists clause using table function</p>
  
  <p>e.g.</p>
  
  <pre><code>CREATE TYPE TYP_EMP AS OBJECT(
  EMPNO     NUMBER(4),
  ENAME     VARCHAR2(10),
  JOB       VARCHAR2(9),
  MGR       NUMBER(4),
  HIREDATE  DATE,
  SAL       NUMBER(7,2),
  COMM      NUMBER(7,2),
  DEPTNO    NUMBER(2));
  /
  </code></pre>
  
  <p>Type created.</p>
  
  <pre><code>CREATE TYPE TYP_EMP_COLL AS TABLE OF TYP_EMP;
  /
  </code></pre>
  
  <p>Type created.</p>
  
  <pre><code>CREATE OR REPLACE VIEW VW_EMP 
  OF TYP_EMP
  WITH OBJECT IDENTIFIER(EMPNO) AS
  SELECT
  EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO
  FROM EMP
  /
  </code></pre>
  
  <p>View Created </p>
  
  <pre><code>CREATE TYPE TYP_DEPT AS OBJECT(
  DEPTNO NUMBER(2),
  DNAME  VARCHAR2(14),
  LOC    VARCHAR2(13));
  /
  </code></pre>
  
  <p>Type Created</p>
  
  <pre><code>CREATE TYPE TYP_DEPT_COLL AS TABLE OF TYP_DEPT;
  /
  </code></pre>
  
  <p>Type Created</p>
  
  <pre><code>CREATE OR REPLACE VIEW VW_DEPT
  OF TYP_DEPT
  WITH OBJECT IDENTIFIER(DEPTNO) AS
  SELECT
  DEPTNO, DNAME, LOC
  FROM DEPT
  /
  
  CREATE OR REPLACE PROCEDURE SP_EMPTEST AS
  V_EMP TYP_EMP_COLL;
  V_DEPT TYP_DEPT_COLL;
  BEGIN
  SELECT VALUE(V) BULK COLLECT INTO V_EMP FROM VW_EMP V;
  SELECT VALUE(VD)
  BULK COLLECT INTO V_DEPT FROM VW_DEPT VD
  WHERE EXISTS
      (SELECT 1 FROM TABLE(V_EMP) VV WHERE VD.DEPTNO = VV.DEPTNO);
  END;
  /
  </code></pre>
  
  <hr>
  
  <h2>TKPROF OUTPUT OF TRACE FILE</h2>
  
  <pre><code>********************************************************************************
  
  SQL ID: 7c02yjs9q5kqr
  Plan Hash: 2616009478
  SELECT VALUE(VD) 
  FROM
  VW_DEPT VD WHERE EXISTS (SELECT 1 FROM TABLE(:B1 ) VV WHERE VD.DEPTNO = VV.DEPTNO)
  
  
  call     count       cpu    elapsed       disk      query    current        rows
  ------- ------  -------- ---------- ---------- ---------- ----------  ----------
  Parse        1      0.00       0.00          0          0          0           0
  Execute      1      0.00       0.00          0         19          0           0
  Fetch        1      0.00       0.00          0          7          0           3
  ------- ------  -------- ---------- ---------- ---------- ----------  ----------
  total        3      0.00       0.00          0         26          0           3
  
  Misses in library cache during parse: 1
  Misses in library cache during execute: 1
  Optimizer mode: ALL_ROWS
  Parsing user id: 84  (mac)   (recursive depth: 1)
  
  Rows     Row Source Operation
  -------  ---------------------------------------------------
    3  HASH JOIN SEMI (cr=7 pr=0 pw=0 time=6 us cost=33 size=32 card=1)
    4   TABLE ACCESS FULL DEPT (cr=7 pr=0 pw=0 time=0 us cost=3 size=120 card=4)
   14   COLLECTION ITERATOR PICKLER FETCH (cr=0 pr=0 pw=0 time=0 us cost=29 size=28 card=14)
  
  error during execute of EXPLAIN PLAN statement
  ORA-22905: cannot access rows from a non-nested table item
  
  parse error offset: 129
  
  Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  asynch descriptor resize                        1        0.00          0.00
  ********************************************************************************
  </code></pre>