碰上这种语句我已经要崩溃了，再看那执行计划，我疯了！

碰上这种语句我已经要崩溃了，再看那执行计划，我疯了！[@more@]ORACLE100203，Windows 2003(AMD64)



Insert into TYBb2JxcKcqc ( invid, orgid, storclass, cwarehouseid, num )
( SELECT b.cinvbasid invid, h.pk_calbody orgid, bd_stordoc.def1 storclass, h.cwarehouseid,
nvl ( SUM ( b.ninnum ), 0 )- nvl ( SUM ( b.noutnum ), 0 ) nnum
FROM ic_general_b b, ic_general_h h, bd_stordoc, bd_invbasdoc
where h.cgeneralhid = b.cgeneralhid
and h.cwarehouseid = bd_stordoc.pk_stordoc
and b.cinvbasid = bd_invbasdoc.pk_invbasdoc
and ( ( ( ( h.cbilltypecode = '40' or
h.cbilltypecode = '41' or
h.cbilltypecode = '4E' or
h.cbilltypecode = '44' or
( h.cbilltypecode = '45' and b.fchecked = 0 and
( h.cbiztype in ( select pk_busitype from bd_busitype where verifyrule <> 'J' )
or h.cbiztype is null )
) or h.cbilltypecode ='46' or h.cbilltypecode = '47' or h.cbilltypecode = '48'
or h.cbilltypecode = '49' or h.cbilltypecode = '4A' or h.cbilltypecode= '4B'
) AND ninnum IS NOT NULL or
( ( h.cbilltypecode = '4C' and
( h.cbiztype in
( select pk_busitype from bd_busitype where verifyrule <> 'C' ) or h.cbiztype is null
)
) or h.cbilltypecode = '4D' or h.cbilltypecode = '4Y' or
h.cbilltypecode = '4F' or h.cbilltypecode = '4G' or
h.cbilltypecode = '4H' or h.cbilltypecode = '4I' or
h.cbilltypecode = '4J' or h.cbilltypecode = '4O'
) AND noutnum IS NOT NULL
) and h.dr = 0 and b.dr = 0 and h.cgeneralhid = b.cgeneralhid
) and ( ( h.cbilltypecode = '40' or h.cbilltypecode = '41' or
h.cbilltypecode = '44' )
or ( b.dbizdate < '2007-07-25' )
) and ( 1 = 1 ) and ( 1 = 1 ) and ( 1 = 1 ) and
( h.cwarehouseid = '1006AA100000000003CM' ) and
( 1 = 1 ) and ( 1 =1 ) and ( 1 = 1 ) and
( h.pk_calbody IN ( Select pk_calbody from TYBb2JxcBD1 ) )
and ( bd_invbasdoc.pk_invcl IN ( Select pk_invcl from TYBb2JxcBD2 ) )
) GROUP BY b.cinvbasid, h.pk_calbody, bd_stordoc.def1, h.cwarehouseid )


Optimizer Plan:
-----------------------------------------------------------------------------------------------
| 　　 Operation 　 | 　 　PHV/Object Name　 | Rows | Bytes | Cost |
-----------------------------------------------------------------------------------------------
|INSERT STATEMENT |--------3140510875--------| | | 5163 |
|HASH GROUP BY | | 1 | 263 | 5163 |
| FILTER | | | | |
| NESTED LOOPS | | 1 | 263 | 5162 |
| NESTED LOOPS | | 1K| 256K| 4572 |
| NESTED LOOPS SEMI | | 1 | 115 | 872 |
| MERGE JOIN CARTESIAN | | 1 | 93 | 871 |
| NESTED LOOPS | | 3K| 272K| 4 |
| MAT_VIEW ACCESS BY INDEX ROWID |BD_STORDOC | 1 | 29 | 1 |
| INDEX UNIQUE SCAN |PK_BD_STORDOC | 1 | | 1 |
| INDEX FULL SCAN |I_BD_INVBASDOC_JSZC_04 | 3K| 161K| 3 |
| BUFFER SORT | | 1 | 22 |18446744|
| SORT UNIQUE | | 1 | 22 | 0 |
| TABLE ACCESS FULL |TYBB2JXCBD1 | 1 | 22 | 0 |
| INDEX RANGE SCAN |I_TYBB2JXCBD2 | 1K| 22K| 1 |
| MAT_VIEW ACCESS BY INDEX ROWID |IC_GENERAL_B | 1K| 90K| 3700 |
| INDEX RANGE SCAN |I_IC_GENERAL_B_13 | 10K| | 5 |
| MAT_VIEW ACCESS BY INDEX ROWID |IC_GENERAL_H | 1 | 85 | 1 |
| INDEX UNIQUE SCAN |PK_IC_GENERAL_H | 1 | | 1 |
| MAT_VIEW ACCESS BY INDEX ROWID |BD_BUSITYPE | 1 | 23 | 1 |
| INDEX UNIQUE SCAN |PK_BD_BUSITYPE | 1 | | 1 |
| MAT_VIEW ACCESS BY INDEX ROWID |BD_BUSITYPE | 1 | 23 | 1 |
| INDEX UNIQUE SCAN |PK_BD_BUSITYPE | 1 | | 1 |
-----------------------------------------------------------------------------------------------

我看不懂执行计划的第1步：BD_STORDOC 如何和　I_BD_INVBASDOC_JSZC_04 作嵌套循环，语句里它们根本就没关系的！

这是两表的索引！
Enter value for tname: BD_STORDOC
old 3: and b.table_name=upper(trim('&tname')) and b.index_name=a.index_name(+)
new 3: and b.table_name=upper(trim('BD_STORDOC')) and b.index_name=a.index_name(+)

CONSTRAINT_NAME C INDEX_NAME COLUMN_NAME COLUMN_POSITION
------------------------- - ------------------------------ -------------------- ---------------
I_BD_STORDOC_1 PK_CORP 1
I_BD_STORDOC_1 STORCODE 2
I_BD_STORDOC_2 PK_CORP 1
I_BD_STORDOC_2 STORNAME 2
PK_BD_STORDOC P PK_BD_STORDOC PK_STORDOC 1

Enter value for tname: BD_INVBASDOC
old 3: and b.table_name=upper(trim('&tname')) and b.index_name=a.index_name(+)
new 3: and b.table_name=upper(trim('BD_INVBASDOC')) and b.index_name=a.index_name(+)

CONSTRAINT_NAME C INDEX_NAME COLUMN_NAME COLUMN_POSITION
------------------------- - ------------------------------ -------------------- ---------------
I_BD_BAS_GRAPHID GRAPHID 1
I_BD_INVBASDOC_JSZC_04 PK_INVCL 1
I_BD_INVBASDOC_JSZC_04 PK_INVBASDOC 2
I_BD_INVBAS_1 INVCODE 1
I_BD_INVBAS_1 PK_CORP 2


来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/594576/viewspace-928335/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/594576/viewspace-928335/