急求，一条sql语句的优化

vber1010 2010-04-14 02:18:12 


select zzny.facility_id,

       zzny.u_name,

       zzny.nf year,

       zzny.yd month,

       nvl(hgl.lckhgl, 0)*100 lckhgl

  from (select facility_id, u_name, year, month, yxts, total, bhgs, lckhgl

          from ZZ_LIMS_Y_LCKHGL) hgl,

       (select zz.facility_id, zz.u_name, ny.nf, ny.yd

          from base_facility_tb zz,

               (select distinct nf, yd from gygl_zzcbpwl_view) ny) zzny

 where hgl.facility_id(+) = zzny.facility_id

   and hgl.year(+) = zzny.nf

   and hgl.month(+) = zzny.yd

from 后面的两个查询第一个有1680条，第二个有112条记录，当然两个查询的数量还会增加。这两个查询的速度还算可以，但是联合之后的查询速度根本不能忍受，请问各位朋友有没有什么方法可以优化一下。谢谢了！
...全文
209 25 打赏 收藏 转发到动态 举报
写回复
用AI写文章
25 条回复
切换为时间正序
请发表友善的回复…
发表回复
vber1010 2010-04-17
  • 打赏
  • 举报
 回复
多谢朋友们的指教，特别是tangren大哥，现在问题解决了，再次感谢！
vber1010 2010-04-16
  • 打赏
  • 举报
 回复
谢谢上面各位朋友的关注。我现在改了一下，将这个查询(select zz.facility_id, zz.u_name, ny.nf, ny.yd from base_facility_tb zz,
(select distinct nf, yd from gygl_zzcbpwl_view) ny)
建成了物化视图zzny。 但是查询速度提高的幅度并不理想，从查询计划上看查询仍然走了物化视图zzny的基础表。为什么不是直接从物化视图zzny中提取数据呢？物化视图不是相当于一张真实的表吗？
vber1010 2010-04-16
  • 打赏
  • 举报
 回复
[Quote=引用 22 楼 tangren 的回复:]
现在慢，要花少时间？

物化视图如何建立的，刷新方式？
[/Quote]
现在要30几秒 ，我要是把数据放进表里，5、6秒就可以了。这是视图：
create materialized view ZZNY
refresh force on demand
as
select zz.facility_id, zz.u_name, ny.nf, ny.yd
from base_facility_tb zz,
(select distinct nf, yd from gygl_zzcbpwl_view) ny
还有我刚发现物化视图老是失效，基础表并没有动过啊。。。找不到原因。。。这也是第一次使用物化视图。。。汗。。。。
tangren 2010-04-16
  • 打赏
  • 举报
 回复
能不能QQ看一下你的环境
我的qq:13458132
tangren 2010-04-16
  • 打赏
  • 举报
 回复
现在慢，要花少时间？

物化视图如何建立的，刷新方式？
vber1010 2010-04-16
  • 打赏
  • 举报
 回复
[Quote=引用 19 楼 tangren 的回复:]
zzny是物化视图还是普通视图
物化视图是基于本地副本来查询的
[/Quote]
zzny是物化视图，因为我发现如果用查询(select zz.facility_id, zz.u_name, ny.nf, ny.yd from base_facility_tb zz,
(select distinct nf, yd from gygl_zzcbpwl_view) ny)
是造成速度慢的主要原因，于是我将这个查询建成了物化视图。但是速度上还是挺慢，当然也快了很多。但是如果我将这个查询的数据放进表里，速度就很快了。我不解的是，物化视图和表不是一样的吗？怎么会慢这么多呢？
tangren 2010-04-16
  • 打赏
  • 举报
 回复
zzny是物化视图还是普通视图
物化视图是基于本地副本来查询的
chengkeqing77 2010-04-15
  • 打赏
  • 举报
 回复
哦，忘记说了，开发人员还是以sql语句优化为主，其他为铺
chengkeqing77 2010-04-15
  • 打赏
  • 举报
 回复
不理解楼主的业务逻辑，所以希望可以把表关系贴出来。

上面主要问题出现在hgl和zzny遍历次数太多了，如果可以在这两条sql里面把数据过滤到最低，那会对性能有提升的。

提升性能，首先是硬件达到标准，然后是表分区索引等，在就是sql语句
vber1010 2010-04-14
  • 打赏
  • 举报
 回复
谢谢楼上的几位1!今天先讨论到这吧，等我先忙完其它的在回答楼上的问题。 希望各位继续关注这个问题，拜托了！
tangren 2010-04-14
  • 打赏
  • 举报
 回复
追踪一下表TB_ITEM_DATA_LIST_VW被那个引用了
vber1010 2010-04-14
  • 打赏
  • 举报
 回复
gygl_zzcbpwl_view倒是没有在TB_ITEM_DATA_LIST_VW表取数，其定义如下：
create or replace view gygl_zzcbpwl_view as
select 。。。
from gygl_bzzcbpwl_view b,
gygl_rzzcbpwl_view r,
gygl_yzzcbpwl_view y,
gygl_nzzcbpwl_view n
where b.facility_id = r.facility_id
and b.facility_id = y.facility_id
and b.facility_id = n.facility_id
and b.rq = r.RQ(+)
and b.yf = y.yf(+)
and b.nf = n.nf
取数用到的几个视图gygl_bzzcbpwl_view等，是从另一个表取的，其数据量也是很大。。。。。。。
tangren 2010-04-14
  • 打赏
  • 举报
 回复
