高手们帮忙优化下一个SQL语句

  如题，
  我自己优化了下，但是结果还是与客户的要求有一定的差距。
  表的数据量比较大，相关搜索字段的索引都建了。大家帮忙优化下SQL语句。
  以下是最原始的SQL：
  SQL code:

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