Hi,
I have the below ctas, It is going for a Merge-Cartesian Join,
I have checked the where clause the join looks fine to me and the tables are also analyzed and statistics ok to me.
Is it becoz of Missing join indexes?. Can you please help?
CREATE TABLE sas_stage.SAS_ONLY_DGNS_2008 tablespace sas_stage_data parallel 8 nologging PARTITION BY LIST (PARTN_NAME)
(
PARTITION C01 VALUES ('C01'),
PARTITION C02 VALUES ('C02'),
PARTITION C03 VALUES ('C03'),
PARTITION C08 VALUES ('C08'),
PARTITION C09 VALUES ('C09'),
PARTITION C10 VALUES ('C10'),
PARTITION C11 VALUES ('C11'),
PARTITION C12 VALUES ('C12'),
PARTITION C14 VALUES ('C14'),
PARTITION C15 VALUES ('C15'),
PARTITION C16 VALUES ('C16'),
PARTITION C17 VALUES ('C17'),
PARTITION C18 VALUES ('C18'),
PARTITION C19 VALUES ('C19'),
PARTITION C20 VALUES ('C20')) as (
select /*+ parallel(xr,4) parallel(t,4) full(t) full(xr) no_expand*/
t.run_dt,
t.link_num,
t.hse_b_seq,
t.nch_clm_type_cd,
t.bene_clm_num_eq,
t.hse_clm_from,
t.hse_clm_thru,
t.hcfa_clm_proc,
t.first_expns,
t.last_expns,
t.carr_clm_rcpt,
t.ficarr_ident_nbr,
t.carr_clm_cntl_num,
t.hse_b_prvdr_tax_num,
t.npi_prfrmg,
d.dgns_cd,
t.hcpcs_cd,
t.hcpcs_initl_mdfr_cd,
t.hcpcs_2nd_mdfr_cd,
t.hse_cwfb_clm_pmt_dnl_cd,
t.line_prcsg_ind_cd,
t.cwfb_alow_chrg_amt,
t.bene_birth,
t.BENE_SEX_IDENT_CD,
t.FINAL_CLM_TYPE_CD,
t.HSE_B_PLC_SRVC_CD,
t.ptb_clm_id,
t.carr_line_prcng_lclty_cd,
t.bene_clm_num_curr,
substr(xr.mc_xref_otpt_fil_name,1,3) partn_name from sas_stage.part_b_fact_temp_2008 t ,
(SELECT /*+ full(xr) full(d) */ mc_xref_otpt_fil_name, mc_xref_clm_clmn_cd
FROM sas_stage.sas_pqri_cd_msr_xref
WHERE mc_xref_prod_ind = 'PQRI08' AND mc_xref_otpt_fil_name IN ('C01Cancer_Care', 'C02ENT', 'C03Other_Dgns', 'C08Cardiac_Care', 'C09Hepatitus', 'C10Repiratory_Care', 'C11Renal', 'C12Stroke', 'C14Osteoporosis', 'C15Depression', 'C16Diabetes', 'C17ER_Care', 'C18Eye_Care', 'C19Incontinence', 'C20Prostate_Cancer') AND mc_xref_clm_clmn_name IN ('DGNS')
) xr,
part_b.diagnosis_dimension d
where
t.first_expns between to_date('01/01/2008','mm/dd/yyyy') and to_date('12/31/2008','mm/dd/yyyy') and t.last_expns between to_date('01/01/2008','mm/dd/yyyy') and to_date('12/31/2008','mm/dd/yyyy') and ( t.LINE_DGNS_ID = d.DGNS_ID
or t.dgns_seq_1_id = d.DGNS_ID
or t.dgns_seq_2_id = d.DGNS_ID
or t.dgns_seq_3_id = d.DGNS_ID
or t.dgns_seq_4_id = d.DGNS_ID
or t.dgns_seq_5_id = d.DGNS_ID
or t.dgns_seq_6_id = d.DGNS_ID
or t.dgns_seq_7_id = d.DGNS_ID
or t.dgns_seq_8_id = d.DGNS_ID
)
AND xr.mc_xref_clm_clmn_cd = d.dgns_cd
)
CREATE TABLE STATEMENT, GOAL = ALL_ROWS 209205798 70410151 17391307297
PX COORDINATOR
PX SEND QC (RANDOM) SYS :TQ10003 208452964 70410151 17391307297
LOAD AS SELECT SAS_STAGE SAS_ONLY_DGNS_2008
PX RECEIVE 208452964 70410151 17391307297
PX SEND RANDOM LOCAL SYS :TQ10002 208452964 70410151 17391307297
HASH JOIN 208452964 70410151 17391307297
PX RECEIVE 26 848160 10177920
PX SEND BROADCAST SYS :TQ10000 26 848160 10177920
PX BLOCK ITERATOR 26 848160 10177920
TABLE ACCESS FULL PART_B DIAGNOSIS_DIMENSION 26 848160 10177920
MERGE JOIN CARTESIAN 199771699 170571106808 40084210099880
SORT JOIN
PX RECEIVE 2 131 4716
PX SEND BROADCAST SYS :TQ10001 2 131 4716
PX BLOCK ITERATOR 2 131 4716
TABLE ACCESS FULL SAS_STAGE SAS_PQRI_CD_MSR_XREF 2 131 4716
BUFFER SORT 199771697 1298634519 258428269281
PX BLOCK ITERATOR 2220601 1298634519 258428269281
TABLE ACCESS FULL SAS_STAGE PART_B_FACT_TEMP_2008 2220601 1298634519 258428269281
Thanks in advance