table type, table of number is slow when selected from

[Guest]

I have created a table type called num_tab as table of numbers.
(create type num_tab as table of number)
I use this table type in a function to temporary store a list of
numbers which is used in a where clause of a separet query. It works
fine, but it is slow. Can the type be indexed? Is there something
better to use? Thanks.
Here is how I use it:
select dbctr bulk collect into v_numtab
from custom_list_d
where dbkey = dbctr_passedin;

select sum(dbsand)
into v_count
from usage_summary
where dbtype = 's'
and dbctr IN (select * from table(v_numtab))
and dbweek > 0
and dbyearperweek >= v_start_year_period
and dbyearperweek <= v_end_year_period;

[Sybrand Bakker]

On 24 Mar 2005 14:23:28 -0800, jeffchirco@gmail.com wrote:
I have created a table type called num_tab as table of numbers.(create type num_tab as table of number)I use this table type in a function to temporary store a list ofnumbers which is used in a where clause of a separet query. It worksfine, but it is slow. Can the type be indexed? Is there somethingbetter to use? Thanks.Here is how I use it: select dbctr bulk collect into v_numtab from custom_list_d where dbkey = dbctr_passedin; select sum(dbsand) into v_count from usage_summary where dbtype = 's' and dbctr IN (select * from table(v_numtab)) and dbweek > 0 and dbyearperweek >= v_start_year_period and dbyearperweek <= v_end_year_period;

Any reason why you can't use an ordinary subquery. Right now you are
splitting up one statement into two! How would that be efficient?
Rather tune the original statement!


--
Sybrand Bakker, Senior Oracle DBA