Variable with many values [message #386951] |
Tue, 17 February 2009 06:30  |
ravi214u
Messages: 153 Registered: February 2008 Location: CANADA
|
Senior Member |
|
|
hi,
my requirement is to store many values in one variable in a procedure and pass that value to another query e.g
select v_src from dim_src where flag='T'
output is
10101
10102
10999
my other query is
select * from fct_tab1,fct_tab2
where fct_tab1.col1=fct_tab2.col1
and fct_tab1.col2 in (select v_src from dim_src where flag='T')
this query take too much time bec of sub query with select statement.Can we reduce the time by storing all the values in one variable and pass it as parameter.
thanks
[Updated on: Tue, 17 February 2009 06:36] by Moderator Report message to a moderator
|
|
|
|
|
|
|
|
|
|
Re: Variable with many values [message #387029 is a reply to message #386951] |
Tue, 17 February 2009 11:27   |
ravi214u
Messages: 153 Registered: February 2008 Location: CANADA
|
Senior Member |
|
|
this is what i have done.
create type t_var_ty is table of varchar2(10);
select a.ename from fct_tab1 a,fct_tab2 b
where a.v_src=b.v_src
and a.v_src in
(select v_src from dim_src c where flag in (select column_value from table(t_var_ty('T'))))
|
|
|
|
|
Re: Variable with many values [message #387033 is a reply to message #386951] |
Tue, 17 February 2009 11:42   |
cookiemonster
Messages: 13963 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Me neither.
Think you wanted to populate the array with the results of:
select v_src from dim_src where flag='T'
Then do something like:
select * from fct_tab1,fct_tab2
where fct_tab1.col1=fct_tab2.col1
and fct_tab1.col2 in select column_value from table(t_var_ty('T'))
But I don't know if that'd speed it up either.
What you really want to do is follow JRowbottom's advise and find out why the original query is so slow - it's not like it's complicated.
|
|
|
Re: Variable with many values [message #387165 is a reply to message #387032] |
Wed, 18 February 2009 01:56  |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
I'd be very suprised if selecting from a collection improved your performance at all. Using TABLE(...) in a query gives you much more flexibility, but in my experience you pay for that flexibility with performance.
Do this, and post the results:EXPLAIN PLAN FOR
select * from fct_tab1,fct_tab2
where fct_tab1.col1=fct_tab2.col1
and fct_tab1.col2 in (select v_src from dim_src where flag='T');
SELECT *
FROM table(dbms_xplan.display);
|
|
|