MySQL [sjcscredit]> explain SELECT O.crdt_contract_no, O.customer_no, O.customer_nm, O.prod_no, O.occr_type_cd, O.busi_ccy_cd, O.contract_amt, O.contract_bal, O.overdue_amt, O.fix_rate, O.exe_rate, O.start_dt, O.mat_dt, O.bfchg_major_gatmhd_cd, O.risk_classify_cd, O.optr_no, O.hndl_org_no, O.hndl_dt, O.afrnwl_contract_epday, CI.dcmtp_cd, CI.document_no FROM contract_bscinf O INNER JOIN cust_info CI ON O.customer_no = CI.customer_no INNER JOIN prd_catalog PC ON O.prod_no = PC.prod_no AND PC.onbs_ofbs_ind = '01' WHERE O.CONTRACT_STATUS IN ('320', '380') AND O.optr_no = '3954' AND O.hndl_org_no = '0011'; +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Query Plan | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ===================================================================== | | |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| | | --------------------------------------------------------------------- | | |0 |PX COORDINATOR | |385383 |10240861 | | | |1 |└─EXCHANGE OUT DISTR |:EX10001|385383 |8859001 | | | |2 | └─HASH JOIN | |385383 |5748888 | | | |3 | ├─EXCHANGE IN DISTR | |385383 |4341154 | | | |4 | │ └─EXCHANGE OUT DISTR (PKEY)|:EX10000|385383 |3134176 | | | |5 | │ └─HASH JOIN | |385383 |417827 | | | |6 | │ ├─TABLE FULL SCAN |PC |180 |33 | | | |7 | │ └─TABLE FULL SCAN |O |385382 |293261 | | | |8 | └─PX PARTITION ITERATOR | |3270518 |148645 | | | |9 | └─TABLE FULL SCAN |CI(tt) |3270518 |148645 | | | ===================================================================== | | Outputs & filters: | | ------------------------------------- | | 0 - output([INTERNAL_FUNCTION(O.CRDT_CONTRACT_NO, O.CUSTOMER_NO, O.CUSTOMER_NM, O.PROD_NO, O.OCCR_TYPE_CD, O.BUSI_CCY_CD, O.CONTRACT_AMT, O.CONTRACT_BAL, | | O.OVERDUE_AMT, O.FIX_RATE, O.EXE_RATE, O.START_DT, O.MAT_DT, O.BFCHG_MAJOR_GATMHD_CD, O.RISK_CLASSIFY_CD, O.OPTR_NO, O.HNDL_ORG_NO, O.HNDL_DT, O.AFRNWL_CONTRACT_EPDAY, | | CI.DCMTP_CD, CI.DOCUMENT_NO)]), filter(nil), rowset=256 | | 1 - output([INTERNAL_FUNCTION(O.CRDT_CONTRACT_NO, O.CUSTOMER_NO, O.CUSTOMER_NM, O.PROD_NO, O.OCCR_TYPE_CD, O.BUSI_CCY_CD, O.CONTRACT_AMT, O.CONTRACT_BAL, | | O.OVERDUE_AMT, O.FIX_RATE, O.EXE_RATE, O.START_DT, O.MAT_DT, O.BFCHG_MAJOR_GATMHD_CD, O.RISK_CLASSIFY_CD, O.OPTR_NO, O.HNDL_ORG_NO, O.HNDL_DT, O.AFRNWL_CONTRACT_EPDAY, | | CI.DCMTP_CD, CI.DOCUMENT_NO)]), filter(nil), rowset=256 | | dop=1 | | 2 - output([O.CUSTOMER_NO], [O.PROD_NO], [O.CRDT_CONTRACT_NO], [O.OPTR_NO], [O.HNDL_ORG_NO], [O.CUSTOMER_NM], [O.OCCR_TYPE_CD], [O.BUSI_CCY_CD], [O.CONTRACT_AMT], | | [O.CONTRACT_BAL], [O.OVERDUE_AMT], [O.FIX_RATE], [O.EXE_RATE], [O.START_DT], [O.MAT_DT], [O.BFCHG_MAJOR_GATMHD_CD], [O.RISK_CLASSIFY_CD], [O.HNDL_DT], | | [O.AFRNWL_CONTRACT_EPDAY], [CI.DCMTP_CD], [CI.DOCUMENT_NO]), filter(nil), rowset=256 | | equal_conds([O.CUSTOMER_NO = CI.CUSTOMER_NO]), other_conds(nil) | | 3 - output([O.CUSTOMER_NO], [O.PROD_NO], [O.CRDT_CONTRACT_NO], [O.OPTR_NO], [O.HNDL_ORG_NO], [O.CUSTOMER_NM], [O.OCCR_TYPE_CD], [O.BUSI_CCY_CD], [O.CONTRACT_AMT], | | [O.CONTRACT_BAL], [O.OVERDUE_AMT], [O.FIX_RATE], [O.EXE_RATE], [O.START_DT], [O.MAT_DT], [O.BFCHG_MAJOR_GATMHD_CD], [O.RISK_CLASSIFY_CD], [O.HNDL_DT], | | [O.AFRNWL_CONTRACT_EPDAY]), filter(nil), rowset=256 | | 4 - output([O.CUSTOMER_NO], [O.PROD_NO], [O.CRDT_CONTRACT_NO], [O.OPTR_NO], [O.HNDL_ORG_NO], [O.CUSTOMER_NM], [O.OCCR_TYPE_CD], [O.BUSI_CCY_CD], [O.CONTRACT_AMT], | | [O.CONTRACT_BAL], [O.OVERDUE_AMT], [O.FIX_RATE], [O.EXE_RATE], [O.START_DT], [O.MAT_DT], [O.BFCHG_MAJOR_GATMHD_CD], [O.RISK_CLASSIFY_CD], [O.HNDL_DT], | | [O.AFRNWL_CONTRACT_EPDAY]), filter(nil), rowset=256 | | (#keys=1, [O.CUSTOMER_NO]), is_single, dop=1 | | 5 - output([O.CUSTOMER_NO], [O.PROD_NO], [O.CRDT_CONTRACT_NO], [O.OPTR_NO], [O.HNDL_ORG_NO], [O.CUSTOMER_NM], [O.OCCR_TYPE_CD], [O.BUSI_CCY_CD], [O.CONTRACT_AMT], | | [O.CONTRACT_BAL], [O.OVERDUE_AMT], [O.FIX_RATE], [O.EXE_RATE], [O.START_DT], [O.MAT_DT], [O.BFCHG_MAJOR_GATMHD_CD], [O.RISK_CLASSIFY_CD], [O.HNDL_DT], | | [O.AFRNWL_CONTRACT_EPDAY]), filter(nil), rowset=256 | | equal_conds([O.PROD_NO = PC.PROD_NO]), other_conds(nil) | | 6 - output([PC.PROD_NO]), filter([PC.ONBS_OFBS_IND = cast('01', VARCHAR(1048576))]), rowset=256 | | access([PC.PROD_NO], [PC.ONBS_OFBS_IND]), partitions(p0) | | is_index_back=false, is_global_index=false, filter_before_indexback[false], | | range_key([PC.PROD_NO]), range(MIN ; MAX)always true | | 7 - output([O.CRDT_CONTRACT_NO], [O.CUSTOMER_NO], [O.PROD_NO], [O.OPTR_NO], [O.HNDL_ORG_NO], [O.CUSTOMER_NM], [O.OCCR_TYPE_CD], [O.BUSI_CCY_CD], [O.CONTRACT_AMT], | | [O.CONTRACT_BAL], [O.OVERDUE_AMT], [O.FIX_RATE], [O.EXE_RATE], [O.START_DT], [O.MAT_DT], [O.BFCHG_MAJOR_GATMHD_CD], [O.RISK_CLASSIFY_CD], [O.HNDL_DT], | | [O.AFRNWL_CONTRACT_EPDAY]), filter([O.CONTRACT_STATUS IN (cast('320', VARCHAR(1048576)), cast('380', VARCHAR(1048576)))], [O.HNDL_ORG_NO = cast('0011', | | VARCHAR(1048576))], [O.OPTR_NO = cast('3954', VARCHAR(1048576))]), rowset=256 | | access([O.CRDT_CONTRACT_NO], [O.CUSTOMER_NO], [O.PROD_NO], [O.CONTRACT_STATUS], [O.OPTR_NO], [O.HNDL_ORG_NO], [O.CUSTOMER_NM], [O.OCCR_TYPE_CD], [O.BUSI_CCY_CD], | | [O.CONTRACT_AMT], [O.CONTRACT_BAL], [O.OVERDUE_AMT], [O.FIX_RATE], [O.EXE_RATE], [O.START_DT], [O.MAT_DT], [O.BFCHG_MAJOR_GATMHD_CD], [O.RISK_CLASSIFY_CD], | | [O.HNDL_DT], [O.AFRNWL_CONTRACT_EPDAY]), partitions(p0) | | is_index_back=false, is_global_index=false, filter_before_indexback[false,false,false], | | range_key([O.CRDT_CONTRACT_NO]), range(MIN ; MAX)always true | | 8 - output([CI.CUSTOMER_NO], [CI.DCMTP_CD], [CI.DOCUMENT_NO]), filter(nil), rowset=256 | | affinitize, force partition granule | | 9 - output([CI.CUSTOMER_NO], [CI.DCMTP_CD], [CI.DOCUMENT_NO]), filter(nil), rowset=256 | | access([CI.CUSTOMER_NO], [CI.DCMTP_CD], [CI.DOCUMENT_NO]), partitions(p[0-255]) | | is_index_back=false, is_global_index=false, | | range_key([CI.CUSTOMER_NO], [CI.DCMTP_CD], [CI.DOCUMENT_NO]), range(MIN,MIN,MIN ; MAX,MAX,MAX)always true | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 57 rows in set (0.01 sec)