oracle数据库 大数据量的sql优化

SELECT
frp.flow_id AS flowId,
frp. SID AS frpsid,
frp.run_id AS runId,
TO_CHAR (frd21.DATA_10) AS title,
TO_CHAR (
frd21.DATA_4 || '〔' || frd21.DATA_6 || '〕' || frd21.DATA_7
) AS docId,
TO_CHAR (frd21.DATA_8) AS dense,
TO_CHAR (frd21.DATA_2) AS urgency,
frp.flag AS state,
fr.begin_time AS startdate
FROM
flow_run_prcs frp,
flow_run fr,
doc_flow_view frd21
WHERE
frp.run_id = fr.run_id AND frp.run_id = frd21.run_id AND
(
frp.run_id = fr.run_id
AND frp.run_id = frd21.run_id
AND frp.flag > 2
AND TO_CHAR (frd21.DATA_8) IS NULL
OR frp.run_id = fr.run_id
AND frp.run_id = frd21.run_id
AND frp.flag > 2
AND TO_CHAR (frd21.DATA_8) = ''
OR frp.run_id = fr.run_id
AND frp.run_id = frd21.run_id
AND frp.flag > 2
AND TO_CHAR (frd21.DATA_8) = '空'
)
AND frp. SID = (
SELECT
MIN (tmp. SID)
FROM
flow_run_prcs tmp
WHERE
tmp.run_id = frp.run_id
)
ORDER BY
SID DESC

创建一个视图就可以提高查询效率啦

创建索引,或者分库分表,创建中间表啊什么的

这种复杂查询，如果数据量特别大，创建视图和索引以及分表有时候并不能解决类似统计查询的问题。如果这个数据并不是要求实时数据，建议楼主建立一个统计表，每个一定时间利用楼主的sql统计出数据，之后就可以用统计表的数据进行业务处理了！