反连接走NL时无法改变驱动表

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(*)----------       304SQL> select count(*) from BUSINESS_CONTRACT    ;  COUNT(*)----------    246600explain 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，反联接无法改变驱动表