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

cannot find any reason to use user defined functions at all in this
case. You can do all that in just one SQL statement, it would be much
faster and better understandable.

Gints Plivna
http://www.gplivna.eu

cannot find any reason to use user defined functions at all in this
case. You can do all that in just one SQL statement, it would be much
faster and better understandable.

Gints Plivna
http://www.gplivna.eu

cannot find any reason to use user defined functions at all in this
case. You can do all that in just one SQL statement, it would be much
faster and better understandable.

Gints Plivna
http://www.gplivna.eu

cannot find any reason to use user defined functions at all in this
case. You can do all that in just one SQL statement, it would be much
faster and better understandable.

Gints Plivna
http://www.gplivna.eu

How can I do instead of using package in my example, use a standard sql.

select
t.column_1, t.column_2,
t2.name , ...
where t2.name = t.
my_package.my_func(t.column_1, '2') ...
from my_table t, my_table2 t2
where t2.name = t.name || ' - 1' (*)

the (*) doesn't work.
What should I do instead ?

Thanks

How can I do instead of using package in my example, use a standard sql.

select
t.column_1, t.column_2,
t2.name , ...
where t2.name = t.
my_package.my_func(t.column_1, '2') ...
from my_table t, my_table2 t2
where t2.name = t.name || ' - 1' (*)

the (*) doesn't work.
What should I do instead ?

Thanks

How can I do instead of using package in my example, use a standard sql.

select
t.column_1, t.column_2,
t2.name , ...
where t2.name = t.
my_package.my_func(t.column_1, '2') ...
from my_table t, my_table2 t2
where t2.name = t.name || ' - 1' (*)

the (*) doesn't work.
What should I do instead ?

Thanks

How can I do instead of using package in my example, use a standard sql.

select
t.column_1, t.column_2,
t2.name , ...
where t2.name = t.
my_package.my_func(t.column_1, '2') ...
from my_table t, my_table2 t2
where t2.name = t.name || ' - 1' (*)

the (*) doesn't work.
What should I do instead ?

Thanks

Eitan M wrote:
You should never use "*" for anything in production code so rather than
asking us how to make bad code work how about describing the business
problem you are trying to address.
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org