oracle left会少数据,left join记录变少

大神们帮看下，为啥查出来的记录变少了

查询是cam_cr_debt_mid_1(302848)     left join其他  没有where，计算结果记录变少(300000)

select crdeptmid.pk_rwa_run pk_rwa_run_set, crdeptmid.data_date,

crdeptmid.acct_no, crdeptmid.acct_no_his, crdeptmid.acct_term,

crdeptmid.cur_bal * currency.exchange_rate cur_bal, crdeptmid.avg_bal *

currency.exchange_rate avg_bal, crdeptmid.currency, currency.exchange_rate,

crdeptmid.depreciation_amt, crdeptmid.account_type,

crdeptmid.loan_state_code, crdeptmid.branch_code, crdeptmid.contract_no,

crdeptmid.debt_code, crdeptmid.debt_name, crdeptmid.debt_weight,

outdebtmid.debt_code out_debt_code, outdebtmid.debt_name out_debt_name,

outdebtmid.debt_ratio, crdeptmid.prod_code, custweight.cust_code,

custweight.cust_name, custweight.cust_type, crdeptmid.cust_manager_code,

cust_manager.cust_manager_name, custweight.credit_rating rating_code,

custweight.industry_code, typemid.col_code, ( ( crdeptmid.ead_principal +

crdeptmid.ead_int ) * currency.exchange_rate/typemid.loan_sum ) *

typemid.col_sum col_sum, typemid.col_type_code, typemid.col_type_name,

typemid.weight col_weight, product_w.adjust_weight prod_adjust_weight,

asset.adjust_weight loan_adjust_weight, custweight.cust_weight

cust_adjust_weight, custweight.industry_weight ind_adjust_weight,

remterm.adjust_weight rem_term_weight, rp_acct.adjust_num rp_adjust_weight,

sg_acct.adjust_num sg_adjust_weight, crdeptmid.rem_term,

remterm.rem_term_code, crdeptmid.product_code, crdeptmid.ead_principal *

currency.exchange_rate ead_principal, crdeptmid.ead_int *

currency.exchange_rate ead_int, typemid.col_type, crdeptmid.bl_code,

crdeptmid.asst_liab, crdeptmid.bf_ead_principal,

crdeptmid.cur_ead_principal, crdeptmid.loan_type,

rm_corp_dept_mapping.is_master

from

cam_cr_debt_mid_1 crdeptmid left outer join cam_out_debt_mid outdebtmid on

crdeptmid.pk_rwa_run = outdebtmid.pk_rwa_run and crdeptmid.data_date =

outdebtmid.data_date and crdeptmid.acct_no = outdebtmid.acct_no and

outdebtmid.data_date = '2017-10-25' and outdebtmid.pk_rwa_run =

'00010910000000TZ6EEX' left outer join ( select typemid.contract_no,

typemid.data_date, typemid.col_code, typemid.col_type, typemid.pk_rwa_run,

typemid.col_sum, typemid.col_prd, typemid.col_type_code,

typemid.col_type_name, typemid.weight, contract.loan_sum from

cam_col_type_mid typemid, rm_loan_contract contract where typemid.data_date

= contract.data_date and typemid.data_date = '2017-10-25' and

contract.data_date = '2017-10-25' and typemid.pk_rwa_run =

'00010910000000TZ6EEX' and typemid.contract_no = contract.contract_no )

typemid on crdeptmid.pk_rwa_run = typemid.pk_rwa_run and

crdeptmid.contract_no = typemid.contract_no and crdeptmid.data_date =

typemid.data_date and crdeptmid.acct_term <= typemid.col_prd left outer

join ( select cust.cust_code, cust.cust_name, cust.cust_type,

cust.credit_rating, cust.industry_code, cust_w.adjust_weight cust_weight,

industry_w.adjust_weight industry_weight from rm_customer cust left outer

join cam_cust_weight cust_w on cust.cust_type = cust_w.cust_type_code and

cust_w.cust_rating_code = cust.credit_rating and cust_w.pk_rwa_run =

'00010910000000TZ6EEX' left outer join cam_industry_weight industry_w on

cust.industry_code = industry_w.industry_code and industry_w.pk_rwa_run =

'00010910000000TZ6EEX' where cust.data_date = '2017-10-25' ) custweight on

custweight.cust_code = crdeptmid.cust_code left outer join cam_cust_manager

cust_manager on cust_manager.cust_manager_code =

