1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Pls help - ineffective select union

Discussion in 'SQL PL/SQL' started by james shallow, Apr 19, 2017.

  1. james shallow

    james shallow Active Member

    Messages:
    25
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    uk
    Please, I need a better way to rewrite this unusual select UNION on a view which I am working on to improve because the performance is slow and it is confusing. Thanks

    Code (SQL):
       CREATE OR REPLACE FORCE VIEW "VIEW_C" ("a", "b", "c", "d", "e", "f", "g", ) AS SELECT               a,
              b,
              c,
              d,
              e,
              f
             '1'
         FROM (SELECT * FROM view_a
               MINUS
               SELECT * FROM view_b)
         UNION
         SELECT  a,
              b,
              c,
              d,
              e,
              f
             '0'
         FROM view_b;
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    666
    Likes Received:
    136
    Trophy Points:
    810
    Location:
    Russian Federation
    What is the view_a and view_b and what is business-key/pk in these views?

    I am suppose, what you to need to optimize logic this of views....

    May be help full : MINUS/INTERSECT/UNION
    Simple use LEFT JOIN

    Example :
    Code (Text):

    -- with MINUS
    SELECT COLUMN_VALUE FROM TABLE(sys.odcinumberlist(2,3,4)) a
    MINUS
    SELECT COLUMN_VALUE  FROM TABLE(sys.odcinumberlist(3,5)) b;

    -- with lef join
    SELECT a.COLUMN_VALUE FROM TABLE(sys.odcinumberlist(2,3,4)) a
    LEFT JOIN TABLE(sys.odcinumberlist(3,5)) b ON  b.column_value(+) = a.column_value                    
    WHERE  b.column_value IS NULL;

     
     
    Last edited: Apr 20, 2017
  3. james shallow

    james shallow Active Member

    Messages:
    25
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    uk
    Thanks Sergey for your suggestion. I think LEFT OUTER JOIN will help in this situation but these views have no Primary nor Foreign keys. So do I have to join all these columns together because the both have the same number of columns as well
     
  4. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    666
    Likes Received:
    136
    Trophy Points:
    810
    Location:
    Russian Federation
    I'll clarify my question : can you provide here the text of the view .
    I was interested in the keys on the table from view_a,view_b.
    Perhaps, the best solution would be to rewrite the view view_a & view_b as a single view.
     
  5. james shallow

    james shallow Active Member

    Messages:
    25
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    uk
    Thanks, Sergey. below is the text of view_a, view_b and view_c . Thanks for your support.
    Code (SQL):
    CREATE OR REPLACE FORCE VIEW "VIEW_A" ("PERSON_CODE", "SURNAME", "KNOWN_AS", "INITIALS", "TITLE", "COMMON_USERNAME", "EMAIL_ADDRESS", "JOB_TITLE", "ORGANISATION_CODE", "ORGANISATION_NAME", "JOB_FAMILY", "PILOT") AS
      SELECT DISTINCT
              ppl.person_code PERSON_CODE,
              ppl.surname SURNAME,
              NVL (ppl.known_as, ppl.forename) KNOWN_AS,
              ppl.initials INITIALS,
              BRSF_REFERENCE_VALUE ('TITLE', ppl.title) TITLE,
              ppl.common_username COMMON_USERNAME,
           CASE
              WHEN ppl.email_name IS NULL AND ppl.common_username = 'rbt1009'
              THEN
                 'doctor_on_call@barbeck.com'
              ELSE
                 ppl.email_name
           END
              AS EMAIL_ADDRESS,
              apps.job_title,
              orgs.organisation_code,
              orgs.organisation_name,
              apps.job_family,
              NVL (
                 (SELECT 'Y'
                    FROM DUAL
                   WHERE ppl.person_code IN (SELECT person_code
                                               FROM brs_staff_simitive_pilot)),
                 'N')
                 PILOT
         --
         FROM hes_people ppl,
              (  SELECT ppl.person_code PERSON_CODE,
                        LISTAGG (poh.organisation_code, '|')
                           WITHIN GROUP (ORDER BY poh.organisation_code)
                           organisation_code,
                        LISTAGG (org.full_name, '|')
                           WITHIN GROUP (ORDER BY org.full_name)
                           organisation_name
                   --
                   FROM hes_people ppl,
                        hes_periods_of_service pos,
                        hes_appointments app,
                        brs_post_org_histories poh,
                        hes_organisation_units org
                  --
                  WHERE     ppl.person_code = pos.per_person_code
                        AND TRUNC (SYSDATE) BETWEEN pos.start_date AND pos.end_date
                        AND pos.period_of_ser_code = app.pes_period_of_ser_code
                        AND TRUNC (SYSDATE) BETWEEN app.start_date AND app.end_date
                        AND app.pos_post_number = poh.post_number
                        AND TRUNC (SYSDATE) BETWEEN poh.start_date AND poh.end_date
                        AND poh.organisation_code = org.organisation_code
                        AND (   ppl.person_code IN (SELECT person_code
                                                      FROM brs_staff_myreview_inc)
                             OR (    BRSF_JOB_FAMILY (app.appointment_code) IN ('A',
                                                                                'O',
                                                                                'T')
                                 AND NVL (BRSF_JOB_PATHWAY (app.appointment_code),
                                          '0') != '2'
                                 AND pos.period_of_service_type IN ('BARBECK SERVICE',
                                                                    'PREVIOUS BARBECK SERVICE'))
                             OR (    BRSF_JOB_FAMILY (app.appointment_code) IN ('C',
                                                                                'R')
                                 AND pos.period_of_service_type IN ('BARBECK SERVICE',
                                                                    'PREVIOUS BARBECK SERVICE')))
               GROUP BY ppl.person_code) orgs,
              (  SELECT ppl.person_code,
                        LISTAGG (jth.job_title, '|')
                           WITHIN GROUP (ORDER BY jth.job_title)
                           job_title,
                        LISTAGG (BRSF_JOB_FAMILY (app.appointment_code), '|')
                           WITHIN GROUP (ORDER BY jth.job_title)
                           job_family
                   --
                   FROM hes_people ppl,
                        hes_periods_of_service pos,
                        brs_job_title_histories jth,
                        hes_appointments app
                  --
                  WHERE     ppl.person_code = pos.per_person_code
                        AND TRUNC (SYSDATE) BETWEEN pos.start_date AND pos.end_date
                        AND pos.period_of_ser_code = app.pes_period_of_ser_code
                        AND TRUNC (SYSDATE) BETWEEN app.start_date AND app.end_date
                        AND app.appointment_code = jth.appointment_code
                        AND TRUNC (SYSDATE) BETWEEN jth.start_date AND jth.end_date
                        AND (   ppl.person_code IN (SELECT person_code
                                                      FROM brs_staff_myreview_inc)
                             OR (    BRSF_JOB_FAMILY (app.appointment_code) IN ('A',
                                                                                'O',
                                                                                'T')
                                 AND NVL (BRSF_JOB_PATHWAY (app.appointment_code),
                                          '0') != '2'
                                 AND pos.period_of_service_type IN ('BARBECK SERVICE',
                                                                    'PREVIOUS BARBECK SERVICE'))
                             OR (    BRSF_JOB_FAMILY (app.appointment_code) IN ('C',
                                                                                'R')
                                 AND pos.period_of_service_type IN ('BARBECK SERVICE',
                                                                    'PREVIOUS BARBECK SERVICE')))
               GROUP BY ppl.person_code) apps
        WHERE     ppl.person_code = apps.person_code
              AND ppl.person_code = orgs.person_code
           AND (ppl.email_name IS NOT NULL OR ppl.common_username = 'hb14868')
              AND ppl.common_username IS NOT NULL
              AND ppl.person_code NOT IN (SELECT person_code
                                            FROM brs_staff_myreview_exc);
     

    -----------------------------
    Code (SQL):
    CREATE OR REPLACE FORCE VIEW "VIEW_B" ("PERSON_CODE", "SURNAME", "KNOWN_AS", "INITIALS", "TITLE", "COMMON_USERNAME", "EMAIL_ADDRESS", "JOB_TITLE", "ORGANISATION_CODE", "ORGANISATION_NAME", "JOB_FAMILY", "PILOT") AS
      SELECT DISTINCT
              ppl.person_code PERSON_CODE,
              ppl.surname SURNAME,
              NVL (ppl.known_as, ppl.forename) KNOWN_AS,
              ppl.initials INITIALS,
              BRSF_REFERENCE_VALUE ('TITLE', ppl.title) TITLE,
              ppl.common_username COMMON_USERNAME,
              ppl.email_name EMAIL_ADDRESS,
              apps.job_title,
              orgs.organisation_code,
              orgs.organisation_name,
              apps.job_family,
              NVL (
                 (SELECT 'Y'
                    FROM DUAL
                   WHERE ppl.person_code IN (SELECT person_code
                                               FROM brs_staff_simitive_pilot)),
                 'N')
                 PILOT
         --
         FROM hes_people ppl,
              (  SELECT ppl.person_code PERSON_CODE,
                        LISTAGG (poh.organisation_code, '|')
                           WITHIN GROUP (ORDER BY poh.organisation_code)
                           organisation_code,
                        LISTAGG (org.full_name, '|')
                           WITHIN GROUP (ORDER BY org.full_name)
                           organisation_name
                   --
                   FROM hes_people ppl,
                        hes_periods_of_service pos,
                        hes_appointments app,
                        hes_app_grade_histories g,
                        hes_grades r,
                        brs_post_org_histories poh,
                        brs_job_title_histories jth,
                        hes_organisation_units org
                  --
                  WHERE ppl.person_code = pos.per_person_code
                        AND TRUNC (SYSDATE) BETWEEN pos.start_date AND pos.end_date
                        AND pos.period_of_ser_code = app.pes_period_of_ser_code
                        AND TRUNC (SYSDATE) BETWEEN app.start_date AND app.end_date
                        AND app.pos_post_number = poh.post_number
                        AND TRUNC (SYSDATE) BETWEEN poh.start_date AND poh.end_date
                        AND app.appointment_code = jth.appointment_code
                        AND TRUNC (SYSDATE) BETWEEN jth.start_date AND jth.end_date
                        AND app.appointment_code = g.app_appointment_code
                        AND TRUNC (SYSDATE) BETWEEN g.effective_start_date
                                                AND g.effective_end_date
                        AND g.gra_grade_code = r.grade_code
                        AND poh.organisation_code = org.organisation_code
                        AND pos.period_of_service_type IN ('BARBECK SERVICE',
                                                           'PREVIOUS BARBECK SERVICE')
                        AND (   (    org.organisation_code IN ('LAHO', 'SITE')
                                 AND BRSF_JOB_FAMILY (app.appointment_code) = 'O'
                                 AND g.gra_grade_code IN (921, 922))
                             OR (    org.organisation_code IN (SELECT organisation_code
                                                                 FROM hes_organisation_units
                                                                WHERE body_position LIKE
                                                                         '%DRCO%')
                                 AND BRSF_JOB_FAMILY (app.appointment_code) = 'O'
                                 AND g.gra_grade_code IN (921, 922, 923))
                             OR (    org.organisation_code = 'BBUI'
                                 AND BRSF_JOB_FAMILY (app.appointment_code) = 'O'
                                 AND g.gra_grade_code IN (921,
                                                          922,
                                                          923,
                                                          924,
                                                          925,
                                                          926,
                                                          927,
                                                          928))
                             OR (    org.organisation_code IN ('SITG', 'FENW')
                                 AND g.gra_grade_code IN (921,
                                                          922,
                                                          923,
                                                          924)
                                 AND BRSF_JOB_FAMILY (app.appointment_code) IN ('O',
                                                                                'T')))
               GROUP BY ppl.person_code) orgs,
              (  SELECT ppl.person_code,
                        LISTAGG (jth.job_title, '|')
                           WITHIN GROUP (ORDER BY jth.job_title)
                           job_title,
                        LISTAGG (BRSF_JOB_FAMILY (app.appointment_code), '|')
                           WITHIN GROUP (ORDER BY jth.job_title)
                           job_family
                   --
                   FROM hes_people ppl,
                        hes_periods_of_service pos,
                        hes_appointments app,
                        hes_app_grade_histories g,
                        hes_grades r,
                        brs_post_org_histories poh,
                        brs_job_title_histories jth,
                        hes_organisation_units org
                  --
                  WHERE     ppl.person_code = pos.per_person_code
                        AND TRUNC (SYSDATE) BETWEEN pos.start_date AND pos.end_date
                        AND pos.period_of_ser_code = app.pes_period_of_ser_code
                        AND TRUNC (SYSDATE) BETWEEN app.start_date AND app.end_date
                        AND app.pos_post_number = poh.post_number
                        AND TRUNC (SYSDATE) BETWEEN poh.start_date AND poh.end_date
                        AND app.appointment_code = jth.appointment_code
                        AND TRUNC (SYSDATE) BETWEEN jth.start_date AND jth.end_date
                        AND app.appointment_code = g.app_appointment_code
                        AND TRUNC (SYSDATE) BETWEEN g.effective_start_date
                                                AND g.effective_end_date
                        AND g.gra_grade_code = r.grade_code
                        AND poh.organisation_code = org.organisation_code
                        AND (   (    org.organisation_code IN ('LAHO', 'SITE')
                                 AND BRSF_JOB_FAMILY (app.appointment_code) = 'O'
                                 AND g.gra_grade_code IN (921, 922))
                             OR (    org.organisation_code IN (SELECT organisation_code
                                                                 FROM hes_organisation_units
                                                                WHERE body_position LIKE
                                                                         '%DRCO%')
                                 AND BRSF_JOB_FAMILY (app.appointment_code) = 'O'
                                 AND g.gra_grade_code IN (921, 922, 923))
                             OR (    org.organisation_code = 'BBUI'
                                 AND BRSF_JOB_FAMILY (app.appointment_code) = 'O'
                                 AND g.gra_grade_code IN (921,
                                                          922,
                                                          923,
                                                          924,
                                                          925,
                                                          926,
                                                          927,
                                                          928))
                             OR (    org.organisation_code IN ('SITG', 'FENW')
                                 AND g.gra_grade_code IN (921,
                                                          922,
                                                          923,
                                                          924)
                                 AND BRSF_JOB_FAMILY (app.appointment_code) IN ('O',
                                                                                'T')))
               GROUP BY ppl.person_code) apps
        WHERE     ppl.person_code = apps.person_code
              AND ppl.person_code = orgs.person_code
              AND ppl.email_name IS NOT NULL
              AND ppl.common_username IS NOT NULL
              AND ppl.person_code NOT IN (SELECT person_code
                                            FROM brs_staff_myreview_exc);
    ------------------------------------
    Code (SQL):
    CREATE OR REPLACE FORCE VIEW "VIEW_C" ("PERSON_CODE", "SURNAME", "KNOWN_AS", "INITIALS", "TITLE", "COMMON_USERNAME", "EMAIL_ADDRESS", "JOB_TITLE", "ORGANISATION_CODE", "ORGANISATION_NAME", "PILOT", "JOB_FAMILY", "IN_USE") AS
      SELECT PERSON_CODE,
              SURNAME,
              KNOWN_AS,
              INITIALS,
              TITLE,
              COMMON_USERNAME,
              EMAIL_ADDRESS,
              JOB_TITLE,
              ORGANISATION_CODE,
              ORGANISATION_NAME,
              PILOT,
              JOB_FAMILY,
              '1' IN_USE
         FROM (SELECT * FROM view_a
               MINUS
               SELECT * FROM view_b)
       UNION
       SELECT PERSON_CODE,
              SURNAME,
              KNOWN_AS,
              INITIALS,
              TITLE,
              COMMON_USERNAME,
              EMAIL_ADDRESS,
              JOB_TITLE,
              ORGANISATION_CODE,
              ORGANISATION_NAME,
              PILOT,
              JOB_FAMILY,
              '0' IN_USE
         FROM view_b;
     
  6. james shallow

    james shallow Active Member

    Messages:
    25
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    uk
    Hi Sergey, I have posted the views, Please could you take a look when you get a chance. Thanks
     
    Last edited: Apr 24, 2017 at 10:02 AM
  7. james shallow

    james shallow Active Member

    Messages:
    25
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    uk
     
  8. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    666
    Likes Received:
    136
    Trophy Points:
    810
    Location:
    Russian Federation
    Hi,James.
    Perphaps today, as I will be have the opportunity , I will be study of these views and then, I write of recommendations/suggestions for improvement these views.
     
  9. james shallow

    james shallow Active Member

    Messages:
    25
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    uk
    Thanks Sergey, you are a saviour!
     
  10. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    666
    Likes Received:
    136
    Trophy Points:
    810
    Location:
    Russian Federation
    1) Replace NVL on COALESCE in these views where you use NVL with SELECT.

    2) replace this block
    Code (Text):

    NVL (
                 (SELECT 'Y'
                    FROM DUAL
                   WHERE ppl.person_code IN (SELECT person_code
                                               FROM brs_staff_simitive_pilot)),
                 'N')

     
    on
    Code (Text):

    (
    SELECT nvl2(max(1), 'Y','N') FROM brs_staff_simitive_pilot t  where t.person_code = ppl.person_code
    )

     
    3) "VIEW_B"
    At which step of the execution appear redundant data?You need to check the conditions of table joins, complement and optimize the logic of joins these tables.

    I am suppose ,may be optimization of this logic :
    for example ,this predicated is redundancy :
    Code (Text):
     AND BRSF_JOB_FAMILY (app.appointment_code) = 'O'
    and etc..


    Code (Text):

    AND (   (    org.organisation_code IN ('LAHO', 'SITE')
                                 AND BRSF_JOB_FAMILY (app.appointment_code) = 'O'
                                 AND g.gra_grade_code IN (921, 922))
                             OR (    org.organisation_code IN (SELECT organisation_code
                                                                 FROM hes_organisation_units
                                                                WHERE body_position LIKE
                                                                         '%DRCO%')
                                 AND BRSF_JOB_FAMILY (app.appointment_code) = 'O'
                                 AND g.gra_grade_code IN (921, 922, 923))
                             OR (    org.organisation_code = 'BBUI'
                                 AND BRSF_JOB_FAMILY (app.appointment_code) = 'O'
                                 AND g.gra_grade_code IN (921,
                                                          922,
                                                          923,
                                                          924,
                                                          925,
                                                          926,
                                                          927,
                                                          928))
                             OR (    org.organisation_code IN ('SITG', 'FENW')
                                 AND g.gra_grade_code IN (921,
                                                          922,
                                                          923,
                                                          924)
                                 AND BRSF_JOB_FAMILY (app.appointment_code) IN ('O',
                                                                                'T')
     
    These queries(view_a,view_b,view_c) should be rewritten in ansy-syntax with CTE.
    This will increase the readability of the query will allow to simplify(unify) the logic of joining the tables and possible to get rid of outdated conditions.


    For example :
    Code (Text):

    with step_apps as
    (  --should to rewrite on ansy-syntax
    SELECT ppl.person_code,
                        LISTAGG (jth.job_title, '|')
                           WITHIN GROUP (ORDER BY jth.job_title)
                           job_title,
                        LISTAGG (BRSF_JOB_FAMILY (app.appointment_code), '|')
                           WITHIN GROUP (ORDER BY jth.job_title)
                           job_family
                   --
                   FROM hes_people ppl,
                        hes_periods_of_service pos,
                        brs_job_title_histories jth,
                        hes_appointments app
                  --
                  WHERE     ppl.person_code = pos.per_person_code
                        AND TRUNC (SYSDATE) BETWEEN pos.start_date AND pos.end_date
                        AND pos.period_of_ser_code = app.pes_period_of_ser_code
                        AND TRUNC (SYSDATE) BETWEEN app.start_date AND app.end_date
                        AND app.appointment_code = jth.appointment_code
                        AND TRUNC (SYSDATE) BETWEEN jth.start_date AND jth.end_date
                        AND (   ppl.person_code IN (SELECT person_code
                                                      FROM brs_staff_myreview_inc)
                             OR (    BRSF_JOB_FAMILY (app.appointment_code) IN ('A',
                                                                                'O',
                                                                                'T')
                                 AND NVL (BRSF_JOB_PATHWAY (app.appointment_code),
                                          '0') != '2'
                                 AND pos.period_of_service_type IN ('BARBECK SERVICE',
                                                                    'PREVIOUS BARBECK SERVICE'))
                             OR (    BRSF_JOB_FAMILY (app.appointment_code) IN ('C',
                                                                                'R')
                                 AND pos.period_of_service_type IN ('BARBECK SERVICE',
                                                                    'PREVIOUS BARBECK SERVICE')))
               GROUP BY ppl.person_code),
    step_orgs as
    (-- should to rewrite on ansy-syntax  
    SELECT ppl.person_code PERSON_CODE,
                        LISTAGG (poh.organisation_code, '|')
                           WITHIN GROUP (ORDER BY poh.organisation_code)
                           organisation_code,
                        LISTAGG (org.full_name, '|')
                           WITHIN GROUP (ORDER BY org.full_name)
                           organisation_name
                   --
                   FROM hes_people ppl,
                        hes_periods_of_service pos,
                        hes_appointments app,
                        brs_post_org_histories poh,
                        hes_organisation_units org
                  --
                  WHERE     ppl.person_code = pos.per_person_code
                        AND TRUNC (SYSDATE) BETWEEN pos.start_date AND pos.end_date
                        AND pos.period_of_ser_code = app.pes_period_of_ser_code
                        AND TRUNC (SYSDATE) BETWEEN app.start_date AND app.end_date
                        AND app.pos_post_number = poh.post_number
                        AND TRUNC (SYSDATE) BETWEEN poh.start_date AND poh.end_date
                        AND poh.organisation_code = org.organisation_code
                        AND (   ppl.person_code IN (SELECT person_code
                                                      FROM brs_staff_myreview_inc)
                             OR (    BRSF_JOB_FAMILY (app.appointment_code) IN ('A',
                                                                                'O',
                                                                                'T')
                                 AND NVL (BRSF_JOB_PATHWAY (app.appointment_code),
                                          '0') != '2'
                                 AND pos.period_of_service_type IN ('BARBECK SERVICE',
                                                                    'PREVIOUS BARBECK SERVICE'))
                             OR (    BRSF_JOB_FAMILY (app.appointment_code) IN ('C',
                                                                                'R')
                                 AND pos.period_of_service_type IN ('BARBECK SERVICE',
                                                                    'PREVIOUS BARBECK SERVICE')))
               GROUP BY ppl.person_code)

    SELECT DISTINCT
              ppl.person_code PERSON_CODE,
              ppl.surname SURNAME,
              NVL (ppl.known_as, ppl.forename) KNOWN_AS,
              ppl.initials INITIALS,
              BRSF_REFERENCE_VALUE ('TITLE', ppl.title) TITLE,
              ppl.common_username COMMON_USERNAME,
           CASE
              WHEN ppl.email_name IS NULL AND ppl.common_username = 'rbt1009'
              THEN
                 'doctor_on_call@barbeck.com'
              ELSE
                 ppl.email_name
           END
              AS EMAIL_ADDRESS,
              apps.job_title,
              orgs.organisation_code,
              orgs.organisation_name,
              apps.job_family,
              NVL (
                 (SELECT 'Y'
                    FROM DUAL
                   WHERE ppl.person_code IN (SELECT person_code
                                               FROM brs_staff_simitive_pilot)),
                 'N')
                 PILOT
         --
         FROM hes_people ppl,
              join step_orgs orgs on orgs.person_code = ppl.ppl.person_code
              join  step_apps apps on  apps.person_code = ppl.person_code
        WHERE
              -- what this is ?
              (ppl.email_name IS NOT NULL OR ppl.common_username = 'hb14868')
              AND ppl.common_username IS NOT NULL
           
              AND ppl.person_code NOT IN (SELECT person_code  FROM brs_staff_myreview_exc) --how many rows in the  views brs_staff_myreview_exc
              ;
     
     
    Last edited: Apr 24, 2017 at 9:51 PM
  11. james shallow

    james shallow Active Member

    Messages:
    25
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    uk
    Thanks Sergey, you are correct about the CTE. I have started amending the code with the "WITH CLAUSE" and the performance is getting better.

    The code line
    Code (SQL):
    AND BRSF_JOB_FAMILY (app.appointment_code) = 'O'
    is not redundant, it is using a function BRSF_JOB_FAMILY, passing the appointment code where it is 'O'. Although I agree with you that this is not the best way to code.
    I will apply your changes and I hope it will help to improve the performance.

    To answer your questions,
    1. brs_staff_myreview_exc currently has 0 (zero) rows
    2 The logic in this code is confusing.
    Code (SQL):
      (ppl.email_name IS NOT NULL OR ppl.common_username = 'hb14868')
              AND ppl.common_username IS NOT NULL
    This is related to the select code above
    Code (SQL):
     CASE
              WHEN ppl.email_name IS NULL AND ppl.common_username = 'rbt1009'
              THEN
                 'doctor_on_call@barbeck.com'
              ELSE
                 ppl.email_name
    I think the logic is meant to be "'doctor_on_call@barbeck.com is reserved email for ppl.common_username of 'rbt1009'. So, it is not a personal email to a specific person, hence it is not stored in the database. So at the moment, this email belongs to ppl.common_username of 'rbt1009', but someone else will be using this address when this guy leaves. The way I look at it, which should be re-written is that since the column ppl.common_username is not null, there is no need to do an extra check.


    Many thanks for your support.
     
    Last edited: Apr 24, 2017 at 11:32 PM
  12. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    666
    Likes Received:
    136
    Trophy Points:
    810
    Location:
    Russian Federation
    Good luck.
    The task of refactoring is difficult , but they are interesting.
    If possible , then write about their successes and the results of the optimization.

    "Yes arrive with you the power of knowledge"