| Tune a select query which contains a pl/sql table type [message #387091] |
Tue, 17 February 2009 21:48  |
mnair
Messages: 5
Registered: January 2009
|
Junior Member |
|
|
Hi,
I have created a table type t_ref as
CREATE OR REPLACE TYPE "T_REF" IS TABLE OF VARCHAR2(35);
Now I have a procedure which uses this table type...
PROCEDURE c4_test_prc(iv_bgln IN c4_edi_tb.b_gln%TYPE,
iv_docreftype IN c4_edi_tb.doc_type_name%TYPE,
it_ref_num IN t_ref,
ov_error OUT VARCHAR2) AS
n_count NUMBER(3) := 0;
begin
SELECT COUNT(tdoc.column_value)
INTO n_count
FROM c4_edi_tb edi,
c4_gln_tb gln,
(SELECT column_value
FROM TABLE(CAST(it_ref_num AS
t_ref))) tdoc
WHERE edi.doc_num = tdoc.column_value
AND edi.b_gln = gln.n_number
AND edi.b_gln = iv_bgln
AND edi.doc_type_name = iv_docreftype;
....
....
end;
I need to tune the select query in the procedure ...How can I get the explain plan for this query, as it is using a table type?
Also if I have to execute just this query and see how much time it is taking, how can I do that? I can give the values for the variables iv_bgln and iv_docreftype. But how can I give the values for it_ref_num , if it has the values say '123455' and '23456'?
|
|
|
|
| Re: Tune a select query which contains a pl/sql table type [message #387095 is a reply to message #387091] |
Tue, 17 February 2009 22:09  |
flyboy
Messages: 1903
Registered: November 2006
|
Senior Member |
|
|
| mnair wrote on Wed, 18 February 2009 04:48 | I need to tune the select query in the procedure ...How can I get the explain plan for this query, as it is using a table type?
|
Possible by using SQL Trace + TKProf.
| mnair wrote on Wed, 18 February 2009 04:48 | Also if I have to execute just this query and see how much time it is taking, how can I do that?
|
Also possible to obtain by the above way.
| mnair wrote on Wed, 18 February 2009 04:48 | I can give the values for the variables iv_bgln and iv_docreftype. But how can I give the values for it_ref_num , if it has the values say '123455' and '23456'?
|
Time to consult PL/SQL User's Guide and Reference, available with other Oracle documentation e.g. online on http://tahiti.oracle.com/.
Have a look at Chapter 12 Using PL/SQL With Object Types.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
