weixin_38647584 2022-04-25 17:03 采纳率: 69.2%
浏览 108
已结题

oracle数据库千万数据量分页请求慢,怎么优化sql或者其他方面

最近在做项目的时候有张单表数据量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;

![img](

能提升一秒是一秒

  • 写回答

4条回答 默认 最新

  • 小方qdm 2022-04-25 17:42
    关注

    执行计划截个图看一下

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(3条)

报告相同问题?

问题事件

  • 系统已结题 7月7日
  • 已采纳回答 6月29日
  • 创建了问题 4月25日

悬赏问题

  • ¥15 halcon DrawRegion 提示错误
  • ¥15 FastAPI Uvicorn启动显示404
  • ¥15 centos7.9脚本,怎么排除特定的访问记录
  • ¥15 关于#Django#的问题:我的静态文件呢?
  • ¥15 关于CPLEX的问题,请专家解答
  • ¥15 cocos的点击事件 怎么穿透到 原生fragment上。
  • ¥20 基于相关估计的TDOA算法中的加权最小二乘拟合法matlab仿真
  • ¥20 基于相关估计的TDOA算法中的自适应加权广义互相关法。
  • ¥15 abaqus CAE 2024软件启动问题
  • ¥20 基于相关估计的TDOA算法中的局部互相关函数滤波matlab仿真