SQL如下，系统动态生成的，主要是查看符合多个条件付款对应的所有明细记录。语句本身有问题，因为，多个in可以合并成一个in，相关的条件and起来即可，这个暂时忽略。
SELECT doc.* FROM TLK_PAYMENT_DETAIL doc
WHERE ISTMP=0 AND (((((((ITEM_contractnumber = '06603833'))
AND (doc.parent in (select parentdoc.id from TLK_TEPAYMENT_MAIN parentdoc where parentdoc.ITEM_单据类型 IN ('4','8'))))
AND (doc.parent in (select parentdoc.id from TLK_TEPAYMENT_MAIN parentdoc where parentdoc.ITEM_IsReverse = '0')))
AND (doc.parent in (select parentdoc.id from TLK_TEPAYMENT_MAIN parentdoc where parentdoc.stateint = 1048576)))
AND (doc.parent in (select parentdoc.id from TLK_TEPAYMENT_MAIN parentdoc where parentdoc.istmp <> 1)))
AND ((ITEM_expensecode = '33000100')))
执行计划: (第4步是一个笛卡尔集！)
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)|
| 1 | NESTED LOOPS | | 1 | 20853 | 0 (0)|
| 2 | NESTED LOOPS | | 1 | 20338 | 0 (0)|
| 3 | NESTED LOOPS | | 1 | 19823 | 0 (0)|
| 4 | MERGE JOIN CARTESIAN | | 1 | 2008 | 0 (0)|
| 5 | TABLE ACCESS BY INDEX ROWID | TLK_TEPAYMENT_MAIN | 1 | 1004 | 0 (0)|
|* 6 | INDEX SKIP SCAN | TLK_TEPAYMENT_MAIN_06 | 1 | | 0 (0)|
| 7 | BUFFER SORT | | 1 | 1004 | 0 (0)|
| 8 | INLIST ITERATOR | | | | |
| 9 | TABLE ACCESS BY INDEX ROWID| TLK_TEPAYMENT_MAIN | 1 | 1004 | 0 (0)|
|* 10 | INDEX RANGE SCAN | TLK_TEPAYMENT_MAIN_06 | 1 | | 0 (0)|
|* 11 | TABLE ACCESS BY INDEX ROWID | TLK_PAYMENT_DETAIL | 1 | 17815 | 0 (0)|
|* 12 | INDEX RANGE SCAN | TLK_TEPAYMENT_DETAIL_INDEX | 1 | | 0 (0)|
|* 13 | TABLE ACCESS BY INDEX ROWID | TLK_TEPAYMENT_MAIN | 1 | 515 | 0 (0)|
|* 14 | INDEX UNIQUE SCAN | SYS_C006361 | 1 | | 0 (0)|
|* 15 | TABLE ACCESS BY INDEX ROWID | TLK_TEPAYMENT_MAIN | 1 | 515 | 0 (0)|
|* 16 | INDEX UNIQUE SCAN | SYS_C006361 | 1 | | 0 (0)|
-----------------------------------------------------------------------------------------------------
思考了一下，为什么系统选择产生笛卡尔集？笛卡尔集未必是坏事，对小的结果集，这种操作还会省时间，但这里，对两个大的结果集产生笛卡尔集，说明系统认为这两个大的结果集"不大"，也就是说系统的统计信息有误，调用dbms_stats相关的函数gether statistics即可
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/136097/viewspace-1003464/ ，如需转载，请注明出处，否则将追究法律责任。
