在以前文章中我介绍了如何在JAVA程序中使用Struct一次传入多条数据给Oracle的存储过程
众所周知,SQL语句到了最后需要做Explain Plan来保证没有影响性能的语句。
对Struct和Array做Explain Plan的方法是:
1.单独分析:
SELECT * FROM TABLE (DEPT_ARRAY(DEPARTMENT_TYPE(
1 , -- DNO NUMBER (10),
' NAME ' , -- NAME VARCHAR2 (50),
' LOCATION ' -- LOCATION VARCHAR2 (50)
))) T;
SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY);
结果:
PLAN_TABLE_OUTPUT
1 Plan hash value: 1748000095
2
3 ----------------------------------------------------------------------------------------------
4 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
5 ----------------------------------------------------------------------------------------------
6 | 0 | SELECT STATEMENT | | 8168 | 16336 | 24 (0)| 00:00:01 |
7 | 1 | COLLECTION ITERATOR CONSTRUCTOR FETCH| | | | | |
8 ----------------------------------------------------------------------------------------------
2.联合其他表:
SELECT * FROM TABLE (DEPT_ARRAY(DEPARTMENT_TYPE(
1 , -- DNO NUMBER (10),
' NAME ' , -- NAME VARCHAR2 (50),
' LOCATION ' -- LOCATION VARCHAR2 (50)
))) T
JOIN TABLE2 T2 ON T.DNO = T2.DNO;
SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY);