如题,
我自己优化了下,但是结果还是与客户的要求有一定的差距。
表的数据量比较大,相关搜索字段的索引都建了。大家帮忙优化下SQL语句。
以下是最原始的SQL:
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,2 usagecode,t.codeformat,10 coderate ,t.nettype,t.playurl, null destpath
from t_cms_mtv_file@dblink_cms t, t_cms_mtv@dblink_cms a
where t.filetype is null
and t.codeformat = '7'
and t.coderate = '30'
and t.nettype = '3'
and t.contentid = a.contentid
and a.type = 2
) z
where z.contentid not in (select b.contentid
from (select t.contentid
from t_cms_mtv_file@dblink_cms t, t_cms_mtv@dblink_cms a
where t.filetype is null
and t.codeformat = '7'
and t.coderate = '30'
and t.nettype = '3'
and t.contentid = a.contentid
and a.type = 2
) b, t_cms_content_tag@dblink_cms c, t_cms_tag@dblink_cms d
where b.contentid = c.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 not like '%手机视频WAP站点%';
我自己优化了下,但是结果还是与客户的要求有一定的差距。
表的数据量比较大,相关搜索字段的索引都建了。大家帮忙优化下SQL语句。
以下是最原始的SQL:
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,2 usagecode,t.codeformat,10 coderate ,t.nettype,t.playurl, null destpath
from t_cms_mtv_file@dblink_cms t, t_cms_mtv@dblink_cms a
where t.filetype is null
and t.codeformat = '7'
and t.coderate = '30'
and t.nettype = '3'
and t.contentid = a.contentid
and a.type = 2
) z
where z.contentid not in (select b.contentid
from (select t.contentid
from t_cms_mtv_file@dblink_cms t, t_cms_mtv@dblink_cms a
where t.filetype is null
and t.codeformat = '7'
and t.coderate = '30'
and t.nettype = '3'
and t.contentid = a.contentid
and a.type = 2
) b, t_cms_content_tag@dblink_cms c, t_cms_tag@dblink_cms d
where b.contentid = c.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 not like '%手机视频WAP站点%';
suiziguo,请问:如何应用oralce全文检索?
数据库版本是:Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
1.小表放from最后。
2.过滤最大的放where最下面
这个我知道!
suiziguo,麻烦你把我的SQL代码优化下,贴上来!谢谢!
优化是个细致活,来不得半点虚拟。
我觉得主要是如此多的like,前面模糊,后面模糊,无论你如何索引,都没用。你先尝试下全文检索。
谁有例子吗?
我这不能上baidu/google
找到篇CSDN上的,你看看
t.contentid,
t.filepath,
t.filetype,
1 transferstatus,
2 usagecode,
t.codeformat,
10 coderate,
t.nettype,
t.playurl,
null destpath
from t_cms_mtv_file@dblink_cms t, t_cms_mtv@dblink_cms a, text_import n
where t.contentid = n.hw_cid
and t.contentid = a.contentid
and not exists
(select 1
from t_cms_content_tag@dblink_cms c, t_cms_tag@dblink_cms d
where t.contentid = c.contentid
and c.tagid = d.tagid
and (d.tagname like '%??????%' or d.tagname like '%WAP???%'))
and n.name_path not like '%????WAP??%'
and t.filetype is null
and t.codeformat = '7'
and t.coderate = '30'
and t.nettype = '3'
and a.type = 2;