crdeptmid.cust_manager_code and cust_manager.data_date =

crdeptmid.data_date left outer join cam_asset_weight asset on

asset.pk_rwa_run = '00010910000000TZ6EEX' and crdeptmid.pk_rwa_run =

asset.pk_rwa_run and crdeptmid.account_type = asset.account_type and

crdeptmid.loan_state_code = asset.loan_state_code left outer join

cam_product_weight product_w on product_w.pk_rwa_run =

'00010910000000TZ6EEX' and product_w.product_code = crdeptmid.product_code

left outer join cam_rem_term_weight remterm on remterm.pk_rwa_run =

'00010910000000TZ6EEX' and crdeptmid.pk_rwa_run = remterm.pk_rwa_run and

remterm.left_prd <= crdeptmid.rem_term and crdeptmid.rem_term < ( case when

remterm.right_prd is null then 999999 else remterm.right_prd end ) left

outer join ( select currexch.exchange_rate, currexch.from_currency from

rm_currency_mapping mapp, dwd_curr_exchange currexch where mapp.pk_currtype

= '00010000000000000001' and mapp.currency_code = currexch.to_currency and

currexch.data_date = '2017-10-25' ) currency on crdeptmid.currency =

currency.from_currency left outer join cam_sg_acct_aj sg_acct on

sg_acct.acct_no_his = crdeptmid.acct_no_his and sg_acct.pk_rwa_run =

'00010910000000TZ6EEX' left outer join cam_repayment repayment on

repayment.repay_code = crdeptmid.amrt_type left outer join cam_rp_aj

rp_acct on rp_acct.pk_repayment = repayment.pk_repayment and

rp_acct.pk_rwa_run = '00010910000000TZ6EEX' left outer join

rm_corp_dept_mapping on crdeptmid.branch_code =

rm_corp_dept_mapping.branch_code

call     count       cpu    elapsed       disk      query    current        rows

------- ------  -------- ---------- ---------- ---------- ----------  ----------

Parse        1      0.65       0.95          0          3          0           0

Execute      2      0.00       0.00          0          0          0           0

Fetch    30000     28.03      96.49     373150     379162          0      300000

------- ------  -------- ---------- ---------- ---------- ----------  ----------

total    30003     28.68      97.45     373150     379165          0      300000

Misses in library cache during parse: 1

Optimizer mode: ALL_ROWS

Parsing user id: 93

Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation

---------- ---------- ----------  ---------------------------------------------------

300000     300000     300000  HASH JOIN RIGHT OUTER (cr=379162 pr=373150 pw=30690 time=14459209 us cost=169951 size=555967940 card=314996)

157        157        157   TABLE ACCESS FULL CAM_PRODUCT_WEIGHT (cr=7 pr=6 pw=0 time=156 us cost=3 size=6149 card=143)

300000     300000     300000   HASH JOIN RIGHT OUTER (cr=379155 pr=373144 pw=30690 time=12923378 us cost=169946 size=542423112 card=314996)

0          0          0    TABLE ACCESS FULL CAM_SG_ACCT_AJ (cr=7 pr=6 pw=0 time=0 us cost=3 size=62 card=1)

300000     300000     300000    HASH JOIN RIGHT OUTER (cr=379148 pr=373138 pw=30690 time=12046546 us cost=169942 size=522893360 card=314996)

0          0          0     TABLE ACCESS FULL CAM_RP_AJ (cr=0 pr=0 pw=0 time=0 us cost=2 size=57 card=1)

300000     300000     300000HASH JOIN OUTER (cr=379148 pr=373138 pw=30690 time=11176883 us cost=169938 size=504938588 card=314996)

313574     313574     313574HASH JOIN RIGHT OUTER (cr=325241 pr=337053 pw=21173 time=3012092 us cost=111966 size=434473644 card=312796)

17         17         17       VIEW  (cr=238 pr=0 pw=0 time=48 us cost=73 size=225 card=9)

17         17         17        HASH JOIN  (cr=238 pr=0 pw=0 time=48 us cost=73 size=441 card=9)

1          1          1         TABLE ACCESS FULL RM_CURRENCY_MAPPING (cr=3 pr=0 pw=0 time=0 us cost=3 size=25 card=1)

17         17         17         TABLE ACCESS FULL DWD_CURR_EXCHANGE (cr=235 pr=0 pw=0 time=16 us cost=69 size=408 card=17)

