explain plan for
SELECT BC."SERIALNO",
BC."RELATIVESERIALNO",
BC."ARTIFICIALNO",
BC."OCCURDATE",
BC."CUSTOMERID",
BC."CUSTOMERNAME",
BC."BUSINESSTYPE",
BC."OLDBUSINESSTYPE",
BC."BUSINESSSUBTYPE",
BC."OCCURTYPE",
BC."CREDITDIGEST",
BC."CREDITCYCLE",
BC."CREDITTYPE",
BC."CURRENYLIST",
BC."CURRENCYMODE",
BC."BUSINESSTYPELIST",
BC."CALCULATEMODE",
BC."USEORGLIST",
BC."FLOWREDUCEFLAG",
BC."CONTRACTFLAG",
BC."SUBCONTRACTFLAG",
BC."SELFUSEFLAG",
BC."CREDITINDEX",
BC."CREDITREDUCESUM",
BC."LIMITATIONTERM",
BC."USETERM",
BC."CREDITAGGREEMENT",
BC."RELATIVEAGREEMENT",
BC."LOANFLAG",
BC."TOTALSUM",
BC."OURROLE",
BC."REVERSIBILITY",
BC."BILLNUM",
BC."HOUSETYPE",
BC."LCTERMTYPE",
BC."RISKATTRIBUTE",
BC."SURETYPE",
BC."SAFEGUARDTYPE",
BC."CREDITBUSINESS",
BC."BUSINESSCURRENCY",
BC."BUSINESSSUM",
BC."BUSINESSPROP",
BC."TERMYEAR",
BC."TERMMONTH",
BC."TERMDAY",
BC."LGTERM",
BC."BASERATETYPE",
BC."BASERATE",
BC."RATEFLOATTYPE",
BC."RATEFLOAT",
BC."BUSINESSRATE",
BC."ICTYPE",
BC."ICCYC",
BC."PDGRATIO",
BC."PDGSUM",
BC."PDGPAYMETHOD",
BC."PDGPAYPERIOD",
BC."PROMISESFEERATIO",
BC."PROMISESFEESUM",
BC."PROMISESFEEPERIOD",
BC."PROMISESFEEBEGIN",
BC."MFEERATIO",
BC."MFEESUM",
BC."MFEEPAYMETHOD",
BC."AGENTFEE",
BC."DEALFEE",
BC."TOTALCAST",
BC."DISCOUNTINTEREST",
BC."PURCHASERINTEREST",
BC."BARGAINORINTEREST",
BC."DISCOUNTSUM",
BC."BAILRATIO",
BC."BAILCURRENCY",
BC."BAILSUM",
BC."BAILACCOUNT",
BC."FINERATETYPE",
BC."FINERATE",
BC."DRAWINGTYPE",
BC."FIRSTDRAWINGDATE",
BC."DRAWINGPERIOD",
BC."PAYTIMES",
BC."PAYCYC",
BC."GRACEPERIOD",
BC."OVERDRAFTPERIOD",
BC."OLDLCNO",
BC."OLDLCTERMTYPE",
BC."OLDLCCURRENCY",
BC."OLDLCSUM",
BC."OLDLCLOADINGDATE",
BC."OLDLCVALIDDATE",
BC."DIRECTION",
BC."PURPOSE",
BC."PLANALLOCATION",
BC."IMMEDIACYPAYSOURCE",
BC."PAYSOURCE",
BC."CORPUSPAYMETHOD",
BC."INTERESTPAYMETHOD",
BC."PUTOUTDATE",
BC."MATURITY",
BC."THIRDPARTY1",
BC."THIRDPARTYID1",
BC."THIRDPARTY2",
BC."THIRDPARTYID2",
BC."THIRDPARTY3",
BC."THIRDPARTYID3",
BC."THIRDPARTYREGION",
BC."THIRDPARTYACCOUNTS",
BC."CARGOINFO",
BC."PROJECTNAME",
BC."OPERATIONINFO",
BC."CONTEXTINFO",
BC."SECURITIESTYPE",
BC."SECURITIESREGION",
BC."CONSTRUCTIONAREA",
BC."USEAREA",
BC."FLAG1",
BC."FLAG2",
BC."FLAG3",
BC."TRADECONTRACTNO",
BC."INVOICENO",
BC."TRADECURRENCY",
BC."TRADESUM",
BC."LCNO",
BC."PAYMENTDATE",
BC."OPERATIONMODE",
BC."BEGINDATE",
BC."ENDDATE",
BC."VOUCHCLASS",
BC."VOUCHTYPE",
BC."VOUCHTYPE1",
BC."VOUCHTYPE2",
BC."VOUCHFLAG",
BC."WARRANTOR",
BC."WARRANTORID",
BC."OTHERCONDITION",
BC."GUARANTYVALUE",
BC."GUARANTYRATE",
BC."BASEEVALUATERESULT",
BC."RISKRATE",
BC."LOWRISK",
BC."OTHERAREALOAN",
BC."LOWRISKBAILSUM",
BC."APPLYTYPE",
BC."ORIGINALPUTOUTDATE",
BC."EXTENDTIMES",
BC."LNGOTIMES",
BC."GOLNTIMES",
BC."DRTIMES",
BC."GUARANTYNO",
BC."PUTOUTSUM",
BC."ACTUALPUTOUTSUM",
BC."BALANCE",
BC."NORMALBALANCE",
BC."OVERDUEBALANCE",
BC."DULLBALANCE",
BC."BADBALANCE",
BC."INTERESTBALANCE1",
BC."INTERESTBALANCE2",
BC."FINEBALANCE1",
BC."FINEBALANCE2",
BC."OVERDUEDAYS",
BC."OWEINTERESTDAYS",
BC."TABALANCE",
BC."TAINTERESTBALANCE",
BC."TATIMES",
BC."LCATIMES",
BC."PBINTERESTSUM",
BC."PBMFEESUM",
BC."PBPDGSUM",
BC."PBLEGALCOSTSUM",
BC."POLEGALCOSTSUM",
BC."ORIGINALBADDATE",
BC."BASECLASSIFYRESULT",
BC."CLASSIFYRESULT",
BC."CLASSIFYTYPE",
BC."CLASSIFYDATE",
BC."CLASSIFYORGID",
BC."RESERVESUM",
BC."EXPECTLOSSSUM",
BC."BAILRATE",
BC."FINISHORG",
BC."FINISHTYPE",
BC."FINISHDATE",
BC."DESCRIBE1",
BC."REINFORCEFLAG",
BC."MANAGEORGID",
BC."MANAGEUSERID",
BC."RECOVERYORGID",
BC."RECOVERYUSERID",
BC."STATORGID",
BC."STATUSERID",
BC."OPERATEORGID",
BC."OPERATEUSERID",
BC."OPERATEDATE",
BC."INPUTORGID",
BC."INPUTUSERID",
BC."INPUTDATE",
BC."UPDATEDATE",
BC."PIGEONHOLEDATE",
BC."REMARK",
BC."FLAG4",
BC."PAYCURRENCY",
BC."PAYDATE",
BC."FLAG5",
BC."CLASSIFYSUM1",
BC."CLASSIFYSUM2",
BC."CLASSIFYSUM3",
BC."CLASSIFYSUM4",
BC."CLASSIFYSUM5",
BC."SHIFTTYPE",
BC."OPERATETYPE",
BC."FUNDSOURCE",
BC."CYCLEFLAG",
BC."CREDITFREEZEFLAG",
BC."SHIFTBALANCE",
BC."CLASSIFYFREQUENCY",
BC."CLASSIFYLEVEL",
BC."VOUCHNEWFLAG",
BC."ACTUALARTIFICIALNO",
BC."DELETEFLAG",
BC."ACCOUNTNO",
BC."LOANACCOUNTNO",
BC."SECONDPAYACCOUNT",
BC."ADJUSTRATETYPE",
BC."ADJUSTRATETERM",
BC."OVERINTTYPE",
BC."RATEADJUSTCYC",
BC."PDGACCOUNTNO",
BC."DEDUCTDATE",
BC."FZANBALANCE",
BC."ACCEPTINTTYPE",
BC."RATIO",
BC."THIRDPARTYADD1",
BC."THIRDPARTYZIP1",
BC."THIRDPARTYADD2",
BC."THIRDPARTYZIP2",
BC."THIRDPARTYADD3",
BC."THIRDPARTYZIP3",
BC."EFFECTAREA",
BC."TERMDATE1",
BC."TERMDATE2",
BC."TERMDATE3",
BC."FIXCYC",
BC."DESCRIBE2",
BC."CANCELSUM",
BC."CANCELINTEREST",
BC."LOANTERM",
BC."PUTOUTORGID",
BC."TEMPSAVEFLAG",
BC."OVERDUEDATE",
BC."OWEINTERESTDATE",
BC."FREEZEFLAG",
BC."APPROVEDATE",
BC."SHIFTSTATUS",
BC."RECOVERYCOGNORGID",
BC."RECOVERYCOGNUSERID",
BC."SHIFTDOCDESCRIBE",
BC."APPLYDATE",
BC."REPAYDATE",
BC."REPAYFUNDSOURCE",
BC."HEAPTYPE",
BC."LOWRISKSUM",
BC."FINISHFLAG",
BC."CONTRACTSTATUS",
BC."AUDITUSERID",
BC."LISTINSUM",
BC."RATEINSTANCE",
BC."TEXTNO",
BC."BSFLIMIT",
BC."BSFRISKSTATE",
BC."HEAPBALANCE",
BC."EXCHANGERATE",
BC."BREAKDATE",
BC."BILLTYPE",
BC."PRODUCTID",
BC."ISDIVIDED",
BC."RELATIVECONTRACTNO",
BC."COOPERATIVECLNO",
BC."COOPERATIVECLTYPE",
BC."GUARANTEESUM",
BC."CREDITSUM",
BC."PAWNSUM",
BC."PAYPRININTVL",
BC."INTMODE",
BC."OTHERAREALOAN2",
BC."PRODUCTID2",
BC."TAXLISTORNOT",
BC."CREDITDEPOSITORYSUM",
BC."OLDMANAGEORGID",
BC."CONTRALPROJECT",
BC."DEFERSTATUS",
BC."LINKTIMES",
BC."CREDITLINESUM",
BC."LISTINLIMITSUM",
BC."INDUSTRYADJUST",
BC."INDUSTRYUPGRADE",
BC."NEWINDUSTRYTYPE",
BC."CONTRALREASON",
BC."RELATIONSHIP1",
BC."RELATIONSHIP2",
BC."RELATIONSHIP3",
BC."ISPOINTCREDIT",
BC."DCPROP",
BC."DCPROP1",
BC."DCPROP2",
BC."ISDEPOSITLOAN"
FROM BUSINESS_CONTRACT BC
WHERE SERIALNO NOT IN (SELECT SERIALNO FROM TRANSFER_CONTRACT);
select * from table(dbms_xplan.display());
Plan hash value: 410066256
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 246K| 134M| 6510 (1)| 00:01:19 |
|* 1 | HASH JOIN RIGHT ANTI | | 246K| 134M| 6510 (1)| 00:01:19 |
| 2 | INDEX FAST FULL SCAN| PK_TRANSFER_CONTRACT | 304 | 4864 | 2 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL | BUSINESS_CONTRACT | 246K| 130M| 6507 (1)| 00:01:19 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("SERIALNO"="SERIALNO")
SQL> select count(*) from TRANSFER_CONTRACT;
COUNT(*)
----------
304
SQL> select count(*) from BUSINESS_CONTRACT ;
COUNT(*)
----------
246600
explain plan for
SELECT /*+ leading(BD) use_nl(BC BD) */ BC.*
FROM BUSINESS_CONTRACT BC
WHERE SERIALNO NOT IN (SELECT SERIALNO FROM TRANSFER_CONTRACT BD);
Plan hash value: 1040340129
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 246K| 134M| 6520 (1)| 00:01:19 |
| 1 | NESTED LOOPS ANTI | | 246K| 134M| 6520 (1)| 00:01:19 |
| 2 | TABLE ACCESS FULL| BUSINESS_CONTRACT | 246K| 130M| 6507 (1)| 00:01:19 |
|* 3 | INDEX UNIQUE SCAN| PK_TRANSFER_CONTRACT | 1 | 16 | 0 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("SERIALNO"="SERIALNO")
驱动表此时变为BUSINESS_CONTRACT，反联接无法改变驱动表