通过图表简化sql语句的表关联(r4笔记第70天）
在之前的博文中分享过一个执行了两天的一条sql语句，走了两个大表的扫描，导致执行时间很长，通过简化sql做了不小的改进，今天我们来看看还可以做些什么。with tmp_logical_date as (SELECT logical_date FROM logical_date WHERE logical_date_type = 'R' AND expiration_date IS NULL)SELECT trim(TO_CHAR(COUNT(distinct coll.entity_id), '000000000')) FROM cl1_coll_entity coll, table_bpm_step_inst bpm, table_bpm_step, ar1_account, csm_account, csm_pay_channel, customer, subscriber, ar1_billing_arrangement, ar1_address_name, charge_distribute, tmp_logical_date WHERE coll.entity_id(+) = csm_account.ban AND coll.proc_inst_id = bpm.parent2proc_inst AND bpm.step2step = table_bpm_step.objid AND bpm.status = 30 AND coll.entity_id = ar1_account.account_id AND csm_account.ban = csm_pay_channel.ban-- AND ar1_account.account_id = ar1_aged_trial_balance.account_id AND csm_account.customer_id = customer.customer_id AND csm_account.customer_id = subscriber.customer_id AND ar1_account.account_id = ar1_billing_arrangement.account_id AND ar1_account.account_id = ar1_address_name.account_id AND ar1_address_name.address_type = 'ACC' and exists( (SELECT 1 FROM ar1_aged_trial_balance WHERE aged_type = 'D' AND group_type = 'B' AND status = 'EFF' AND TRUNC(tmp_logical_date.logical_date - due_date) >= 0 AND account_id = coll.entity_id ) ) AND subscriber.trx_id = charge_distribute.trx_id AND subscriber.subscriber_no = charge_distribute.agreement_no AND charge_distribute.target_pcn = csm_pay_channel.pym_channel_no AND csm_account.ban = csm_pay_channel.ban AND EXISTS (SELECT null--cl1_treatment_activity.entity_id FROM cl1_treatment_activity, table_bpm_step_inst, table_bpm_step WHERE cl1_treatment_activity.step_id = table_bpm_step_inst.objid AND table_bpm_step_inst.step2step = table_bpm_step.objid AND table_bpm_step.NAME LIKE '%IVR%' AND table_bpm_step_inst.status = 65 AND TO_DATE(TO_CHAR(cl1_treatment_activity.activity_date, 'YYYYMMDD'), 'YYYYMMDD') =tmp_logical_date.logical_date AND cl1_treatment_activity.entity_id = csm_account.ban)
coll.entity_id(+) = csm_account.ban3 rows selected.1 row selected.