您好，
这条语句 T_ERROR_FLOW 与 T_STANDARD_FLOW表各200W数据，其它表数据很小，执行计划不走索引，有没有办法提高执行时间？
谢谢
SELECT
*
FROM
T_STANDARD_FLOW SF
INNER JOIN T_ERROR_FLOW EF ON EF.ERROR_ID = SF. ID
LEFT JOIN T_TRAN_CODE TC ON (
TC.ENCODE = SF.TRAN_CODE
AND TC.SYSTEM_CODE = SF.SYSTEM_ID
AND TC.ORFLAG = SF.ORFLAG
AND SF.CURRENCY = TC.CURRENCY
)
LEFT JOIN T_SYSTEM S ON S.CODE = SF.SYSTEM_ID
AND SF.STATE = '2'
AND (
SF.AMT > 0
OR SF.DISCOUNTAMT > 0
OR SF.AMT < 0
OR SF.DISCOUNTAMT < 0
)
AND SF.PRODUCTID IN (
SELECT DISTINCT
(P .PRODUCT_ID)
FROM
T_PRODUCT_SUBJECT PS
LEFT JOIN T_PRODUCT P ON PS.PRODUCT_ID = P . ID
)
1601407 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1795839580
--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1961K| 1494M| | 17M (2)| 59:44:07 | | |
| 1 | NESTED LOOPS OUTER | | 1961K| 1494M| | 17M (2)| 59:44:07 | | |
|* 2 | HASH JOIN RIGHT OUTER| | 1961K| 725M| | 62096 (1)| 00:12:26 | | |
| 3 | TABLE ACCESS FULL | T_TRAN_CODE | 2505 | 207K| | 11 (0)| 00:00:01 | | |
|* 4 | HASH JOIN | | 1961K| 566M| 207M| 62061 (1)| 00:12:25 | | |
| 5 | TABLE ACCESS FULL | T_ERROR_FLOW | 1961K| 185M| | 7833 (2)| 00:01:35 | | |
| 6 | PARTITION RANGE ALL| | 2120K| 412M| | 22167 (2)| 00:04:27 | 1 |1048575|
| 7 | TABLE ACCESS FULL | T_STANDARD_FLOW | 2120K| 412M| | 22167 (2)| 00:04:27 | 1 |1048575|
| 8 | VIEW | | 1 | 411 | | 9 (0)| 00:00:01 | | |
|* 9 | FILTER | | | | | | | | |
|* 10 | TABLE ACCESS FULL | T_SYSTEM | 1 | 75 | | 3 (0)| 00:00:01 | | |
|* 11 | HASH JOIN | | 1 | 85 | | 6 (0)| 00:00:01 | | |
|* 12 | TABLE ACCESS FULL | T_PRODUCT | 1 | 48 | | 3 (0)| 00:00:01 | | |
| 13 | TABLE ACCESS FULL | T_PRODUCT_SUBJECT | 205 | 7585 | | 3 (0)| 00:00:01 | | |
--------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("SF"."CURRENCY"="TC"."CURRENCY"(+) AND "TC"."ORFLAG"(+)="SF"."ORFLAG" AND
"TC"."SYSTEM_CODE"(+)="SF"."SYSTEM_ID" AND "TC"."ENCODE"(+)="SF"."TRAN_CODE")
4 - access("EF"."ERROR_ID"="SF"."ID")
9 - filter( EXISTS (SELECT 0 FROM "T_PRODUCT" "P","T_PRODUCT_SUBJECT" "PS" WHERE
"PS"."PRODUCT_ID"="P"."ID" AND "P"."PRODUCT_ID"=:B1) AND ("SF"."AMT"<>0 OR "SF"."DISCOUNTAMT"<>0) AND
"SF"."STATE"='2')
10 - filter("S"."CODE"="SF"."SYSTEM_ID")
11 - access("PS"."PRODUCT_ID"="P"."ID")
12 - filter("P"."PRODUCT_ID"=:B1)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
9890560 consistent gets
78609 physical reads
0 redo size
387055582 bytes sent via SQL*Net to client
1174884 bytes received via SQL*Net from client
106762 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1601407 rows processed
执行计划没问题，走全表+hash join是最佳的
但是你的SQL先确认下结果是不是你想要的
on里有过滤条件而不是连接条件时，你一定要清楚这和该条件放在where中的区别是什么。具体区别可以自己建表去体会。
什么是过滤条件呢？如下，都不是连接条件而是过滤条件。过滤条件通常会放在where中，当然，也不是说就不能放在on中，只是你得清楚二者之间的差别，避免得出的结果不是你想要的。
AND SF.STATE = '2'
AND (
SF.AMT > 0
OR SF.DISCOUNTAMT > 0
OR SF.AMT < 0
OR SF.DISCOUNTAMT < 0
)
评论
有用 01、请使用gather_plan_statistics hint判断执行计划与真实执行结果有没有偏差，尤其是T_ERROR_FLOW 与 T_STANDARD_FLOW两个大row source关联后的筛选情况。
2、如果中间结果集体积明显超过优化器预期，那么步骤1的nest loop就有可能不合适，而hash join可能更好,或者使用QB_NAME + cardinality hint人工指示中间结果集大小
3、如果T_ERROR_FLOW 与 T_STANDARD_FLOW两个大row source的首次筛选后的结果集很大，可以考虑强制要求oracle使用小row source（T_SYSTEM）作为筛选条件进行filter，具体方法可以考虑使用no_unnest hint
评论 有用 0
墨值悬赏