To make long story short we have this function ... it is THE FUNCTION.
It's called 'get_user_id'
What it does is getting info from context about apex login and then selecting user_id from our custom auth table.
This function has like 5yrs or even more because i can see APEX_030200 user in db user list , and our DBA said that he created it back when he was evaling Apex for company use.
create or replace function get_user_id return number as
client_info VARCHAR2(1000);
rezult number;
x_user varchar2(512);
begin
-- return 1;
DBMS_APPLICATION_INFO.READ_CLIENT_INFO ( client_info );
x_user := substr(client_info,INSTR(client_info, ':' ,3,1)+1) ;
select user_id
into rezult
from USERS
where upper(login) = upper(x_user) ;
RETURN rezult ;
EXCEPTION
WHEN OTHERS THEN
RETURN -1;
end;
So yea ... I tried running this query :
select * from (0,5 x 10^6 rows ) where user_id = get_user_id;
and our server took like 2 minutes to return no rows
I started reading about optimising functions in sql etc. and I worked out that I can get it down sub 0.1 sec just by :
select * from (0,5 x 10^6 rows ) where user_id = (select get_user_id from dual );
and then I started to analyze what's actually inside the function , face palmed , and just did
select * from (0,5 x 10^6 rows )
where user_id in (
select user_id from users
where upper(login) = nvl(upper(:APP_USER),'SYSTEM')
)
Query time: 0.03 sec.
Problem solved ... but
Here goes my question
Is there any decent way to change all instances of sql in Apex raports/item sources and db views from
get_user_id
to
(
select user_id from users
where upper(login) = nvl(upper(:APP_USER),'SYSTEM')
)
Edit :
I`M thinking about updating apex internal tables any1 has any experience with it ?
