Home » SQL & PL/SQL » SQL & PL/SQL » Variable with many values (oracle 10g)
Variable with many values [message #386951] Tue, 17 February 2009 06:30 Go to next message
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 #386954 is a reply to message #386951] Tue, 17 February 2009 06:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Search for "varying in-list"

Regards
Michel
Re: Variable with many values [message #386963 is a reply to message #386951] Tue, 17 February 2009 07:12 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You might want to investigate why the query performs badly too.

Can you post the explain plan for the two version of the query?
Re: Variable with many values [message #387008 is a reply to message #386951] Tue, 17 February 2009 10:12 Go to previous messageGo to next message
ravi214u
Messages: 153
Registered: February 2008
Location: CANADA
Senior Member
thanks for replying.

Can you please give a simple eg of my requirement it will be a great help.

thanks
Re: Variable with many values [message #387012 is a reply to message #387008] Tue, 17 February 2009 10:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Tue, 17 February 2009 13:37
Search for "varying in-list"

Regards
Michel

Re: Variable with many values [message #387024 is a reply to message #386951] Tue, 17 February 2009 10:53 Go to previous messageGo to next message
ravi214u
Messages: 153
Registered: February 2008
Location: CANADA
Senior Member
thanks for replying michel.

"varying in list" when we now what is the values.But when i have to pic dynamically than how can we process that.

Re: Variable with many values [message #387025 is a reply to message #386951] Tue, 17 February 2009 10:59 Go to previous messageGo to next message
BlackSwan
Messages: 25037
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you.
Re: Variable with many values [message #387027 is a reply to message #387024] Tue, 17 February 2009 11:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ravi214u wrote on Tue, 17 February 2009 17:53
thanks for replying michel.

"varying in list" when we now what is the values.But when i have to pic dynamically than how can we process that.

If it is varying then we don't know the list otherwise it is static.

Regards
Michel

Re: Variable with many values [message #387029 is a reply to message #386951] Tue, 17 February 2009 11:27 Go to previous messageGo to next message
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 #387030 is a reply to message #386951] Tue, 17 February 2009 11:30 Go to previous messageGo to next message
cookiemonster
Messages: 12404
Registered: September 2008
Location: Rainy Manchester
Senior Member
And did it work?
Re: Variable with many values [message #387032 is a reply to message #386951] Tue, 17 February 2009 11:34 Go to previous messageGo to next message
ravi214u
Messages: 153
Registered: February 2008
Location: CANADA
Senior Member
yes it worked but don't now how it will increase performance.
Re: Variable with many values [message #387033 is a reply to message #386951] Tue, 17 February 2009 11:42 Go to previous messageGo to next message
cookiemonster
Messages: 12404
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 Go to previous message
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);
Previous Topic: Oracle 10g, Win XP
Next Topic: packages
Goto Forum:
  


Current Time: Mon Dec 05 10:37:59 CST 2016

Total time taken to generate the page: 0.09419 seconds