100分求优化SQL

sjlzcj 2011-07-08 09:20:50
select aa.workitem_id,aa.formset_inst_id,bb.receive_time,aa.complete_time from 
(select a.workitem_id,a.formset_inst_id,a.complete_time from mv_workitem a where workitem_id in (
select max(t.workitem_id) from mv_workitem t where t.formset_id=347 and t.receiver_dept='总裁办公室(股份)' and t.app_type_name='部门阅办文件' group by t.formset_inst_id) order by workitem_id)aa,
(select a.workitem_id,a.formset_inst_id,a.receive_time from mv_workitem a where a.receive_time in(
select min(t.receive_time)
from mv_workitem t where t.formset_id=347 and t.receiver_dept='总裁办公室(股份)' and t.app_type_name='部门阅办文件' group by t.formset_inst_id))bb where aa.workitem_id=bb.workitem_id


查出结果105条,目前在plsql上看到的时间 1.15-1.25秒之间


刚才发的忘设置分数了 重发一个 2个一起给 一共140分
...全文
210 13 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
13 条回复
切换为时间正序
请发表友善的回复…
发表回复
hudingchen 2011-07-08
  • 打赏
  • 举报
回复

SELECT m.workitem_id,
m.formset_inst_id,
m.complete_time,
m.receive_time
FROM (SELECT t.workitem_id,
t.formset_inst_id,
t.complete_time,
t.receive_time,
RANK() OVER(PARTITION BY t.formset_inst_id ORDER BY t.workitem_id DESC) workitem_id_rn,
RANK() OVER(PARTITION BY t.formset_inst_id ORDER BY t.receive_time ASC) receive_time_rn
FROM mv_workitem t
WHERE t.formset_id = 347
AND t.receiver_dept = '总裁办公室(股份)'
AND t.app_type_name = '部门阅办文件'
) m
WHERE m.workitem_id_rn = m.receive_time_rn
luoyoumou 2011-07-08
  • 打赏
  • 举报
回复
-- 你的原语句:
select aa.workitem_id,
aa.formset_inst_id,
bb.receive_time,
aa.complete_time
from (select a.workitem_id,
a.formset_inst_id,
a.complete_time
from mv_workitem a
where workitem_id in (select max(t.workitem_id)
from mv_workitem t
where t.formset_id=347
and t.receiver_dept='总裁办公室(股份)'
and t.app_type_name='部门阅办文件'
group by t.formset_inst_id )
order by workitem_id) aa,
(select a.workitem_id,
a.formset_inst_id,
a.receive_time
from mv_workitem a
where a.receive_time in(select min(t.receive_time)
from mv_workitem t
where t.formset_id=347
and t.receiver_dept='总裁办公室(股份)'
and t.app_type_name='部门阅办文件'
group by t.formset_inst_id) ) bb
where aa.workitem_id=bb.workitem_id;

-- 下面是我略加修改的语句:(你去测试一下:看执行结果是否一样?效率如何?)

WITH a1 AS(select a.workitem_id, a.formset_inst_id, a.receive_time, a.complete_time
from mv_workitem a
where a.formset_id=347
and a.receiver_dept='总裁办公室(股份)'
and a.app_type_name='部门阅办文件' ),
a2 AS(select max(t1.workitem_id) workitem_id from a1 t1 group by t1.formset_inst_id),
a3 AS(select t2.workitem_id, t2.formset_inst_id, t2.complete_time
from a1 t2
where t2.workitem_id in (select a2.workitem_id from a2),
a3 AS(selete t3.workitem_id, t3.receive_time
from a1 t3
where t3.receive_time in(select min(t4.receive_time)
from a1 t4
group by t4.formset_inst_id)
select a1.workitem_id, a1.formset_inst_id, a3.receive_time, a1.complete_time
from a1, a3
where a1.workitem_id=a3.workitem_id;
「已注销」 2011-07-08
  • 打赏
  • 举报
回复
没测试,你自己测试下看看
 ;WITH cte AS (select max(t.workitem_id) maxWid, min(t.receive_time) minRtime
from mv_workitem t
where t.formset_id = 347
and t.receiver_dept = '总裁办公室(股份)'
and t.app_type_name = '部门阅办文件'
group by t.formset_inst_id
)
SELECT a.workitem_id, a.formset_inst_id, a.complete_time
FROM mv_workitem a,cte c
WHERE workitem_id= c.maxWid AND receive_time =c.minRtime
ORDER BY workitem_id
「已注销」 2011-07-08
  • 打赏
  • 举报
回复
给出测试数据
iqlife 2011-07-08
  • 打赏
  • 举报
回复
select max(t.workitem_id) from mv_workitem t where t.formset_id=347 and t.receiver_dept='总裁办公室(股份)' and t.app_type_name='部门阅办文件' group by t.formset_inst_id) order by workitem_id)aa,
(select a.workitem_id,a.formset_inst_id,a.receive_time from mv_workitem a where a.receive_time in(
select min(t.receive_time)
from mv_workitem t where t.formset_id=347 and t.receiver_dept='总裁办公室(股份)' and t.app_type_name='部门阅办文件' group by t.formset_inst_id))bb