313574     313574     313574       HASH JOIN RIGHT OUTER (cr=325003 pr=337053 pw=21173 time=2830988 us cost=111892 size=426653744 card=312796)

0          0          0        TABLE ACCESS FULL CAM_REPAYMENT (cr=7 pr=6 pw=0 time=0 us cost=3 size=34 card=1)

313574     313574     313574        HASH JOIN RIGHT OUTER (cr=324996 pr=337047 pw=21173 time=2654619 us cost=111888 size=416018680 card=312796)

851        851        851         TABLE ACCESS FULL RM_CORP_DEPT_MAPPING (cr=16 pr=0 pw=0 time=242 us cost=6 size=8130 card=813)

313574     313574     313574         HASH JOIN RIGHT OUTER (cr=324980 pr=337047 pw=21173 time=2452525 us cost=111880 size=412890720 card=312796)

0          0          0          TABLE ACCESS FULL CAM_REM_TERM_WEIGHT (cr=0 pr=0 pw=0 time=0 us cost=2 size=78 card=1)

313574     313574     313574          HASH JOIN RIGHT OUTER (cr=324980 pr=337047 pw=21173 time=2247998 us cost=111877 size=388492632 card=312796)

2889695    2889695    2889695           TABLE ACCESS FULL CAM_CUST_MANAGER (cr=20318 pr=20315 pw=0 time=6531763 us cost=5312 size=63806163 card=2774181)

313574     313574     313574           HASH JOIN RIGHT OUTER (cr=304662 pr=295559 pw=682 time=1243156 us cost=83758 size=381298324 card=312796)

36346      36346      36346            TABLE ACCESS FULL CAM_OUT_DEBT_MID (cr=31139 pr=31134 pw=0 time=16892 us cost=8464 size=43296 card=492)

313574     313574     313574            HASH JOIN RIGHT OUTER (cr=273523 pr=264425 pw=124 time=901298 us cost=75292 size=353772276 card=312796)

30         30         30             TABLE ACCESS FULL CAM_ASSET_WEIGHT (cr=7 pr=6 pw=0 time=29 us cost=4 size=930 card=30)

313574     313574     313574             HASH JOIN RIGHT OUTER (cr=273516 pr=264419 pw=124 time=677957 us cost=75286 size=344075600 card=312796)

75970      75970      75970              VIEW  (cr=264429 pr=264419 pw=62 time=1081727 us cost=72815 size=418 card=1)

75970      75970      75970               HASH JOIN  (cr=264429 pr=264419 pw=62 time=1063310 us cost=72815 size=440 card=1)

81267      81267      81267                TABLE ACCESS FULL CAM_COL_TYPE_MID (cr=4784 pr=4780 pw=0 time=31226 us cost=1297 size=405 card=1)

257923     257923     257923                TABLE ACCESS FULL RM_LOAN_CONTRACT (cr=259645 pr=259639 pw=0 time=5157544 us cost=71517 size=9951410 card=284326)

302848     302848     302848              TABLE ACCESS FULL CAM_CR_DEBT_MID_1 (cr=9087 pr=0 pw=0 time=301823 us cost=2470 size=213326872 card=312796)

2773678    2773678    2773678      VIEW  (cr=53907 pr=26568 pw=0 time=8437891 us cost=7502 size=595662580 card=2783470)

2773678    2773678    2773678       HASH JOIN RIGHT OUTER (cr=53907 pr=26568 pw=0 time=7658522 us cost=7502 size=289480880 card=2783470)

6          6          6        TABLE ACCESS FULL CAM_INDUSTRY_WEIGHT (cr=7 pr=6 pw=0 time=15 us cost=3 size=222 card=6)

2773678    2773678    2773678        HASH JOIN RIGHT OUTER (cr=53900 pr=26562 pw=0 time=6163015 us cost=7489 size=186492490 card=2783470)

0          0          0         TABLE ACCESS FULL CAM_CUST_WEIGHT (cr=23 pr=21 pw=0 time=0 us cost=8 size=30 card=1)

2773678    2773678    2773678         PARTITION RANGE SINGLE PARTITION: 1023 1023 (cr=53877 pr=26541 pw=0 time=4164867 us cost=7472 size=102988390 card=2783470)

2773678    2773678    2773678          TABLE ACCESS FULL RM_CUSTOMER PARTITION: 1023 1023 (cr=53877 pr=26541 pw=0 time=3426201 us cost=7472 size=102988390 card=2783470)

********************************************************************************