一个sql执行计划的改变
一，简介
   1，需要执行的sql如下，其中llu.id 这个参数是可变的，根据配置的变化sql会变化
   2，GV_INV_LOTLOCATIONUOM 的自连接，他有127713260条记录
   3，当llu.id这个参数有150个参数时，执行计划如下
       
SELECT t.id AS docId, o.*
  FROM GV_INV_LOTLOCATIONUOM o,
       (SELECT *
          FROM GV_INV_LOTLOCATIONUOM llu
         WHERE llu.id IN (:1,
                          :2.......150)) t
 WHERE o.domain_Id = t.domain_Id
   AND o.org_id = t.org_id
   AND o.warehouse_id = t.warehouse_id
   AND o.customer_id = t.customer_id
   AND o.location_id = :43
   AND o.uom_id = t.uom_id
   AND o.sku_id = t.sku_id
   AND o.traceCode IS NULL
   AND o.lotHeader_id || '_' = t.lotHeader_id || '_'
   AND o.inboundtime || '_' = t.inboundtime || '_'
   AND o.fixCreatedTime || '_' = t.fixCreatedTime || '_'
   AND o.fixExpiredTime || '_' = t.fixExpiredTime || '_'
   AND o.fixStatus_id || '_' = t.fixStatus_id || '_'
   AND o.lotAtt01 || '_' = t.lotAtt01 || '_'
   AND o.lotAtt02 || '_' = t.lotAtt02 || '_'
   AND o.lotAtt03 || '_' = t.lotAtt03 || '_'
   AND o.lotAtt04 || '_' = t.lotAtt04 || '_'
   AND o.lotAtt05 || '_' = t.lotAtt05 || '_'
   AND o.lotAtt06 || '_' = t.lotAtt06 || '_'




 Plan Hash Value  : 1874416940 


