doing hints on view
Date: Wed, 6 Feb 2008 23:31:07 +0200
I have created a view, that most of its column are functions.
I know what exactly are the tables inside that function, but when I put all together, there is a problem :
I don't know how to put hints on the query, so I shall use a specific index.
the view is :
t.column_1, t.column_2, my_package.my_func(t.column_1, '1'), my_package.my_func(t.column_1, '2') ...
from my_table t
each column : my_package.my_func is calling my_table2 t2
package body my_package ...
function my_func(my_column in varchar2, const_val in varchar2) return varchar2 is
cursor c is
select my_value from my_table2 t2 where name = my_column || ' - ' || const_val; -- name is indexed byIX_NAME
res:= null; open c; fetch c into res; close c; return res;
If I could do hints on the main query :
/*+ index (t2 IX_NAME) */
... it would be great.
what is now, that there is always a full table scan, and materialized view is unconsiderable.
Can I do something like that ?
Is there any other solution for that ?
Thanks :) Received on Wed Feb 06 2008 - 15:31:07 CST