你的取最大和最小,就是取头尾2条记录的一个字段,可以直接用FIRST_VALUE,和LAST_VALUE句SQL实现,具体效率实际情况为主



iqlife 2011-07-08
  • 打赏
  • 举报
回复
大致看了下,
1:in 改为EXISTS
2:内层的ORDER BY 可以去除 order by workitem_id
zn85600301 2011-07-08
  • 打赏
  • 举报
回复
只贴语句 没有执行计划 怎么调优
tangren 2011-07-08
  • 打赏
  • 举报
回复
至少应该提供执行计划,索引建立的情况,表中数据量大小等等
BenChiM888 2011-07-08
  • 打赏
  • 举报
回复

--第二句最后修改下
GROUP BY FORMSET_INST_ID;

BenChiM888 2011-07-08
  • 打赏
  • 举报
回复

--从你的sql分析,
-- 先找到最大的 WORKITEM_ID 对应的信息
-- 再找到最小的 RECEIVE_TIME 对应的信息
-- 然后两组信息通过 WORKITEM_ID 再关联
-- 实际上相当于同时找 WORKITEM_ID 最大 和 RECEIVE_TIME 最小的信息,因此改造如下

SELECT WORKITEM_ID, FORMSET_INST_ID, COMPLETE_TIME, RECEIVE_TIME
FROM MV_WORKITEM A
WHERE (WORKITEM_ID, RECEIVE_TIME) IN
(SELECT MAX(T.WORKITEM_ID), MIN(T.RECEIVE_TIME)
FROM MV_WORKITEM T
WHERE T.FORMSET_ID = 347
AND T.RECEIVER_DEPT = '总裁办公室(股份)'
AND T.APP_TYPE_NAME = '部门阅办文件'
GROUP BY T.FORMSET_INST_ID);

--另外如果从业务逻辑看(猜测)
--猜测你想找的数据是,对于每一个 FORMSET_INST_ID ,有接收时间(RECEIVE_TIME)
----有不同的处理环节(WORKITEM_ID)
----每一个处理环节有完成时间(COMPLETE_TIME)
----所以猜测你想找的数据是
------对每一个FORMSET_INST_ID,最早收到的,最大处理环节 的信息
--测试下下面的语句是否满足你的要求吧
SELECT FORMSET_INST_ID,
MAX(COMPLETE_TIME)KEEP(DENSE_RANK LAST ORDER BY WORKITEM_ID)COMPLETE_TIME
MAX(WORKITEM_ID)KEEP(DENSE_RANK FIRST ORDER BY RECEIVE_TIME)WORKITEM_ID,
MIN(RECEIVE_TIME)KEEP(DENSE_RANK FIRST ORDER BY RECEIVE_TIME)RECEIVE_TIME
FROM MV_WORKITEM A
WHERE T.FORMSET_ID = 347
AND T.RECEIVER_DEPT = '总裁办公室(股份)'
AND T.APP_TYPE_NAME = '部门阅办文件'
GROUP BY FORMSET_INST_ID,COMPLETE_TIME;

kingtiy 2011-07-08
  • 打赏
  • 举报
回复
in 改成exists
hudingchen 2011-07-08
  • 打赏
  • 举报
回复
少个条件,改下