-----------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name                     | Rows | Bytes | Cost | Time     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                          |    1 |   572 |  249 | 00:00:03 |
|   1 |   NESTED LOOPS                        |                          |    1 |   572 |  249 | 00:00:03 |
|   2 |    PARTITION HASH ALL                 |                          |   65 | 18590 |   54 | 00:00:01 |
| * 3 |     TABLE ACCESS BY LOCAL INDEX ROWID | GV_INV_LOTLOCATIONUOM    |   65 | 18590 |   54 | 00:00:01 |
| * 4 |      INDEX RANGE SCAN                 | IDX_INVLLU_LOCATION_ID   |   65 |       |   17 | 00:00:01 |
|   5 |    PARTITION HASH ITERATOR            |                          |    1 |   286 |    3 | 00:00:01 |
| * 6 |     TABLE ACCESS BY LOCAL INDEX ROWID | GV_INV_LOTLOCATIONUOM    |    1 |   286 |    3 | 00:00:01 |
| * 7 |      INDEX RANGE SCAN                 | IDX_UNION_LOTLOCATIONUOM |    1 |       |    2 | 00:00:01 |
-----------------------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
------------------------------------------
* 3 - filter("O"."TRACECODE" IS NULL)
* 4 - access("O"."LOCATION_ID"=4922418)
* 6 - filter(("LLU"."ID"=45977555 OR "LLU"."ID"=47458234 OR "LLU"."ID"=49189599 OR "LLU"."ID"=49781785 OR "LLU"."ID"=51977373 OR "LLU"."ID"=52038166 OR "LLU"."ID"=52147738 OR "LLU"."ID"=52161640 OR
 ."CUSTOMER_ID"="LLU"."CUSTOMER_ID" AND TO_CHAR("O"."LOTHEADER_ID")||'_'=TO_CHAR("LLU"."LOTHEADER_ID")||'_' AND
  INTERNAL_FUNCTION("O"."INBOUNDTIME")||'_'=INTERNAL_FUNCTION("LLU"."INBOUNDTIME")||'_' AND INTERNAL_FUNCTION("O"."FIXCREATEDTIME")||'_'=INTERNAL_FUNCTION("LLU"."FIXCREATEDTIME")||'_' AND
  INTERNAL_FUNCTION("O"."FIXEXPIREDTIME")||'_'=INTE)
* 7 - access("O"."UOM_ID"="LLU"."UOM_ID" AND "O"."SKU_ID"="LLU"."SKU_ID")


注意
4，rows=65的计算方式   
17:33:56 sys@WMS>select 127713260/1941639 from dual;


127713260/1941639
-----------------
       65.7760068
17:56:18 sys@WMS>select count(*) from  wms.GV_INV_LOTLOCATIONUOM o  where o.location_id = 4922418
17:56:21   2  ;


  COUNT(*)
----------
    367116
显然数据库已o表做驱动表走location id 来做嵌套循环是不对的。
    
5，如果走llu.id的方式，因为id是主键，所以有多少参数，就有多少rows，计算的rows是150，cbo没有选择这个执行方式
 在加了hint /*+ leading(t) */ 后出现如下执行计划，走的是hash 连接
 最好的执行计划是，以t做驱动表走，嵌套连接。       
 Plan Hash Value  : 2390386484 


----------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name                   | Rows | Bytes | Cost | Time     |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                        |    1 |   572 |  282 | 00:00:04 |
| * 1 |   HASH JOIN                            |                        |    1 |   572 |  282 | 00:00:04 |
|   2 |    INLIST ITERATOR                     |                        |      |       |      |          |
|   3 |     TABLE ACCESS BY GLOBAL INDEX ROWID | GV_INV_LOTLOCATIONUOM  |  150 | 42900 |  228 | 00:00:03 |
| * 4 |      INDEX UNIQUE SCAN                 | SYS_C0013482           |  150 |       |  153 | 00:00:02 |
|   5 |    PARTITION HASH ALL                  |                        |   65 | 18590 |   54 | 00:00:01 |
| * 6 |     TABLE ACCESS BY LOCAL INDEX ROWID  | GV_INV_LOTLOCATIONUOM  |   65 | 18590 |   54 | 00:00:01 |
| * 7 |      INDEX RANGE SCAN                  | IDX_INVLLU_LOCATION_ID |   65 |       |   17 | 00:00:01 |
----------------------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
------------------------------------------
* 1 - access("O"."DOMAIN_ID"="LLU"."DOMAIN_ID" AND "O"."ORG_ID"="LLU"."ORG_ID" AND "O"."WAREHOUSE_ID"="LLU"."WAREHOUSE_ID" AND "O"."CUSTOMER_ID"="LLU"."CUSTOMER_ID" AND "O"."UOM_ID"="LLU"."UOM_ID"
  AND "O"."SKU_ID"="LLU"."SKU_ID" AND TO_CHAR("O"."LOTHEADER_ID")||'_'=TO_CHAR("LLU"."LOTHEADER_ID")||'_' AND INTERNAL_FUNCTION("O"."INBOUNDTIME")||'_'=INTERNAL_FUNCTION("LLU"."INBOUNDTIME")||'_' AND
  INTERNAL_FUNCTION("O"."FIXCREATEDTIME")||'_'=INTERNAL_FUNCTION("LLU"."FIXCREATEDTIME")||'_' AND INTERNAL_FUNCTION("O"."FIXEXPIREDTIME")||'_'=INTERNAL_FUNCTION("LLU"."FIXEXPIREDTIME")||'_' AND
  TO_CHAR("O"."FIXSTATUS_ID")||'_'=TO_CHAR("LLU"."FIXSTATUS_ID")||'_' AND "O"."LOTATT01"||'_'="LLU"."LOTATT01"||'_' AND "O"."LOTATT02"||'_'="LLU"."LOTATT02"||'_' AND
  "O"."LOTATT03"||'_'="LLU"."LOTATT03"||'_' AND "O"."LOTATT04"||'_'="LLU"."LOTATT04"||'_' AND "O"."LOTATT05"||'_'="LLU"."LOTATT05"||'_' AND "O"."LOTATT06"||'_'="LLU"."LOTATT06"||'_')
* 4 - access("LLU"."ID"=45977555 OR "LLU"."ID"=47458234 OR "LLU"."ID"=49189599 OR "LLU"."ID"=49781785 OR "LLU"."ID"=51977373 OR "LLU"."ID"=52038166 OR "LLU"."ID"=52147738 OR "LLU"."ID"=52161640 OR
 ID"=590027057 OR "LLU"."ID"=596508187 OR "LLU"."ID"=597871307 OR "LLU"."ID"=599239467)
* 6 - filter("O"."TRACECODE" IS NULL)
* 7 - access("O"."LOCATION_ID"=4922418)       
       


 Plan Hash Value  : 2390386484 


----------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name                   | Rows | Bytes | Cost | Time     |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                        |    1 |   572 |  155 | 00:00:02 |
| * 1 |   HASH JOIN                            |                        |    1 |   572 |  155 | 00:00:02 |
|   2 |    INLIST ITERATOR                     |                        |      |       |      |          |
|   3 |     TABLE ACCESS BY GLOBAL INDEX ROWID | GV_INV_LOTLOCATIONUOM  |   65 | 18590 |  101 | 00:00:02 |
| * 4 |      INDEX UNIQUE SCAN                 | SYS_C0013482           |   65 |       |   68 | 00:00:01 |
|   5 |    PARTITION HASH ALL                  |                        |   65 | 18590 |   54 | 00:00:01 |
| * 6 |     TABLE ACCESS BY LOCAL INDEX ROWID  | GV_INV_LOTLOCATIONUOM  |   65 | 18590 |   54 | 00:00:01 |
| * 7 |      INDEX RANGE SCAN                  | IDX_INVLLU_LOCATION_ID |   65 |       |   17 | 00:00:01 |
----------------------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
------------------------------------------
* 1 - access("O"."DOMAIN_ID"="LLU"."DOMAIN_ID" AND "O"."ORG_ID"="LLU"."ORG_ID" AND "O"."WAREHOUSE_ID"="LLU"."WAREHOUSE_ID" AND "O"."CUSTOMER_ID"="LLU"."CUSTOMER_ID" AND "O"."UOM_ID"="LLU"."UOM_ID"
  AND "O"."SKU_ID"="LLU"."SKU_ID" AND TO_CHAR("O"."LOTHEADER_ID")||'_'=TO_CHAR("LLU"."LOTHEADER_ID")||'_' AND INTERNAL_FUNCTION("O"."INBOUNDTIME")||'_'=INTERNAL_FUNCTION("LLU"."INBOUNDTIME")||'_' AND
  INTERNAL_FUNCTION("O"."FIXCREATEDTIME")||'_'=INTERNAL_FUNCTION("LLU"."FIXCREATEDTIME")||'_' AND INTERNAL_FUNCTION("O"."FIXEXPIREDTIME")||'_'=INTERNAL_FUNCTION("LLU"."FIXEXPIREDTIME")||'_' AND
  TO_CHAR("O"."FIXSTATUS_ID")||'_'=TO_CHAR("LLU"."FIXSTATUS_ID")||'_' AND "O"."LOTATT01"||'_'="LLU"."LOTATT01"||'_' AND "O"."LOTATT02"||'_'="LLU"."LOTATT02"||'_' AND
  "O"."LOTATT03"||'_'="LLU"."LOTATT03"||'_' AND "O"."LOTATT04"||'_'="LLU"."LOTATT04"||'_' AND "O"."LOTATT05"||'_'="LLU"."LOTATT05"||'_' AND "O"."LOTATT06"||'_'="LLU"."LOTATT06"||'_')
* 4 - access("LLU"."ID"=49189599 OR "LLU"."ID"=52038166 OR "LLU"."ID"=52147738 OR "LLU"."ID"=54005775 OR "LLU"."ID"=60104178 OR "LLU"."ID"=60163482 OR "LLU"."ID"=64023775 OR "LLU"."ID"=64042444 OR
  "LLU"."ID"=64104666 OR "LLU"."ID"=64227729 OR "LLU"."ID"=66883555 OR "LLU"."ID"=69884257 OR "LLU"."ID"=69884277 OR "LLU"."ID"=69884491 OR "LLU"."ID"=70059387 OR "LLU"."ID"=71321605 OR
  "LLU"."ID"=71843147 OR "LLU"."ID"=73872181 OR "LLU"."ID"=81936569 OR "LLU"."ID"=82059984 OR "LLU"."ID"=83977036 OR "LLU"."ID"=85308742 OR "LLU"."ID"=85464607 OR "LLU"."ID"=85580297 OR
  "LLU"."ID"=85673052 OR "LLU"."ID"=85699670 OR "LLU"."ID"=85850963 OR "LLU"."ID"=85851392 OR "LLU"."ID"=85852479 OR "LLU"."ID"=86564725 OR "LLU"."ID"=100305127 OR "LLU"."ID"=160517367 OR
  "LLU"."ID"=183898597 OR "LLU"."ID"=246089447 OR "LLU"."ID"=246325737 OR "LLU"."ID"=254965527 OR "LLU"."ID"=277559697 OR "LLU"."ID"=277559787 OR "LLU"."ID"=324798797 OR "LLU"."ID"=455024707 OR
  "LLU"."ID"=462167737 OR "LLU"."ID"=467542007 OR "LLU"."ID"=472188727 OR "LLU"."ID"=473934937 OR "LLU"."ID"=482567407 OR "LLU"."ID"=484889477 OR "LLU"."ID"=493426267 OR "LLU"."ID"=496272327 OR
  "LLU"."ID"=506452577 OR "LLU"."ID"=516402677 OR "LLU"."ID"=516547247 OR "LLU"."ID"=521305887 OR "LLU"."ID"=522943547 OR "LLU"."ID"=531291017 OR "LLU"."ID"=532816517 OR "LLU"."ID"=536431347 OR
  "LLU"."ID"=536981557 OR "LLU"."ID"=540285967 OR "LLU"."ID"=550883567 OR "LLU"."ID"=565054857 OR "LLU"."ID"=573476197 OR "LLU"."ID"=579825607 OR "LLU"."ID"=585416367 OR "LLU"."ID"=590027057 OR
  "LLU"."ID"=597871307)
* 6 - filter("O"."TRACECODE" IS NULL)
* 7 - access("O"."LOCATION_ID"=4922418)