doing hints on view

From: Mr. X. <no_spam_please_at_nospam_please.com>
Date: Wed, 6 Feb 2008 23:31:07 +0200
Message-ID: <fod8s2$6kf$1@news2.netvision.net.il>

Hello,
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.

i.e :

the view is :

---

select
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 (i.e :
  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 by 

IX_NAME
   res varchar2(100);
   begin

      res:= null;
      open c;
      fetch c into res;
      close c;
      return res;

   end;
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