Geek asks and answers

Oracle Explain Query Plan Using Collection Using Table Function

How to create a Plan in Oracle for a query using some Collection in exists clause using a table function

eg.

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

Type created.

CREATE TYPE TYP_EMP_COLL AS TABLE OF TYP_EMP;
/

Type created.

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
/

View created

CREATE TYPE TYP_DEPT AS OBJECT(
DEPTNO NUMBER(2),
DNAME  VARCHAR2(14),
LOC    VARCHAR2(13));
/

Type created

CREATE TYPE TYP_DEPT_COLL AS TABLE OF TYP_DEPT;
/

Type created

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

TKPROF OUTPUT OF TRACE FILE

********************************************************************************

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
********************************************************************************
+3
source share
1 answer

There are many different ways to get an explanation plan. This is not just an exhaustive list. I think there are important differences between the methods, and it's worth having each of them in your toolbox.

1 - Tracking procedure call.

, SQL PL/SQL. , PL/SQL SQL , .

, , , , . , , () .

2 - SQL

, :

SELECT VALUE(VD) 
FROM
VW_DEPT VD WHERE EXISTS (SELECT 1 FROM TABLE(typ_emp_coll()  ) VV WHERE VD.DEPTNO = VV.DEPTNO);

:

SELECT VALUE(VD) 
FROM
VW_DEPT VD WHERE EXISTS (SELECT 1 FROM TABLE(typ_emp_coll(typ_emp(null,null,null,null,null,null,null,null))  ) VV WHERE VD.DEPTNO = VV.DEPTNO);

SQL. . , , , . Oracle .

3 - SQL_ID .

SQL_ID V$SQL. 1 , .

select * from v$sql where upper(sql_text) like '%SELECT 1 FROM TABLE(%';

sql_id , :

select * from table(dbms_xplan.display_cursor(sql_id => '7c02yjs9q5kqr'));

SQL_ID  7c02yjs9q5kqr, child number 0
-------------------------------------
SELECT VALUE(VD) FROM VW_DEPT VD WHERE EXISTS (SELECT 1 FROM TABLE(:B1 
) VV WHERE VD.DEPTNO = VV.DEPTNO)

Plan hash value: 2616009478

-------------------------------------------------------------------------------------------
| Id  | Operation                          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |      |       |       |    32 (100)|          |
|*  1 |  HASH JOIN SEMI                    |      |     1 |    32 |    32   (4)| 00:00:01 |
|   2 |   TABLE ACCESS FULL                | DEPT |     1 |    30 |     2   (0)| 00:00:01 |
|   3 |   COLLECTION ITERATOR PICKLER FETCH|      |  8168 | 16336 |    29   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("DEPTNO"=SYS_OP_ATG(VALUE(KOKBF$),8,9,2))

: /*+ gather_plan_statistics */, v$sql_monitor, v$sql_plan, dbms_sqltune.report_sql_monitor(sql_id => '7c02yjs9q5kqr', type => 'active') .. . report_sql_monitor, . ,   .

4 - .

create or replace package test_pkg is
    v_emp typ_emp_coll;
    function get_v_emp return typ_emp_coll;
end;
/

create or replace package body test_pkg is
    function get_v_emp return typ_emp_coll is
    begin
        return v_emp;
    end;
end;
/

SQL . :

select * from table(test_pkg.get_v_emp);

?

, , , , . , , 8168 .

, , , , .

, :

select /*+ dynamic_sampling(5) */ * from table(test_pkg.get_v_emp)
+3

More articles:


All Articles