Re: table type, table of number is slow when selected from

On 24 Mar 2005 14:23:28 -0800, jeffchirco_at_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 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;

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