上面有两处对这个表进行全表扫描，况且数据量较大，
肯定是有问题的（除非对这个表查询没有条件）
是不是gygl_zzcbpwl_view视图从这个表取数据，
gygl_zzcbpwl_view的定义？
vber1010 2010-04-14
  • 打赏
  • 举报
 回复
不好意思 TB_ITEM_DATA_LIST_VW是个表 ，不是视图，因为之前为了更小的改动，所以这个表起了个视图的名字。这个表的数据现有11万条数据，而且一直是在增长的。
tangren 2010-04-14
  • 打赏
  • 举报
 回复
把你这个视图TB_ITEM_DATA_LIST_VW的定义贴出来看一下，
可能需要优化一下
vber1010 2010-04-14
  • 打赏
  • 举报
 回复
[Quote=引用 7 楼 suiziguo 的回复:]
这数据结构设计的有问题。另外，建立索引也无法避免全表扫描，因为你内嵌视图里根本没有where过滤。

从数据量来看，数据也根本不多。


再次建议，如果gygl_zzcbpwl_view是视图，应该考虑优化它先。特别是避免类似万能视图一样的东西。
[/Quote]

谢谢！gygl_zzcbpwl_view是个视图，但是这个查询(select zz.facility_id, zz.u_name, ny.nf, ny.yd
from base_facility_tb zz,
(select distinct nf, yd from gygl_zzcbpwl_view) ny) zzny
并没有占用多少时间，就是两个查询之后的连接有问题，但是不知道这样还有可能优化的余地吗。
vber1010 2010-04-14
  • 打赏
  • 举报
 回复
我对查询计划了解不是很多，这是直接复制过来的，请帮我分析下有没有优化的可能了，谢谢！
SELECT STATEMENT, GOAL = CHOOSE 1731 5908 992544
HASH JOIN OUTER 1731 5908 992544
VIEW PTQM 51 409 51125
MERGE JOIN CARTESIAN 51 409 51125
VIEW PTQM 49 1 10
SORT UNIQUE 49 1 100
FILTER
HASH JOIN OUTER
FILTER
HASH JOIN OUTER
HASH JOIN 21 1 58
VIEW PTQM GYGL_NZZCBPWL_VIEW 9 3 57
SORT GROUP BY 9 3 36
TABLE ACCESS FULL PTQM GYGL_CSCBSTJ_TB 3 2280 27360
VIEW PTQM GYGL_BZZCBPWL_VIEW 11 45 1755
SORT GROUP BY 11 45 765
TABLE ACCESS FULL PTQM GYGL_CSCBSTJ_TB 3 2280 38760
VIEW PTQM GYGL_RZZCBPWL_VIEW 9 3 60
SORT GROUP BY 9 3 36
TABLE ACCESS FULL PTQM GYGL_CSCBSTJ_TB 3 2280 27360
VIEW PTQM GYGL_YZZCBPWL_VIEW 16 3 66
SORT GROUP BY 16 3 1581
HASH JOIN 6 164 86428
TABLE ACCESS FULL PTQM GYGL_CSFW_TB 2 20 10300
TABLE ACCESS FULL PTQM GYGL_CSCBSTJ_TB 3 2280 27360
BUFFER SORT 51 409 47035
TABLE ACCESS FULL PTQM BASE_FACILITY_TB 2 409 47035
VIEW PTQM 1656 144441 6210963
NESTED LOOPS OUTER 1656 144441 9533106
VIEW PTQM 20 1636 49080
MERGE JOIN CARTESIAN 20 1636 49080
MERGE JOIN CARTESIAN 12 4 68
VIEW PTQM 4 2 26
COUNT STOPKEY
VIEW SYS USER_OBJECTS 4 2
UNION-ALL
FILTER
TABLE ACCESS BY INDEX ROWID SYS OBJ$ 3 1 122
INDEX RANGE SCAN SYS I_OBJ2 2 1
TABLE ACCESS BY INDEX ROWID SYS IND$ 2 1 26
INDEX UNIQUE SCAN SYS I_IND1 1 1
INDEX RANGE SCAN SYS I_LINK1 2 1 13
BUFFER SORT 12 2 8
VIEW PTQM 4 2 8
COUNT STOPKEY
VIEW SYS USER_OBJECTS 4 2
UNION-ALL
FILTER
TABLE ACCESS BY INDEX ROWID SYS OBJ$ 3 1 122
INDEX RANGE SCAN SYS I_OBJ2 2 1
TABLE ACCESS BY INDEX ROWID SYS IND$ 2 1 26
INDEX UNIQUE SCAN SYS I_IND1 1 1
INDEX RANGE SCAN SYS I_LINK1 2 1 13
BUFFER SORT 16 409 5317
TABLE ACCESS FULL PTQM BASE_FACILITY_TB 2 409 5317
VIEW PUSHED PREDICATE PTQM ZZ_LIMS_Y_LCKHGL1 1 88 3168
HASH JOIN 4281 3611022 996642072
VIEW PTQM 2574 355145 3551450
SORT UNIQUE 2574 355145 2486015
TABLE ACCESS FULL PTQM TB_ITEM_DATA_LIST_VW 420 355145 2486015
HASH JOIN 822 101677 27046082
HASH JOIN 4 4 920
TABLE ACCESS BY INDEX ROWID PTQM BASE_FACILITY_TB 1 4 460
INDEX RANGE SCAN PTQM PK_BASE_FACILITY_TB 1 2
TABLE ACCESS FULL PTQM BASE_FACILITY_LIMS_TB 2 327 37605
VIEW PTQM 817 24860 894960
SORT GROUP BY 817 24860 3530120
HASH JOIN 435 24860 3530120
VIEW SYS VW_NSO_1 4 7 581
SORT UNIQUE 4 7 672
TABLE ACCESS FULL PTQM LIMS_XM_SFKH 2 7 672
TABLE ACCESS FULL PTQM TB_ITEM_DATA_LIST_VW 420 355145 20953555
suiziguo 2010-04-14
  • 打赏
  • 举报
 回复
