Reputation: 37
I've an existing view as below.
CREATE OR REPLACE FORCE EDITIONABLE VIEW "VW_EMP_DATA"
("EMP_ID", "STATUS", "ROLE", "LAST_ROLE", "DEPARTMENT", "JOINING_DATE", "DURATION", "HIRE_DATE") AS
SELECT emp.empid
,NVL(ei.status, 'new') AS STATUS
,emp.role
,ei.last_role
,emp.department AS dept
,promotiondate AS promotion_date
,SYSDATE - promotiondate AS duration
,ei.hire_date
FROM (
employee emp outer apply (select ed.promotiondate FROM emp_details ed
where ed.empid=emp.empid and ed.promotiondate is not null
order by ed.promotiondate asc
fetch next 1 rows only )
)
LEFT JOIN emp_info ei ON emp.empid = ei.empid
WHERE emp.empid IN (
SELECT eh.empid
FROM emp_history eh WHERE eh.STATUS not in ('TRAINEE', 'TERMINATED')
)
AND emp.name IS NOT NULL
AND ((emp.role != 'PROBATION') OR (emp.role_type_settlement_bank = 'PROBATION' AND hire_date > sysdate + 30))hgewq
Now I need to override this view with some employee ids which should be always present no matter they match the criteria or not.
To make it more dynamic, i've added the static employee ids(String, comma-separated) in my static_data
table and trying to pull those from it in this view so that i cant change that static table value in some point and refresh the view to reflect the new ids.
static_code | static_value
EMP_OVERRIDE | '1001, 1002,1003,1004'
My new View query is like the one below
CREATE OR REPLACE FORCE EDITIONABLE VIEW "VW_EMP_DATA"
("EMP_ID", "STATUS", "ROLE", "LAST_ROLE", "DEPARTMENT", "JOINING_DATE", "DURATION", "HIRE_DATE") AS
SELECT emp.empid
,NVL(ei.status, 'new') AS STATUS
,emp.role
,ei.last_role
,emp.department AS dept
,promotiondate AS promotion_date
,SYSDATE - promotiondate AS duration
,ei.hire_date
FROM (
employee emp outer apply (select ed.promotiondate FROM emp_details ed
where ed.empid=emp.empid and ed.promotiondate is not null
order by ed.promotiondate asc
fetch next 1 rows only )
)
LEFT JOIN emp_info ei ON emp.empid = ei.empid
WHERE emp.empid IN (
SELECT eh.empid
FROM emp_history eh WHERE eh.STATUS not in ('TRAINEE', 'TERMINATED')
)
AND emp.name IS NOT NULL
AND ((emp.role != 'PROBATION') OR (emp.role = 'PROBATION' AND hire_date > sysdate + 30))
OR emp.empid in (select to_number(ids) from (
select
distinct regexp_substr(static_value,'[^,]+', 1, level) as ids
from static_data where static_code='EMP_OVERRIDE'
connect by regexp_substr(static_value, '[^,]+', 1, level) is not null));
The problem with this is it is making my view too slow. The below query use to take 2-3secs is now taking more than 30sec to return the result.
select count(*) from VW_EMP_DATA where ROLE='MANAGER';
Can someone please tell me steps to optimize it for better performance. Thanks
Upvotes: 0
Views: 89
Reputation: 50077
Don't use delimited strings in your static data, as the CONNECT BY can be very inefficient.
Performance-wise you'll be better off creating several entries in STATIC_DATA to hold one value per row and then joining it in:
CREATE OR REPLACE FORCE EDITIONABLE VIEW "VW_EMP_DATA"
("EMP_ID", "STATUS", "ROLE", "LAST_ROLE", "DEPARTMENT", "JOINING_DATE", "DURATION", "HIRE_DATE") AS
SELECT emp.empid
,NVL(ei.status, 'new') AS STATUS
,emp.role
,ei.last_role
,emp.department AS dept
,promotiondate AS promotion_date
,SYSDATE - promotiondate AS duration
,ei.hire_date
FROM (
employee emp outer apply (select ed.promotiondate FROM emp_details ed
where ed.empid=emp.empid and ed.promotiondate is not null
order by ed.promotiondate asc
fetch next 1 rows only )
)
LEFT JOIN emp_info ei ON emp.empid = ei.empid
WHERE emp.empid IN (
SELECT eh.empid
FROM emp_history eh WHERE eh.STATUS not in ('TRAINEE', 'TERMINATED')
)
AND emp.name IS NOT NULL
AND ((emp.role != 'PROBATION') OR (emp.role = 'PROBATION' AND hire_date > sysdate + 30))
OR emp.empid in (select to_number(static_value)
from static_data
where static_code='EMP_OVERRIDE');
If STATIC_DATA.STATIC_CODE is unique then I suggest creating serialized entries such as:
STATIC_CODE STATIC_VALUE
EMP_OVERRIDE 1 1001
EMP_OVERRIDE 2 1002
EMP_OVERRIDE 3 1003
EMP_OVERRIDE 4 1004
and changing the last line of the view query to
where static_code LIKE 'EMP_OVERRIDE%');
Upvotes: 3