We help IT Professionals succeed at work.

ORACLE 10G PL/SQL Improve Union query performance

cookiejar
cookiejar asked
on
918 Views
Last Modified: 2013-12-18
I have a query where I do the following
Select records with the maximum expiration_date or completion_date
Select records that do not exists in the first subset

I  have a created a view and it takes approx 19 minutes to run the view.  The following example is a simple construct of the query

SELECT
   'ATTEND'
   table1.col
   table2.col
   table1.emp_name
   table2.title
FROM
    table1,
    table2  
WHERE
    table1.title_id = table2.title_id
     AND nvl(table1.expiration_date,table1.completion_date =
        (SELECT MAX(nvl(t1.expiration_date,t1.completion_date))
            FROM table1 t1
                 
            WHERE t1.title_id = table1.title_id
              AND t1.emp_id  = table1.emp_id)
UNION
SELECT
   'NOT ATTENDED'
   table1.col
   table3.col
FROM
   table1,
   table2
WHERE table2.title_id not in
             (SELECT
                table1.title_id
              FROM
                 table1 t1
                 table2 t2
             WHERE
               table1.title_id = table2.title_id
                AND t1.title_id = table1.title_id
                AND t1.emp_id   = table1.emp_id)                                    

Is there a better way to accomplish this task?
Comment
Watch Question

Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
Does this have anything to do with Crystal Reports?

mlmcc
SQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Bill PrewTest your restores, not your backups...
CERTIFIED EXPERT
Expert of the Year 2019
Top Expert 2016

Commented:
Where is table3 in the table3.col reference?

~bp

Author

Commented:
rrjegan17,
Performance improved significantly with the UNION ALL
Thanks,
Attached is the working query
UNIONALL-QUERY.txt
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019

Commented:
Please find attached the query slightly modified..

And this is where your query might suffer in performance now:

 AND        nvl(grp_lectures.expiration_date,grp_lectures.completion_date) =
               ( SELECT NVL( MAX (itlg.expiration_date), MAX(itlg.completion_date) )
                     FROM   grp_lectures itlg,
                            emp_lectures itl_l,
                            master_list_lecture_versions mtl_lv,
                            master_list_lectures mtl_l
                    WHERE   itlg.grp_lecture_id       = itl_l.grp_lecture_id
                            AND itlg.lecture_version_id = mtl_lv.lecture_version_id
                            AND mtl_lv.lecture_id       = mtl_l.lecture_id
                            AND itl_l.emp_id        = p.emp_id
                            AND mtl_l.lecture_id        = master_list_lectures.lecture_id
                            )

If possible try some alternate ways to achieve the desired result
Using NVL on both the sides causes huge compilations resulting in lesser performance.
If any one of the column is not nullable, then use it out.
SELECT      master_list_lectures.lecture_id,
            master_list_tasks_versions.name lecture_list,
            master_list_lecture_versions.title,
            DECODE (
               (nvl(grp_lectures.expiration_date,SYSDATE) - SYSDATE)
               - ABS (nvl(grp_lectures.expiration_date,SYSDATE) - SYSDATE),
               0,
               'CURRENT',
               'EXPIRED'
            ) status,
            lecture_types.name lecture_type,
            get_department (p.emp_id) department,
            get_name_job_title (p.emp_id) name_job_title,
            grp_lectures.completion_date,
            grp_lectures.expiration_date
            
              
FROM        grp_lectures,
            emp_lectures,
            emp_assignments pa,
            p_employees p,
            master_list_lecture_versions,
            lecture_types,
            master_list_lectures,
            master_list_tasks,
            master_list_tasks_versions,
            tasklist_types,            
            departments
            
WHERE       tasklist_types.name = 'CLASS LECTURES'
 AND        grp_lectures.grp_lecture_id                          = emp_lectures.grp_lecture_id
 AND        grp_lectures.dept_id                                 = departments.dept_id
 AND        pa.emp_id                                            = emp_lectures.emp_id
 AND        pa.emp_id                                            = p.emp_id                                                and p.emp_id                                 =emp_lectures.emp_id                                 
 AND        pa.emp_assignment_id                                 = p.orders_to_assign_id
 AND        pa.dept_id                                           = get_parent_dept_id(department)
 AND        master_list_lecture_versions.lecture_version_id      = grp_lectures.lecture_version_id
 AND        master_list_lecture_versions.mdm_lecture_type_id     = lecture_types.mdm_lecture_type_id
 AND        master_list_lecture_versions.lecture_version_id      = master_list_lectures.online_version_id
 AND        master_list_lectures.lecture_id                      = master_list_lecture_versions.lecture_id
 AND        master_list_tasks.task_target_id                     = master_list_lectures.lecture_id
 AND        master_list_tasks_versions.tasklist_type_id          = tasklist_types.tasklist_type_id
 AND        master_list_tasks_versions.tasklist_version_id       = master_list_tasks.tasklist_version_id
 AND        nvl(grp_lectures.expiration_date,grp_lectures.completion_date) =
               ( SELECT NVL( MAX (itlg.expiration_date), MAX(itlg.completion_date) )
                     FROM   grp_lectures itlg,
                            emp_lectures itl_l,
                            master_list_lecture_versions mtl_lv,
                            master_list_lectures mtl_l
                    WHERE   itlg.grp_lecture_id       = itl_l.grp_lecture_id
                            AND itlg.lecture_version_id = mtl_lv.lecture_version_id
                            AND mtl_lv.lecture_id       = mtl_l.lecture_id
                            AND itl_l.emp_id        = p.emp_id
                            AND mtl_l.lecture_id        = master_list_lectures.lecture_id
                            ) 
            
UNION ALL
SELECT    master_list_lectures.lecture_id,
          master_list_tasks_versions.name lecture_list,
          master_list_lecture_versions.title,
         'NOT ATTENDED ' status,
          lecture_types.name lecture_type,
          get_department (p.emp_id) department,
          get_name_job_title (p.emp_id) name_job_title,
          NULL completion_date,
          NULL expiration_date
     
     FROM   master_list_tasks_versions,
            master_list_tasks,
            master_list_lectures,
            master_list_lecture_versions,
            lecture_types,
            tasklist_types,
            personal_departments_vw p
    WHERE   tasklist_types.name = 'CLASS LECTURES'
      AND   master_list_tasks_versions.tasklist_type_id                = tasklist_types.tasklist_type_id
      AND   master_list_tasks_versions.tasklist_version_id             = master_list_tasks.tasklist_version_id
      AND   master_list_tasks.task_target_id                           = master_list_lectures.lecture_id
      AND   master_list_lecture_versions.lecture_version_id            = master_list_lectures.online_version_id
      AND   master_list_lectures.lecture_id                            = master_list_lecture_versions.lecture_id
      AND   master_list_lecture_versions.mdm_lecture_type_id           = lecture_types.mdm_lecture_type_id
      AND   NOT EXISTS
            (SELECT  mtl_lect.lecture_id                               
                 FROM   grp_lectures,
                        emp_lectures,
                        emp_assignments pa,
                        p_employees p1,
                        master_list_lecture_versions,
                        lecture_types,
                        master_list_lectures mtl_lect,
                        master_list_tasks,
                        master_list_tasks_versions,
                        tasklist_types,            
                        departments
                WHERE   grp_lectures.grp_lecture_id                          = emp_lectures.grp_lecture_id
                  AND   grp_lectures.dept_id                                 = departments.dept_id
                  AND   pa.emp_id                                            = emp_lectures.emp_id-- lecture per_id
                  AND   pa.emp_id                                            = p1.emp_id                                               
                  AND   p1.emp_id                                            = emp_lectures.emp_id                                 
                  AND   pa.emp_assignment_id                                 = p1.orders_to_assign_id
                  AND   pa.dept_id                                           = get_parent_dept_id(department)
                  AND   master_list_lecture_versions.lecture_version_id      = grp_lectures.lecture_version_id
                  AND   master_list_lecture_versions.mdm_lecture_type_id     = lecture_types.mdm_lecture_type_id
                  AND   master_list_lecture_versions.lecture_version_id      = master_list_lectures.online_version_id
                  AND   master_list_lectures.lecture_id                      = master_list_lecture_versions.lecture_id
                  AND   master_list_tasks.task_target_id                     = master_list_lectures.lecture_id
                  AND   master_list_tasks_versions.tasklist_type_id          = tasklist_types.tasklist_type_id
                  AND   master_list_tasks_versions.tasklist_version_id       = master_list_tasks.tasklist_version_id
                  AND   p1.emp_id                                            =  p.emp_id
                  AND   mtl_lect.lecture_id                                  = master_list_lectures.lecture_id
            )             

Open in new window

Author

Commented:
rrjegan17,

You stated, Please find attached the query slightly modified..
Is the attached query in the data window?  Which lines where modified?
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019

Commented:
>> Is the attached query in the data window?  Which lines where modified?

Yes..
I changed your NOT IN condition in second part of your query using NOT EXISTS for some more better performance.
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.