如题,下面的SQL怎么最优化?SELECT A.ROW_ID,G.CREATED FROM S_DOC_QUOTE A,
(
SELECT MAX(B.REV_NUM) MAXREV,B.QUOTE_NUM
FROM S_DOC_QUOTE B
GROUP BY B.QUOTE_NUM
) C,
(
SELECT E.RECORD_ID AS RECORD_ID, MAX(E.CREATED) AS CREATED
FROM S_AUDIT_ITEM E
WHERE E.FIELD_NAME = 'Status'
GROUP BY E.RECORD_ID
) G
WHERE A.QUOTE_NUM = C.QUOTE_NUM AND A.REV_NUM = MAXREV AND A.ROW_ID = G.RECORD_ID
(
SELECT MAX(B.REV_NUM) MAXREV,B.QUOTE_NUM
FROM S_DOC_QUOTE B
GROUP BY B.QUOTE_NUM
) C,
(
SELECT E.RECORD_ID AS RECORD_ID, MAX(E.CREATED) AS CREATED
FROM S_AUDIT_ITEM E
WHERE E.FIELD_NAME = 'Status'
GROUP BY E.RECORD_ID
) G
WHERE A.QUOTE_NUM = C.QUOTE_NUM AND A.REV_NUM = MAXREV AND A.ROW_ID = G.RECORD_ID
S_AUDIT_ITEM中有400W条数据我拿出来的只是一个大SQL中的一部分,其实整个大SQL更慢,要不我贴出来看看?
(
SELECT b.*, MAX(B.REV_NUM)(partition by B.QUOTE_NUM ) MAXREV
FROM S_DOC_QUOTE B
) C,
(
SELECT E.RECORD_ID AS RECORD_ID, MAX(E.CREATED) AS CREATED
FROM S_AUDIT_ITEM E
WHERE E.FIELD_NAME = 'Status'
GROUP BY E.RECORD_ID
) G
WHERE C.ROW_ID = G.RECORD_ID
但是不知道是不是真的能快很多
FROM S_DOC_QUOTE A,
(SELECT MAX(B.REV_NUM) MAXREV, B.QUOTE_NUM FROM S_DOC_QUOTE B GROUP BY B.QUOTE_NUM) C,
(SELECT E.RECORD_ID AS RECORD_ID, MAX(E.CREATED) AS CREATED
FROM S_AUDIT_ITEM E
WHERE E.FIELD_NAME = 'Status'
GROUP BY E.RECORD_ID) G
WHERE A.QUOTE_NUM = C.QUOTE_NUM
AND A.REV_NUM = C.MAXREV
AND A.ROW_ID = G.RECORD_ID
1、针对 S_AUDIT_ITEM 建索引,并确认走了索引
2、建临时表存储数据,在分步查询
SELECT MAX(E.CREATED) CREATED
FROM S_AUDIT_ITEM E
WHERE E.FIELD_NAME = 'Status' and e.RECORD_ID=a.ROW_ID
GROUP BY E.RECORD_ID
) CREATED
from S_DOC_QUOTE A,
(
SELECT MAX(B.REV_NUM) MAXREV,B.QUOTE_NUM
FROM S_DOC_QUOTE B
GROUP BY B.QUOTE_NUM
) C
WHERE A.QUOTE_NUM = C.QUOTE_NUM AND A.REV_NUM = MAXREV试试这个~~~
where语句里面,能过滤掉最多记录的条件放最后。关注。
A.ROW_ID,
MAX(B.CREATED) AS CREATED
FROM
(
SELECT ROW_ID,
REV_NUM,
MAX(REV_NUM) OVER(PARTITION BY QUOTE_NUM) AS MAX_REV
FROM S_DOC_QUOTE
) A
JOIN S_AUDIT_ITEM B ON A.ROW_ID=B.RECORD_ID
WHERE A.REV_NUM=A.MAX_REV
SELECT
A.ROW_ID,
MAX(B.CREATED) AS CREATED
FROM
(
SELECT ROW_ID,
REV_NUM,
MAX(REV_NUM) OVER(PARTITION BY QUOTE_NUM) AS MAX_REV
FROM S_DOC_QUOTE
) A
JOIN S_AUDIT_ITEM B ON A.ROW_ID=B.RECORD_ID AND B.FIELD_NAME = 'Status'
WHERE A.REV_NUM=A.MAX_REV
我会另开一个帖子,把整个SQL贴出来
2、查询的时候最好按需查询,比如分页的概念,或大表分离到多个小表
3、查询SQL优化技巧,根据里面的一些技巧做进一步的优化,应该有用的,我同事尝试过,查询速度明星快,