SQL优化，大神看下

[qsc1818518]

SELECT KEYWORD, Fno, Fdate, OPERATIONLOG, OPDATE, OPID, bid
FROM (
SELECT KEYWORD, SUBSTR(dbms_lob.substr(OPERATIONLOG), 12, 6) AS Fno,
SUBSTR(dbms_lob.substr(OPERATIONLOG), 19, 10) AS Fdate, OPERATIONLOG, OPDATE, OPID,
(
SELECT COUNT(*) AS Expr1
FROM ODS_Tbl_Operationlog_001
WHERE (KEYWORD = tbl.KEYWORD)
AND (LOGID <= tbl.LOGID)
AND (OPERATIONLOG LIKE '%OffLoad:AI%')
AND
(OPDATE >= TO_DATE('2013-01-01 00:00:00','yyyy-mm-dd HH24:mi:ss'))
AND (OPDATE <= TO_DATE('2020-12-31 23:59:59','yyyy-mm-dd HH24:mi:ss'))
) AS bid
FROM ODS_Tbl_Operationlog_001 tbl
where (OPERATIONLOG LIKE '%OffLoad:AI%')
AND
(OPDATE >= TO_DATE('2013-01-01 00:00:00','yyyy-mm-dd HH24:mi:ss'))
AND (OPDATE <= TO_DATE('2020-12-31 23:59:59','yyyy-mm-dd HH24:mi:ss'))
)


数据量一千多万 查询七八分钟 走两遍全表

[ZJHZ_叶]

(
SELECT COUNT(*) AS Expr1
FROM ODS_Tbl_Operationlog_001
WHERE (KEYWORD = tbl.KEYWORD)
AND (LOGID <= tbl.LOGID)
AND (OPERATIONLOG LIKE '%OffLoad:AI%')
AND
(OPDATE >= TO_DATE('2013-01-01 00:00:00','yyyy-mm-dd HH24:mi:ss'))
AND (OPDATE <= TO_DATE('2020-12-31 23:59:59','yyyy-mm-dd HH24:mi:ss'))
) AS bid

改成下面这个看下

count(*) OVER(PARTITION BY KEYWORD ORDER BY LOGID )
AS bid

[qsc1818518]

dense_rank() over (partition by KEYWORD order by id 这 怎么样

[ZJHZ_叶]

如果是要排名的话可以的

[AHUA1001]

SELECT KEYWORD, SUBSTR(dbms_lob.substr(OPERATIONLOG), 12, 6) AS Fno, SUBSTR(dbms_lob.substr(OPERATIONLOG), 19, 10) AS Fdate, OPERATIONLOG, OPDATE, OPID, ( SELECT COUNT(0) AS Expr1 FROM ODS_Tbl_Operationlog_001 WHERE KEYWORD = tbl.KEYWORD AND LOGID <= tbl.LOGID AND OPERATIONLOG LIKE '%OffLoad:AI%' AND OPDATE BETWEEN TO_DATE('2013-01-01 00:00:00','yyyy-mm-dd HH24:mi:ss') AND TO_DATE('2020-12-31 23:59:59','yyyy-mm-dd HH24:mi:ss') ) AS bid FROM ODS_Tbl_Operationlog_001 tbl where OPERATIONLOG LIKE '%OffLoad:AI%' AND OPDATE BETWEEN TO_DATE('2013-01-01 00:00:00','yyyy-mm-dd HH24:mi:ss') AND TO_DATE('2020-12-31 23:59:59','yyyy-mm-dd HH24:mi:ss')