怎样优化此sql

Question

    如题:如何优化下面的SQL语句select * from              
    (select z.fileid, z.contentid,z.filepath,z.filetype,z.transferstatus,z.usagecode,z.codeformat,
            z.coderate, z.nettype,z.playurl,z.destpath 
       from (select t.fileid,t.contentid,t.filepath,t.filetype,
                    1 transferstatus,4 usagecode,t.codeformat,20 coderate,
                    t.nettype,t.playurl, null destpath
               from t_cms_mtv_file@dblink_cms t, t_cms_mtv@dblink_cms a
              where t.filetype = 2
                and t.codeformat = '7' 
                and t.coderate = '30' 
                and t.nettype = '3' 
                and t.contentid = a.contentid
                and a.type = 4
              ) z
         where not exists  (select b.contentid 
                              from (select t.contentid 
                                     from t_cms_mtv_file@dblink_cms t, t_cms_mtv@dblink_cms a
                                    where t.filetype = 2
                                      and t.codeformat = '7' 
                                      and t.coderate = '30' 
                                      and t.nettype = '3' 
                                      and t.contentid = a.contentid
                                      and a.type = 4
                                     ) b, t_cms_content_tag@dblink_cms c, t_cms_tag@dblink_cms d
                             where b.contentid = c.contentid
                               and b.contentid = z.contentid
                               and ((c.tagid = d.tagid and d.tagname like '%客户端类节目%')
                                    or (c.tagid = d.tagid and d.tagname like '%WAP类节目%'))
                               )
         )m, text_import n
   where m.contentid = n.hw_cid
     and n.name_path like '%手机视频WAP站点%';    达人们，帮忙分析下！在线等！

Answer 1

考虑以下几个方面
1."*"用具体的字段代替
2.大表在from字句靠后
3.在搜索字段上建立合适的索引

Answer 2

1.实际我是用的字段。由于字段多，我在此用*展示。
2.这是RBO的规则！在11g是基于CBO的
3.表较多，而且是在建在dblink上，索引的作用不大！

Answer 3

那优化从数据库SQL机制层面是没有办法做到了，你看不到执行计划，
只有业务上和语句层面上看看能否优化了

Answer 4

解释计划：SELECT STATEMENT, GOAL = CHOOSE 35 1 1330
 FILTER
  HASH JOIN 25 1 1330
   NESTED LOOPS 4 1 616
    REMOTE 3 1 586
    REMOTE 1 1 30
   TABLE ACCESS FULL MS0206 TEXT_IMPORT 2 367470 262373580
  CONCATENATION
   NESTED LOOPS 5 1 194
    MERGE JOIN CARTESIAN 4 1 129
     NESTED LOOPS 3 1 99
      REMOTE 2 1 30
      REMOTE 1 1 69
     BUFFER SORT 3 3 90
      REMOTE 1 3 90
    REMOTE 1 1 65
   NESTED LOOPS 5 1 194
    MERGE JOIN CARTESIAN 4 1 129
     NESTED LOOPS 3 1 99
      REMOTE 2 1 30
      REMOTE 1 1 69
     BUFFER SORT 3 3 90
      REMOTE 1 3 90
    REMOTE 1 1 65

Answer 5

看到了TABLE ACCESS FULL    MS0206    TEXT_IMPORT    2    367470    262373580
这个。。可以把
 )m, text_import n
   where m.contentid = n.hw_cid
     and n.name_path like '%手机视频WAP站点%
