I'm trying to fetch the details of employee payment from Oracle Fusion HCM tables with he help of below SQL query but the results I'm getting with 4-5 minutes of delay. Need help on how can I improve the performance of this SQL query.

As I'm new to SQL and not aware of all the functionalities and best practices available in SQL. Please help as this query is from the support project what I'm working in

SELECT DISTINCT papf.person_number,
            ppnf.full_name name,
            bal.balance_value,
            fi.instance_name,
            fabu.bu_name
        FROM pay_payroll_actions ppa,
            pay_all_payrolls_f pay,
            pay_payroll_rel_actions pra,
            pay_pay_relationships_dn pprd,
            per_all_people_f papf,
            per_all_assignments_m paam,
            per_person_names_f ppnf,
            fun_all_business_units_v fabu,
            pay_time_periods ptp,
            pay_person_pay_methods_f ppm,
            pay_action_classes pac,
            pay_balance_types_vl pbt,
            PAY_FLOW_INSTANCES fi,
            PAY_REQUESTS pr,
            TABLE(
                pay_balance_view_pkg.get_balance_dimensions (
                    p_balance_type_id => pbt.balance_type_id,
                    p_payroll_rel_action_id => pra.payroll_rel_action_id,
                    p_payroll_term_id => NULL,
                    p_payroll_assignment_id => NULL
                )
            ) bal,
            pay_dimension_usages_vl pdu,
            PAY_ORG_PAY_METHODS_F popf1
        WHERE ppa.action_type IN ('Q', 'R')
            AND ppa.effective_date BETWEEN to_date(
                :p_pay_period,
                'MON-YY',
                'nls_date_language=American'
            ) AND LAST_DAY(
                to_date(
                    :p_pay_period,
                    'MON-YY',
                    'nls_date_language=American'
                )
            )
            AND pay.payroll_id = ppa.payroll_id
            AND ppa.PAYROLL_ACTION_ID = pra.PAYROLL_ACTION_ID
            AND pr.PAY_REQUEST_ID = ppa.PAY_REQUEST_ID
            AND fi.FLOW_INSTANCE_ID = pr.FLOW_INSTANCE_ID
            AND pay.payroll_name = NVL(:p_payroll, pay.payroll_name)
            AND ppa.effective_date BETWEEN pay.effective_start_date AND pay.effective_end_date
            AND pra.payroll_action_id = ppa.payroll_action_id
            AND pra.retro_component_id IS NULL
            AND pra.action_status = 'C'
            AND pprd.payroll_relationship_id = pra.payroll_relationship_id
            AND ppa.effective_date BETWEEN pprd.start_date AND pprd.end_date
            AND papf.person_id = pprd.person_id
            AND ppa.effective_date BETWEEN papf.effective_start_date AND papf.effective_end_date
            AND paam.person_id = papf.person_id
            AND paam.assignment_type = 'E'
            AND paam.primary_flag = 'Y'
            AND paam.effective_latest_change = 'Y'
            AND ppa.effective_date BETWEEN paam.effective_start_date AND paam.effective_end_date
            AND ppnf.person_id = pprd.person_id
            AND ppnf.name_type = 'GLOBAL'
            AND ppa.effective_date BETWEEN ppnf.effective_start_date AND ppnf.effective_end_date
            AND fabu.bu_id = paam.business_unit_id
            AND ppa.effective_date BETWEEN fabu.date_from AND fabu.date_to
            AND (
                LEAST(:company_name) IS NULL
                OR (
                    DECODE(
                        :company_name,
                        'Company 1',
                        1,
                        0
                    ) = 1
                    AND fabu.bu_name IN (
                        'Company 2',
                        'Shared Service BU'
                    )
                )
                OR (
                    DECODE(
                        :company_name,
                        'Company 3',
                        1,
                        0
                    ) = 0
                    AND fabu.bu_name IN (:company_name)
                )
            )
            AND pay.payroll_id = ptp.payroll_id
            AND ptp.period_category IN ('E', 'C')
            AND TO_CHAR(
                ptp.start_date,
                'MON-YY',
                'NLS_DATE_LANGUAGE = american'
            ) IN (:p_pay_period)
            AND ppm.payroll_relationship_id( ) = pprd.payroll_relationship_id
            AND pac.action_type = ppa.action_type
            AND pac.classification_name = 'SEQUENCED'
            AND pbt.legislation_code = 'SA'
            AND pbt.balance_name IN ('Net Pay')
            AND pdu.database_item_suffix = '_REL_RUN'
            AND pdu.balance_dimension_id = bal.balance_dimension_id
            AND pdu.legislation_code = 'SA'
            AND bal.balance_value <> 0
            AND ppm.org_payment_method_id = popf1.org_payment_method_id( )
            AND EXISTS (
                SELECT 1
                FROM pay_paymt_search_results_vl ppsrv,
                    pay_action_interlocks pai,
                    pay_payroll_rel_actions pre_rel_actions,
                    pay_payroll_actions pre_actions
                WHERE (
                        ppsrv.person_number = pprd.payroll_relationship_number
                        OR ppsrv.person_number || '-1' = pprd.payroll_relationship_number
                        OR ppsrv.person_number || '-2' = pprd.payroll_relationship_number
                        OR ppsrv.person_number || '-3' = pprd.payroll_relationship_number
                    )
                    AND pai.locked_action_id = pra.payroll_rel_action_id
                    AND pre_rel_actions.payroll_rel_action_id = pai.locking_action_id
                    AND pre_actions.payroll_action_id = pre_rel_actions.payroll_action_id
                    AND ppsrv.opm IN (:PAYMENT_METHOD)
                    AND ppsrv.payroll_rel_action_id = pre_rel_actions.payroll_rel_action_id
                    AND ppsrv.process_date BETWEEN ptp.start_date AND ptp.end_date
            )
        ORDER BY papf.person_number

CodePudding user response：

I don't know if your data needs to be real time but if not, I wonder if it would be prudent to create an additional table to hold the complexity of this query and update it on a schedule (nightly?). Have the new table do all the joins and math when a 5 min query doesn't hurt anything leaving this query as a simple select * from payTablesCombined.

CodePudding user response：

you should use this solution

 SELECT 

 ...........
      FROM pay_payroll_actions ppa

      left join pay_all_payrolls_f pay on  pay.payroll_id = ppa.payroll_id

      .....

Should use join