5 Replies Latest reply on Aug 22, 2019 2:41 PM by jaramill

    what is the best of way of optimizing the following query

    MKR

      SELECT /*+ parallel */

      d.id as DischargeID,

      hosp.ORGANIZATION_ID    AS ORGANIZATION_ID,

      hosp.ORGANIZATION_NAME AS FACILITY_NAME,

      af.name2 as AcuteFacilityName,

      c.first_name,

      c.last_name,

      pe.source_type,

      pe.account_number,

      pe.is_bpci,

      d.episode_identifier as Episode_Identifier,

      d.admission_date AS admissiondate,

      d.actual_discharge AS actualdischarge,

      NVL2(vbc_st.Create_DATE,1,0) as Request_Created,

      vbc_st.create_date as createdate,

      vbc_st.security_id,

      vbc_st.completed_date AS completeddate,

      NVL2(vbc_st.Create_DATE,DECODE(vbc_st.status_id,2,'yes','no'),NULL) AS metcriteria,

      con.last_name

      || ', '

      || con.first_name

      || ' '

      || con.middle_name AS Discharge_User,

      sent_ref.request_time AS referralsenttime, -- referral notified time

      sent_ref.user_sent_referral,

      sent_ref.providername AS Provider_Name,  

      book_ref.last_update as bookreferraltime, -- referral booked time

      book_ref.user_booked_ref,

      book_ref.locus AS bookedlocus,

      vbc_st.assessment_type

      FROM

          ERPDW01.discharge d

          JOIN ERPDW01.T_D_Patient_Encounter pe

              ON d.episode_identifier = pe.account_number

              AND d.MRN = pe.MRN

              AND d.Facility_id = pe.organization_id

          LEFT JOIN T_D_ORGANIZATION hosp

              ON  d.Facility_ID = hosp.ORGANIZATION_ID

          LEFT JOIN CLIN01.vbc_review_status vbc_st

              ON  ( vbc_st.discharge_id = d.id )

          JOIN ERPDW01.reference r

              ON ( d.ref_discharge_disposition = r.id )

          JOIN ERPDW01.facility af

              ON ( d.facility_id = af.id )

          JOIN ERPDW01.contact c

              ON ( d.contact_id = c.id )

          JOIN ERPDW01.security sec

              ON ( d.security_id = sec.id ) --discharge user security id

          JOIN ERPDW01.contact con

              ON ( sec.contact_id = con.id ) --discharge user info

          LEFT JOIN (

              SELECT /*+ parallel */

              brn.discharge_id,

              brn.booking_request_id,

              brn.note,

              br.facility_id,

              br.request_time,

              --br.in_network, --Hidden Tableau Field

              f.edischarge_enabled,

              f.name2 as providername,

               f.address1,

              f.address2,

              f.city,

              f.state_code,

              f.zip_code,

              brn.last_update,

               l.name as locus,

              con.last_name || ', ' || con.first_name AS user_sent_referral,

              brn.security_id as user_id,

              acpt.name AS final_provider_status,

              rqst.name AS final_hospital_status

              FROM

                ERPDW01.booking_note brn

                LEFT JOIN CLIN01.vbc_review_status vbc_st       --Synonym?

                   ON ( brn.discharge_id = vbc_st.discharge_id)

                 JOIN ERPDW01.booking_request br

                   ON ( br.discharge_id = brn.discharge_id  

                       and br.id = brn.booking_request_id  

                       AND brn.to_status_id = 1

                       and brn.from_status_id = 6

                       and  brn.booking_note_type_id = 1

                     )

                  LEFT JOIN ERPDW01.accept_state acpt

                    ON ( br.accept_state_id = acpt.id )

                  LEFT JOIN ERPDW01.request_state rqst

                    ON ( br.request_state_id = rqst.id )

                  JOIN ERPDW01.security s

                    ON ( brn.security_id = s.id )

                  JOIN ERPDW01.contact con

                    ON ( s.contact_id = con.id )

                  Left JOIN ERPDW01.facility f

                    ON ( br.facility_id = f.id )

                  JOIN ERPDW01.locus l

                    ON ( br.locus_id = l.id )

          ) sent_ref

            ON ( sent_ref.discharge_id = d.id )

          LEFT JOIN (

              SELECT /*+ parallel */

              brn.discharge_id,

               brn.booking_request_id,

              f.name2,

              f.address1,

              f.address2,

              f.city,

              f.state_code,

              f.zip_code,

              br.facility_id,

              brn.last_update,

              con.last_name || ', ' || con.first_name AS user_booked_ref,

              l.name locus

              FROM

                  ERPDW01.booking_note brn

                  LEFT  JOIN CLIN01.vbc_review_status vbc_st

                      ON ( brn.discharge_id = vbc_st.discharge_id    )

                  JOIN ERPDW01.booking_request br

                      ON ( br.discharge_id = brn.discharge_id

                  AND request_state_id = 2

                  AND brn.to_status_id = 2  and  brn.booking_note_type_id = 1  AND brn.from_status_id =1  and br.id = brn.booking_request_id

              )

              JOIN ERPDW01.security s

                ON ( brn.security_id = s.id )

              JOIN ERPDW01.contact con

                ON ( s.contact_id = con.id )

              JOIN ERPDW01.locus l

                ON ( br.locus_id = l.id )

             left JOIN ERPDW01.facility f

                ON ( br.facility_id = f.id )

          ) book_ref

            ON (

                book_ref.discharge_id = d.id

                and book_ref.booking_request_id=sent_ref.booking_request_id

               )

      WHERE

      d.facility_id in (

                          171330,171325,171315,91410,91561,170547,91634,171320,

                          91573,91524,57889,171327,91704,171326,171328,91654,

                          91739,91768,171323,91602,91589,91536,170545,

                          171322,171321,91560,57904,171314,170546,93516,

                          93530,93525,91587

                      )