oracle多表join优化,多个大表的join有什么调优方法？

SQL:

SELECT /*+ use_hash(a e g)  */

MAX(g.first_account_entry_date),

MAX(g.request_date),

MAX(g.requested_date),

a.individual_agreement_id, --degree

a.cal_date_id, --degree

a.life_event_type_id, --degree

MAX(e.renewal_indicator),

MAX(e.account_entry_date),

MAX(e.status),

MAX(e.risk_category),

MAX(e.inception_date),

MAX(e.issue_date),

MAX(e.planned_end_date),

MAX(e.selling_channel_role_player_id),

MAX(e.external_reference),

MAX(e.branch_company_id),

MAX(e.department_id),

MAX(e.section_office_id),

MAX(f.external_reference),

MAX(e.selling_channel_type),

MAX(h.circ_using_code),

MAX(e.recorded_date),

MAX(e.underwritting_period),

MAX(a.population_timestamp),

MAX(a.number_of_policy),

SUM(a.premium_amount * rate_f2r) premium_amount ,

SUM(CASE

WHEN a.currency_code = '01' THEN

a.premium_amount

ELSE

0

END) premium_amount_rmb

,

SUM(CASE

WHEN a.currency_code != '01' THEN

a.premium_amount * i.rate_f2u

ELSE

0

END) premium_amount_usd

,

MAX(a.number_of_business_transaction)

,

SUM(a.saving_premium_amount * i.rate_f2r) saving_premium_amount

,

SUM(CASE

WHEN a.currency_code = '01' THEN

a.saving_premium_amount

ELSE

0

END) saving_premium_amount_rmb

,

SUM(CASE

WHEN a.currency_code != '01' THEN

a.saving_premium_amount * i.rate_f2u

ELSE

0

END) saving_premium_amount_usd

,

SUM(a.sum_insured_amount * i.rate_f2r) sum_insured_amount

,

SUM(CASE

WHEN a.currency_code = '01' THEN

a.sum_insured_amount

ELSE

0

END) sum_insured_amount_rmb

,

SUM(CASE

WHEN a.currency_code != '01' THEN

a.sum_insured_amount * i.rate_f2u

ELSE

0

END) sum_insured_amount_usd

,

SUM(a.collected_premium_amount * i.rate_f2r) collected_premium_amount

,

SUM(CASE

WHEN a.currency_code = '01' THEN

a.collected_premium_amount

ELSE

0

END) collected_premium_amount_rmb

,

SUM(CASE

WHEN a.currency_code != '01' THEN

a.collected_premium_amount * i.rate_f2u

ELSE

0

END) collected_premium_amount_usd

,

SUM(a.payment_amount * rate_f2r) payment_amount

,

SUM(CASE

WHEN a.currency_code = '01' THEN

a.payment_amount

ELSE

0

END) payment_amount_rmb

,

SUM(CASE

WHEN a.currency_code != '01' THEN

a.payment_amount * i.rate_f2u

ELSE

0

END) payment_amount_usd

,

MAX(a.number_of_insured_object)

,

MAX(e.number_of_covered_person)

FROM policy_underwriting_fact      a,  -- 2680920 rows

individual_agreement_snapshot e,  -- 2412020 rows

financial_service_product     f,  -- 1178 rows

agreement_request             g,  -- 2846620 rows

internal_organisation         h,  -- 14778 rows

bs_fe_exchange_rate           i   -- 174 rows

WHERE a.individual_agreement_id = e.individual_agreement_id

AND a.agreement_request_id = g.agreement_request_id(+)

AND a.financial_service_product_id = f.financial_service_product_id

AND e.department_id = h.internal_organisation_id

AND a.branch_company_id = e.branch_company_id

AND a.branch_company_id = g.branch_company_id(+)

AND a.branch_company_id = 1000004528

AND a.currency_code = i.base_currency

AND a.cal_date_id BETWEEN to_number(to_char(i.start_date, 'yyyymmdd')) AND

to_number(to_char(i.end_date, 'yyyymmdd'))

GROUP BY a.individual_agreement_id, --degree

a.cal_date_id, --degree

a.life_event_type_id, --degree

a.financial_service_product_id,

a.agreement_request_id,

a.external_organisation_id,

a.internal_organisation_id;