Bdtjtk

I have plsql function. And this function has query like as below:

select  colum_name 

from table_name 

where  filter_coloumn_name in ( select filter_coloumn_name from table_name_2); 

My function is used by other query to return value from query. This query which use this function take more time. Even the site (front screen) down. Because table_name_2 has three million record. So that i must use some performance tuning method in my function. I change my function query like as below:

cursor my_cursor IS  

  select filter_coloumn_name from table_name_2;



TYPE cursor_array_type IS TABLE OF my_cursor%ROWTYPE INDEX BY BINARY_INTEGER; 

m    cursor_array_type;



TYPE cursor_table_type IS TABLE OF VARCHAR2(20) INDEX BY BINARY_INTEGER;

cursor_table_object cursor_table_type; 



fetch_size NUMBER := 5000;

index_var number;



begin 

index_var := 1; 



open my_cursor; 

    loop

      FETCH my_cursor BULK COLLECT

        into m LIMIT fetch_size;

      exit when my_cursor %notfound; 



     for i in 1 .. m.count loop       

        cursor_table_object (index_var) := m(i).filter_coloumn_name;     

       index_var := index_var + 1;

     end loop;

    end loop;

  Close my_cursor; 



   select  colum_name 

    from table_name 

    where  filter_coloumn_name in (cursor_table_object);

select colum_name
from table_name
where filter_coloumn_name in (cursor_table_object);

Namely, i want fetch all values at one time, and then i want use this table object like as above. But i can't use table object with in condition expression in sql query.

I take PLS-00382: expression is of wrong type error.

I want to use this table object like as below:

select  colum_name 

from table_name 

where  filter_coloumn_name in ('bla', 'bla bla', 'bla bla bla');

Should i convert table object to the array ?

A suggestion, based on what you've said so far: how about using a table function? It makes it possible to use something like statement you wanted to use, i.e.

select colum_name from table_name where filter_coloumn_name in (cursor_table_object);

but which returns

PLS-00382: expression is of wrong type

Here's an example based on Scott's schema. Have a look:

SQL> create or replace function f_test

  2    return sys.odcinumberlist

  3  is

  4    -- Cursor's SELECT should contain your 3-million-rows table

  5    cursor cur_r is select deptno from dept where deptno < 30;

  6    l_ret sys.odcinumberlist;

  7  begin

  8    open cur_r;

  9    fetch cur_r bulk collect into l_ret;

 10    close cur_r;

 11    return l_ret;

 12  end;

 13  /



Function created.



SQL> -- How to use it?

SQL> select e.ename

  2  from emp e join table(f_test) x on x.column_value = e.deptno;



ENAME

----------

MILLER

KING

CLARK

ADAMS

SCOTT

FORD

JONES

SMITH



8 rows selected.



SQL>

You must use exists instead of in, also table_name_2 must have index over filter_coloumn_name field.
Using cursor only what you get is full access to a big table.