急求，一条sql语句的优化

Question

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.ydfrom 后面的两个查询第一个有1680条，第二个有112条记录，当然两个查询的数量还会增加。这两个查询的速度还算可以，但是联合之后的查询速度根本不能忍受，请问各位朋友有没有什么方法可以优化一下。谢谢了！

Answer 1

zz和ny笛卡儿积？另外，gygl_zzcbpwl_view是view？查查其SQL，是否合理。

Answer 2

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
你这个语句做笛卡尔积了,???

Answer 3

回2#、3#：是做笛卡尔积。sql业务上没有问题，只是现在不知道怎么优化是他更快一点，现在的问题是更本查不出来数据，太慢了...  拜托在想想办法，谢谢了！

Answer 4

先暂停执行，然后查看执行计划
看执行计划不会卡死的,你可以在pl/sql developer中选中你那条SQL，然后F5如果要建索引的话,where后面的条件所涉及的字段都可以考虑，但索引效率的高低
要由实际选择性情况来决定。

Answer 5

这数据结构设计的有问题。另外，建立索引也无法避免全表扫描，因为你内嵌视图里根本没有where过滤。从数据量来看，数据也根本不多。
再次建议，如果gygl_zzcbpwl_view是视图，应该考虑优化它先。特别是避免类似万能视图一样的东西。

Answer 6

我对查询计划了解不是很多，这是直接复制过来的，请帮我分析下有没有优化的可能了，谢谢！
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

Answer 7

谢谢！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
并没有占用多少时间，就是两个查询之后的连接有问题，但是不知道这样还有可能优化的余地吗。

Answer 8

把你这个视图TB_ITEM_DATA_LIST_VW的定义贴出来看一下，
可能需要优化一下

Answer 9

不好意思 TB_ITEM_DATA_LIST_VW是个表 ，不是视图，因为之前为了更小的改动，所以这个表起了个视图的名字。这个表的数据现有11万条数据，而且一直是在增长的。

Answer 10

上面有两处对这个表进行全表扫描，况且数据量较大，
肯定是有问题的（除非对这个表查询没有条件）
是不是gygl_zzcbpwl_view视图从这个表取数据，
gygl_zzcbpwl_view的定义？

Answer 11

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等，是从另一个表取的，其数据量也是很大。

Answer 12

追踪一下表TB_ITEM_DATA_LIST_VW被那个引用了

Answer 13

不理解楼主的业务逻辑，所以希望可以把表关系贴出来。上面主要问题出现在hgl和zzny遍历次数太多了，如果可以在这两条sql里面把数据过滤到最低，那会对性能有提升的。提升性能，首先是硬件达到标准，然后是表分区索引等，在就是sql语句

Answer 14

哦，忘记说了，开发人员还是以sql语句优化为主，其他为铺

Answer 15

谢谢上面各位朋友的关注。我现在改了一下，将这个查询(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中提取数据呢？物化视图不是相当于一张真实的表吗？

Answer 16

zzny是物化视图还是普通视图
物化视图是基于本地副本来查询的

Answer 17

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)
是造成速度慢的主要原因，于是我将这个查询建成了物化视图。但是速度上还是挺慢，当然也快了很多。但是如果我将这个查询的数据放进表里，速度就很快了。我不解的是，物化视图和表不是一样的吗？怎么会慢这么多呢？

Answer 18

现在要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
还有我刚发现物化视图老是失效，基础表并没有动过啊找不到原因这也是第一次使用物化视图汗。

Answer 19

多谢朋友们的指教，特别是tangren大哥，现在问题解决了，再次感谢！