有一个查询两个表中记录是否匹配的查询select* from
(SELECT
(
CASE WHEN EXISTS
(
SELECT * FROM CLEAR.TC_HIS_FTR_MATCH n
WHERE m.TRADE_DATE = n.TRADE_DATE
AND m.MATCH_NO = TO_NUMBER(n.MATCH_NO)
AND ((m.BS_FLAG = '买' AND n.BS_FLAG = 1)
OR (m.BS_FLAG = '卖' AND n.BS_FLAG = 3))
AND m.MEMBER_ID = n.MEMBER_ID
AND m.CLIENT_ID = n.CLIENT_ID
AND m.CONTRACT_ID = n.CONTRACT_ID
AND m.QTY = n.QTY
AND m.PRICE = n.PRICE
AND m.MATCH_TIME = n.MATCH_TIME
AND ((m.EO_FLAG = '开仓' AND n.EO_FLAG = 1)
OR (m.EO_FLAG = '平仓' AND (n.EO_FLAG = 2OR n.EO_FLAG = 4))
OR (m.EO_FLAG = '强减' AND n.EO_FLAG = 3)
OR (m.EO_FLAG = '强平' AND n.EO_FLAG = 4))
AND n.TRADE_DATE = '20071211' ) THEN 1
ELSE 2 END
) AS MATCH_FLAG
FROM CLEAR.TC_ORIGIN_MATCH m
WHERE m.TRADE_DATE = '20071211'
)where MATCH_FLAG=2;
)case的作用是如果匹配则为1,如果不匹配则为2.
查询的结果集中有约30万条数据,从中找出MATCH_FLAG为2的很慢。
也就是这样的一个select结果集,大概几十万条的数据,其中只有几条或者没有,是自己需要查询出来的(MATCH_FLAG为2的记录),怎么把它们最快的找出来呢?谢谢
(SELECT
(
CASE WHEN EXISTS
(
SELECT * FROM CLEAR.TC_HIS_FTR_MATCH n
WHERE m.TRADE_DATE = n.TRADE_DATE
AND m.MATCH_NO = TO_NUMBER(n.MATCH_NO)
AND ((m.BS_FLAG = '买' AND n.BS_FLAG = 1)
OR (m.BS_FLAG = '卖' AND n.BS_FLAG = 3))
AND m.MEMBER_ID = n.MEMBER_ID
AND m.CLIENT_ID = n.CLIENT_ID
AND m.CONTRACT_ID = n.CONTRACT_ID
AND m.QTY = n.QTY
AND m.PRICE = n.PRICE
AND m.MATCH_TIME = n.MATCH_TIME
AND ((m.EO_FLAG = '开仓' AND n.EO_FLAG = 1)
OR (m.EO_FLAG = '平仓' AND (n.EO_FLAG = 2OR n.EO_FLAG = 4))
OR (m.EO_FLAG = '强减' AND n.EO_FLAG = 3)
OR (m.EO_FLAG = '强平' AND n.EO_FLAG = 4))
AND n.TRADE_DATE = '20071211' ) THEN 1
ELSE 2 END
) AS MATCH_FLAG
FROM CLEAR.TC_ORIGIN_MATCH m
WHERE m.TRADE_DATE = '20071211'
)where MATCH_FLAG=2;
)case的作用是如果匹配则为1,如果不匹配则为2.
查询的结果集中有约30万条数据,从中找出MATCH_FLAG为2的很慢。
也就是这样的一个select结果集,大概几十万条的数据,其中只有几条或者没有,是自己需要查询出来的(MATCH_FLAG为2的记录),怎么把它们最快的找出来呢?谢谢
from
(
select a.n_id,b.c_flag as flagnull
from t_ydxc_sta_charge_temp a,t_ydxc_sta_charge_map b
where a.n_id =b.n_tempid(+)
)va
where va.flagnull is nulla 表n_id,n_xx b表 n_tempid c_flag
1 1 1 1
2 2 2 2
3 3
4 4
上面查询可以得到
3
4
如果把查询条件改成where va.flagnull is not null
查询结果为
1
2
2次的全表扫描好像很费时间,有办法优化么Execution Plan
----------------------------------------------------------
Plan hash value: 3336792957--------------------------------------------------------------------------------
-------------------------| Id | Operation | Name | Rows | Bytes
| Cost (%CPU)| Time |--------------------------------------------------------------------------------
-------------------------| 0 | SELECT STATEMENT | | 142K| 8751K
| 540 (2)| 00:00:07 ||* 1 | FILTER | | |
| | ||* 2 | TABLE ACCESS BY INDEX ROWID | TC_HIS_FTR_MATCH | 1 | 57
| 4 (0)| 00:00:01 ||* 3 | INDEX RANGE SCAN | IDX_HIS_FUTURES_MATCH_1 | 1 |
| 3 (0)| 00:00:01 ||* 4 | FILTER | | |
| | ||* 5 | TABLE ACCESS FULL | TC_ORIGIN_MATCH | 142K| 8751K
| 536 (2)| 00:00:07 ||* 6 | FILTER | | |
| | ||* 7 | TABLE ACCESS BY INDEX ROWID| TC_HIS_FTR_MATCH | 1 | 57
| 4 (0)| 00:00:01 ||* 8 | INDEX RANGE SCAN | IDX_HIS_FUTURES_MATCH_1 | 1 |
| 3 (0)| 00:00:01 |--------------------------------------------------------------------------------
-------------------------
Predicate Information (identified by operation id):
--------------------------------------------------- 1 - filter(:B1='20071211')
2 - filter("N"."MATCH_TIME"=:B1 AND "N"."PRICE"=:B2 AND "N"."QTY"=:B3 AND
"N"."CONTRACT_ID"=:B4 AND (:B5='Br' AND TO_NUMBER("N"."BS_FLAG")=1
OR :B6='Bt' AND TO_NUMBER("N"."BS_FLAG")=3) AND (:B7='F=2V' AND (TO_NUMBER("N"."EO
_FLAG")=2 OR TO_NUMBER("N"."EO_FLAG")=4) OR :B8='?*2V' AND TO_NUMBER("N"."EO_FL
AG")=1 OR :B9='G?<u' AND TO_NUMBER("N"."EO_FLAG")=3 OR :B10='G?F=' AND TO_NUMBER("N"."EO_FL
AG")=4) AND TO_NUMBER(TO_CHAR("N"."MATCH_NO"))=TO_NUMBER(:B11))
3 - access("N"."TRADE_DATE"=:B1 AND "N"."MEMBER_ID"=:B2 AND "N"."CLIENT_ID"=:
B3) 4 - filter(LNNVL( EXISTS (SELECT /*+ */ 0 FROM "CLEAR"."TC_HIS_FTR_MATCH" "N"
WHERE :B1='20071211' AND "N"."CLIENT_ID"=:B2 AND "N"."MEMBER_ID"=:B3 AND
"N"."TRADE_DATE"=:B4 AND "N"."MATCH_TIME"=:B5 AND "N"."PRICE"=:B6 AND "N"."QTY"=:B7 AND "N"
."CONTRACT_ID"=:B8 AND (:B9='Br' AND TO_NUMBER("N"."BS_FLAG")=1 OR :B10='Bt' AND TO_NUMBE
R("N"."BS_FLAG")=3) AND (:B11='F=2V' AND (TO_NUMBER("N"."EO_FLAG")=2 OR TO_NUMBER("N"."EO_
FLAG")=4) OR :B12='?*2V' AND TO_NUMBER("N"."EO_FLAG")=1 OR :B13='G?<u' AND TO_NUMBER("N"."EO_FL
AG")=3 OR :B14='G?F=' AND TO_NUMBER("N"."EO_FLAG")=4) AND TO_NUMBER(TO_CHAR("N"."MATCH_NO"))
=TO_NUMBER(:B15)))) 5 - filter("M"."TRADE_DATE"='20071211')
6 - filter(:B1='20071211')
7 - filter("N"."MATCH_TIME"=:B1 AND "N"."PRICE"=:B2 AND "N"."QTY"=:B3 AND
"N"."CONTRACT_ID"=:B4 AND (:B5='Br' AND TO_NUMBER("N"."BS_FLAG")=1
OR :B6='Bt' AND TO_NUMBER("N"."BS_FLAG")=3) AND (:B7='F=2V' AND (TO_NUMBER("N"."EO
_FLAG")=2 OR TO_NUMBER("N"."EO_FLAG")=4) OR :B8='?*2V' AND TO_NUMBER("N"."EO_FL
AG")=1 OR :B9='G?<u' AND TO_NUMBER("N"."EO_FLAG")=3 OR :B10='G?F=' AND TO_NUMBER("N"."EO_FL
AG")=4) AND TO_NUMBER(TO_CHAR("N"."MATCH_NO"))=TO_NUMBER(:B11))
8 - access("N"."TRADE_DATE"=:B1 AND "N"."MEMBER_ID"=:B2 AND "N"."CLIENT_ID"=:
B3)