修改成
)m where exist (select 1 from text_import n
   where m.contentid = n.hw_cid
     and n.name_path like '%手机视频WAP站点%)
避免全表扫描

Answer 6

  呵呵！是啊！
  YY_MM_DD，麻烦看下里面还有哪些地方能优化下。
  执行这句sql需要15分钟！觉得太慢了！
  t_cms_mtv_file：     500W数据
  t_cms_mtv：          70W数据
  t_cms_content_tag：  200W数据
  t_cms_tag：          3000数据  可是查询结果仅仅只有109条。

Answer 7

相应搜索字段都建立索引。
由于执行时间满慢，查询执行计划的时间较长。
先列出解释计划如下：SELECT STATEMENT, GOAL = CHOOSE 13 1 302
 SORT AGGREGATE 1 302
  NESTED LOOPS SEMI 13 1 302
   HASH JOIN ANTI 12 1 98
    NESTED LOOPS 3 1 81
     INDEX RANGE SCAN CMS_ZHOU IX_CMS_MTV_FILE_CHECK 2 1 69
     TABLE ACCESS BY INDEX ROWID CMS_ZHOU T_CMS_MTV 1 1 12
      INDEX UNIQUE SCAN CMS_ZHOU PK_CMS_MTV_CONTENTID 1
    VIEW SYS VW_NSO_1 8 1 17
     NESTED LOOPS 8 1 159
      NESTED LOOPS 4 1 94
       NESTED LOOPS 3 1 81
        INDEX RANGE SCAN CMS_ZHOU IX_CMS_MTV_FILE_CHECK 2 1 69
        TABLE ACCESS BY INDEX ROWID CMS_ZHOU T_CMS_MTV 1 1 12
         INDEX UNIQUE SCAN CMS_ZHOU PK_CMS_MTV_CONTENTID 1
       INDEX RANGE SCAN CMS_ZHOU IX_CMS_CONTENT_TAG_ID 1 3 39
      INDEX FAST FULL SCAN CMS_ZHOU IX_CMS_TAG_NAMEID 4 1 65
   INDEX RANGE SCAN CMS_ZHOU IX_TEXT_IMPORT_ID 1 526 107304

Answer 8

t_cms_mtv_file@dblink_cms t, t_cms_mtv@dblink_cms a
              where t.filetype = 2
                and t.codeformat = '7' 
                and t.coderate = '30' 
                and t.nettype = '3' 
                and t.contentid = a.contentid
                and a.type = 4
把这个也改成exists的形式

Answer 9

把t_cms_content_tag@dblink_cms c, t_cms_tag@dblink_cms d这两个表的位置调一下。。

Answer 10

改成exists后:select fileid,contentid,filepath,filetype,transferstatus,usagecode,codeformat,coderate,nettype,playurl,destpath from         
    (select z.fileid, z.contentid,z.filepath,z.filetype,z.transferstatus,z.usagecode,z.codeformat,
            z.coderate, z.nettype,z.playurl,z.destpath 
       from (select t.fileid,t.contentid,t.filepath,t.filetype,
                    1 transferstatus,4 usagecode,t.codeformat,20 coderate,
                    t.nettype,t.playurl, null destpath
               from t_cms_mtv_file@dblink_cms t, t_cms_mtv@dblink_cms a
              where exists (select 1 
                              from t_cms_mtv_file@dblink_cms b, t_cms_mtv@dblink_cms a
                             where b.filetype = 2
                               and b.codeformat = '7' 
                               and b.coderate = '30' 
                               and b.nettype = '3' 
                               and b.contentid = a.contentid
                               and a.type = 4
                )
              ) z
         where not exists  (select x.contentid 
                              from (select t.contentid 
                                      from t_cms_mtv_file@dblink_cms t, t_cms_mtv@dblink_cms a
                                    where exists (select 1 
                                                    from t_cms_mtv_file@dblink_cms b, t_cms_mtv@dblink_cms a
                                                   where b.filetype = 2
                                                     and b.codeformat = '7' 
                                                     and b.coderate = '30' 
                                                     and b.nettype = '3' 
                                                     and b.contentid = a.contentid
                                                     and a.type = 4
                )
                                     ) x, t_cms_tag@dblink_cms d,t_cms_content_tag@dblink_cms c
                             where x.contentid = c.contentid
                               and x.contentid = z.contentid
                               and ((c.tagid = d.tagid and d.tagname like '%客户端类节目%')
                                    or (c.tagid = d.tagid and d.tagname like '%WAP类节目%'))
                               )
         )m 
         where exists (select 1 
                        from text_import n
                       where m.contentid = n.hw_cid
                         and n.name_path like '%手机视频WAP站点%') ;   这样cost变得非常大了！有15位数.

Answer 11

改了下：select fileid,contentid,filepath,filetype,transferstatus,usagecode,codeformat,coderate,nettype,playurl,destpath from         
    (select z.fileid, z.contentid,z.filepath,z.filetype,z.transferstatus,z.usagecode,z.codeformat,
            z.coderate, z.nettype,z.playurl,z.destpath 
       from (select t.fileid,t.contentid,t.filepath,t.filetype,
                    1 transferstatus,4 usagecode,t.codeformat,20 coderate,
                    t.nettype,t.playurl, null destpath
               from t_cms_mtv_file@dblink_cms t
              where exists (select 1 
                              from t_cms_mtv@dblink_cms a
                             where t.filetype = 2
                               and t.codeformat = '7' 
                               and t.coderate = '30' 
                               and t.nettype = '3' 
                               and t.contentid = a.contentid
                               and a.type = 4
                )
              ) z
         where not exists  (select x.contentid 
                              from (select t.contentid 
                                      from t_cms_mtv_file@dblink_cms t
                                    where exists (select 1 
                                                    from t_cms_mtv@dblink_cms a
                                                   where t.filetype = 2
                                                     and t.codeformat = '7' 
                                                     and t.coderate = '30' 
                                                     and t.nettype = '3' 
                                                     and t.contentid = a.contentid
                                                     and a.type = 4
                )
                                     ) x, t_cms_tag@dblink_cms d,t_cms_content_tag@dblink_cms c
                             where x.contentid = c.contentid
                               and x.contentid = z.contentid
                               and ((c.tagid = d.tagid and d.tagname like '%客户端类节目%')
                                    or (c.tagid = d.tagid and d.tagname like '%WAP类节目%'))
                               )
         )m 
         where exists (select 1 
                        from text_import n
                       where m.contentid = n.hw_cid
                         and n.name_path like '%手机视频WAP站点%') ;

Answer 12

还有就是吧
t.filetype = 2
                               and t.codeformat = '7' 
                               and t.coderate = '30' 
                               and t.nettype = '3' 
这个放到exist的外面

Answer 13

   修改后：--insert into t_ms_mtv_file3(fileid,contentid,filepath,filetype,transferstatus,usagecode,codeformat,coderate,nettype,playurl,destpath)               
select fileid,contentid,filepath,filetype,transferstatus,usagecode,codeformat,coderate,nettype,playurl,destpath from         
    (select z.fileid, z.contentid,z.filepath,z.filetype,z.transferstatus,z.usagecode,z.codeformat,
            z.coderate, z.nettype,z.playurl,z.destpath 
       from (select t.fileid,t.contentid,t.filepath,t.filetype,
                    1 transferstatus,4 usagecode,t.codeformat,20 coderate,
                    t.nettype,t.playurl, null destpath
               from t_cms_mtv_file@dblink_cms t
              where exists (select 1 
                              from t_cms_mtv@dblink_cms a
                             where t.contentid = a.contentid
                               and a.type = 4
                           )
               and t.filetype = 2
               and t.codeformat = '7' 
               and t.coderate = '30' 
               and t.nettype = '3' 
              ) z
         where not exists  (select x.contentid 
                              from (select t.contentid 
                                      from t_cms_mtv_file@dblink_cms t
                                    where exists (select 1 
                                                    from t_cms_mtv@dblink_cms a
                                                   where t.contentid = a.contentid
                                                     and a.type = 4
                                                  )
                                      and t.filetype = 2
                                      and t.codeformat = '7' 
                                      and t.coderate = '30' 
                                      and t.nettype = '3'
                                     ) x
                              where exists (select 1 
                                              from t_cms_content_tag@dblink_cms c, t_cms_tag@dblink_cms d
                                             where x.contentid = c.contentid
                                               and x.contentid = z.contentid
                                               and ((c.tagid = d.tagid and d.tagname like '%客户端类节目%')
                                                    or (c.tagid = d.tagid and d.tagname like '%WAP类节目%')))
                               )
         )m 
         where exists (select 1 
                        from text_import n
                       where m.contentid = n.hw_cid
                         and n.name_path like '%手机视频WAP站点%') ;    cost=16,但是cost小，并不代表执行时间就很少。呵呵。
    正在执行中.....

Answer 14

现在这句SQL的执行时间是：7分30秒！