SELECT m.workitem_id,
m.formset_inst_id,
m.complete_time,
m.receive_time
FROM (SELECT t.workitem_id,
t.formset_inst_id,
t.complete_time,
t.receive_time,
RANK() OVER(PARTITION BY t.formset_inst_id ORDER BY t.workitem_id DESC) workitem_id_rn,
RANK() OVER(PARTITION BY t.formset_inst_id ORDER BY t.receive_time ASC) receive_time_rn
FROM mv_workitem t
WHERE t.formset_id = 347
AND t.receiver_dept = '总裁办公室(股份)'
AND t.app_type_name = '部门阅办文件'
) m
WHERE m.workitem_id_rn = 1
AND m.receive_time_rn = 1
更优更快 人工智能自动SQL优化----------http://www.sina.com.cn 2001/12/12 17:48 中国电脑教育报文/SQL爱好者  所谓SQL,就是指Structured Query Language(结构化查询语言),它是目前使用最广泛的数据库语言,用来和数据库打交道,从数据库中得到用户需要的数据。但是要想熟练使用SQL语句,也不是一件简单的事,有些语句使用起来也比较麻烦。如果我们对SQL语句进行优化,那么用户使用起来 就会方便许多。  简单来说,SQL语句的优化就是将性能低下的SQL语句转换成达到同样目的的性能优异的SQL语句。人工智能自动SQL优化就是使用人工智能技术,自动对SQL语句进行重写,找到性能最好的等效SQL语句。  人工智能自动SQL 优化  随着人工智能技术的发展和在数据库优化领域应用的深入,在20世纪90年代末终于出现了突破性的进展——人工智能自动SQL优化。目前在商用数据库领域LECCO TechnologyLimited(灵高公司)拥有该技术并提供使用该技术的自动优化产品——LECCO SQL Expert,其支持Oracle、Sybase、MS SQLServer和IBMDB2数据库平台。该产品针对数据库应用的开发和维护阶段提供了几个特别的模块:SQL语法优化器、PL/SQL集成化开发调试环境(IDE)、扫描器、数据库监视器等。图1 人工智能自动SQL优化示意图  其核心模块之一“SQL语法优化器”的工作原理大致如下(如图1):  一条源SQL语句输入→“人工智能反馈式搜索引擎”对输入的SQL语句结合检测到的数据库结构和索引进行重写,产生N条等效的SQL语句输出→产生的N条等效SQL语句再送入“人工智能反馈式搜索引擎”进行重写,直至无法产生新的输出或搜索限额满→对 输出的SQL语句进行过滤,选出具有不同执行计划的SQL语句(即不同的执行效率)→对得到的SQL语句进行批量测试,找出性能最好的SQL语句。图2 优化前的SQL语句  自动优化实例  假设我们从源代码中抽取出这条SQL语句(如图2):  SELECTCOUNT(*)FROMEMPLOY-EE WHEREEXISTS(SELECT'X'FROM DEPARTMENTswheresEMP_DEPT=DPT_IDAND DPT_NAME LIKE'AC%')AND EMP_IDIN(SELECT SAL_EMP_IDFROM EMP_SAL_HISTB WHERESAL_SALARY>70000)   按“优化”按钮后,经过十几秒,SQL Expert就完成了优化的过程,从优化细节中可以看到,它在十几秒的时间内重写产生了2267条等价的SQL语句,其中136条SQL语句有不同的执行计划(如图3)。图3 优化结果  接下来我们可以对自动重写产生的136条具有不同执行计划的SQL语句进行批运行测试,以选出性能最佳的等效SQL语句。按下“批运行”按钮,在“终止条件”页选择“最佳运行时间SQL语句”(如图4),按“确定”。图4 测试条件  经过几分钟的测试运行后,我们可以发现SQL124的运行时间和反应时间最短。运行速度约有22.75倍的提升(源SQL语句运行时间为2.73秒,SQL124运行时间为0.12秒,如图5)。图5 测试结果  我们把SQL124放入源代码中,结束一条SQL语句的优化工作。从上例可以看到,LECCO SQL Expert的自动重写技术使原来需要几小时才能完成的SQL语句的优化工作,缩减到几分钟之内就可以完成。数据库管理员和开发人员可以从繁重的SQL语句优化工作中解脱出来。  边做边学式训练  LECCO SQL Expert不仅能够找到最佳的SQL语句,而且提供的“边做边学式训练”还能够教会开发人员和数据库管理员如何写出性能最好的SQL语句。LECCO SQL Expert的“SQL比较器”可以标明源SQL和待选SQL之间的不同之处。LECCO SQL Expert详尽的上下文敏感帮助系统可以指出执行计划的深层含义。图6 源语句与SQL124的比较  以上面优化的结果为例,为了查看源SQL语句和SQL124在写法上的不同,我们可以按下“比较器”按钮,对SQL124和源SQL语句进行比较。如果选择“双向比较”复选框,“SQL比较器”可以将两条互相间的不同之处以蓝色表示。当然,你也可以从 源语句和重写后的SQL语句中任选两条进行比较(如图6)。  从比较的结果可以看到,重写得到的SQL124把第一个Exists改写成了In;在字段DPT_ID上进行了合并空字符串的操作以诱导数据库先执行子查询中的(SELECTDPT_ID||'FROMDEPART-MENTWH

17,382

社区成员

发帖
与我相关
我的任务
社区描述
Oracle 基础和管理
社区管理员
  • 基础和管理社区
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