最近在做项目的时候有张单表数据量1千两百万多条数据，在查询的时候速度极其的慢，大概120秒才会返回，而且关联了四张表，与业务逻辑相关的，还必须得关联，不然逻辑出错，目前不知道如何进行优化，请各位同仁指点一二谢谢大家，
已加过索引了，还是慢
数据库oracle 后端java 用的分页框架是PageHelper这个
PPS_MBOM_DETAILED 主表，数据大约1千2百万条
sql如下
SELECT
*
FROM
(
SELECT
TMP_PAGE.*,
ROWNUM PAGEHELPER_ROW_ID
FROM
(
SELECT
assembly_item_code,
assembly_item_version,
account_number,
unit,
component_item_code,
component_item_version,
mfqty,
mtype,
mfmrtg,
mfartg,
cfmrtg,
cfartg,
PRICING_TIME,
UNIT_PRICE,
EXPIRATION_TIME,
VALID_STATUS,
START_TIME,
END_TIME,
ITEM_NAME,
PPVMLOC,
PPVBOM_NUM,
PPVSERIALNUM,
GROUP_DESCRIPTIONS,
GROUP_SHORT_NAME
FROM
(
SELECT
pmd.del_flag,
pmd.assembly_item_code,
pmd.assembly_item_version,
pmd.account_number,
pmd.component_item_code,
pmd.component_item_version,
pmd.mfqty,
pmd.unit,
pmd.mtype,
pmd.mfmrtg,
pmd.mfartg,
pmd.cfmrtg,
pmd.cfartg,
ppl.PRICING_TIME,
ppl.UNIT_PRICE,
ppl.EXPIRATION_TIME,
ppl.VALID_STATUS,
ppl.START_TIME AS START_TIME,
ppl.END_TIME AS END_TIME,
ppnm.ITEM_NAME,
ppv.MLOC AS PPVMLOC,
ppv.BOM_NUM AS PPVBOM_NUM,
ppv.SERIALNUM AS PPVSERIALNUM,
ple.GROUP_DESCRIPTIONS,
ple.GROUP_SHORT_NAME,
row_number ( ) over ( partition BY pmd.COMPONENT_ITEM_CODE, ppv.mloc ORDER BY 1 ) rn
FROM
PPS_MBOM_DETAILED pmd
INNER JOIN (
SELECT
a.TOP_PLAN_NUM,
a.MLOC,
a.BOM_NUM,
a.SERIALNUM
FROM
PPS_PLAN_VIN a
INNER JOIN ( SELECT TOP_PLAN_NUM, max( VERSIONS ) AS max_VERSIONS FROM PPS_PLAN_VIN GROUP BY TOP_PLAN_NUM ) b ON a.TOP_PLAN_NUM = b.TOP_PLAN_NUM
AND a.VERSIONS = b.max_VERSIONS
) ppv ON ppv.TOP_PLAN_NUM = pmd.account_number
INNER JOIN pps_parts_name_maintain ppnm ON ppnm.ITEM_NO = pmd.COMPONENT_ITEM_CODE
INNER JOIN pps_parts_library ppl ON pmd.COMPONENT_ITEM_CODE = ppl.COMPONENT_ITEM_CODE
AND ppl.CONTRACT_UNIT = ppv.MLOC
INNER JOIN ( SELECT DISTINCT PROD_LINE_GROUP_CODE, GROUP_DESCRIPTIONS, GROUP_SHORT_NAME FROM PPS_PROD_LINE WHERE GROUP_SHORT_NAME IS NOT NULL ) ple ON ple.PROD_LINE_GROUP_CODE = ppv.MLOC
AND NOT EXISTS ( SELECT 1 FROM PPS_virtual_position pvp WHERE pvp.line = pmd.lineid AND pvp.station = pmd.stationid )
)
WHERE
rn = 1
AND GROUP_SHORT_NAME IS NOT NULL
AND MFMRTG LIKE concat( concat( '%', 'CG' ), '%' )
AND MFARTG LIKE concat( concat( '%', 'ZZ' ), '%' )
AND del_flag = '0'
AND unit_price IS NOT NULL
) TMP_PAGE
)
WHERE
PAGEHELPER_ROW_ID >= 10
AND ROWNUM <= 0;