这数据结构设计的有问题。另外，建立索引也无法避免全表扫描，因为你内嵌视图里根本没有where过滤。

从数据量来看，数据也根本不多。


再次建议，如果gygl_zzcbpwl_view是视图，应该考虑优化它先。特别是避免类似万能视图一样的东西。
tangren 2010-04-14
  • 打赏
  • 举报
 回复
先暂停执行，然后查看执行计划
看执行计划不会卡死的,你可以在pl/sql developer中选中你那条SQL，然后F5

如果要建索引的话,where后面的条件所涉及的字段都可以考虑，但索引效率的高低
要由实际选择性情况来决定。
vber1010 2010-04-14
  • 打赏
  • 举报
 回复
回2#、3#：是做笛卡尔积。sql业务上没有问题，只是现在不知道怎么优化是他更快一点，现在的问题是更本查不出来数据，太慢了... 拜托在想想办法，谢谢了！
加载更多回复（4）
最热网友收藏：30条经典的SQL语句(2007年第12周)
2007年第12周最热网友收藏 求30条经典的SQL语句,或是T-SQL语句.随后再开贴放分.(138)，共176人收藏，yuewh491首先收藏 吐血奉献：C#获取硬盘编号、CPU编号及类型、内存大小及使用情况(120)，共122人收藏，jrl5365首先收藏 微软的面试题及答案－超变态但是很经典(99)，共171人收藏，cutftp首先收藏 Java精...
oracle执行计划完全一样,急求解答:两个相同的数据库的执行计划差别怎么那么大，附带执行计划!!!...
最近工作中遇到一个很郁闷的问题:前提:我一个培训环境，一个测试环境，均是AIX 5.3的操作系统，2个CPU ,6G内存,测试环境是1个月前通过rman方式将数据从培训环境中导入的检查确认参数文件保持一致,表结构一致,数据量差别非常小问题现象:一个sql语句在两个配置和表结构相同的数据库中的执行计划不同，而且差别太大,查询结果出来后一个是5秒,一个几分钟sql语句:select *from (se...
2007年第12周最热网友收藏
2007年第12周最热网友收藏 求30条经典的SQL语句,或是T-SQL语句.随后再开贴放分.(138)，共176人收藏，yuewh491首先收藏 吐血奉献：C#获取硬盘编号、CPU编号及类型、内存大小及使用情况(120)，共122人收藏，jrl5365首先收藏 微软的面试题及答案－超变态但是很经典(99)，共171人收藏，cutftp首先收藏 Java精华积累:每个初学者都应该搞懂的问题...
mysql outfile特殊字符_急求！~前辈们，小弟搞了一天MySQL导入txt，涉及到特殊符号，load data local infile 语句出错...
要求:导出test数据库的student表，字段之间用顿号+空格(、)分隔，字符型字段用引号包括("")，行首使用>开头，每条记录之间换行这是导出txt语句:SELECT*FROMtest.studentINTOOUTFILE'C...要求:导出test数据库的student表，字段之间用顿号+空格(、 )分隔，字符型字段用引号包括("")，行首使用>开头，每条记录之间换行这是导出tx...
c# 网站在服务器上第一次请求都需要耗时很久,c#查询太慢 网站发布到服务器上查询速度太慢 急求高手啊 在线等...
我做了一个查重的功能 查询出来的数据结果 最多几百条 几百条这种情况也很少 一般就几条 所以我并没有用到分页 表里面的总行数也就十几万数据的样子 按理说不应该会这么慢啊下面是我的存储过程 @where条件是where C_Web_Name =''lily'' 我给C_Web_Name 这个字段 建立了一个非聚集索引ALTER proc [dbo].[CourseBywhere]...
Oracle

17,086

社区成员

55,238

社区内容

发帖
与我相关
我的任务
社区描述
Oracle开发相关技术讨论
社区管理员
  • 开发
  • Lucifer三思而后行
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

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

+ 用AI写文章