CODEWITHSUNDEEP
sqloracle-databaseperformancequery-optimization
jasmeet
jasmeet

Reputation: 37

Optimizing SQL Query for a View in Oracle

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

Answers (1)

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

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

Related Questions