select a.transactionid, a.creator, a.creattime, a.updatetime,
a.status, a.handletimes, a.operatetype, a.objecttype,
a.objectid, a.objectname, a.objecturl, a.nettype,
a.netid, a.returntime, a.returncode, a.returndesc, a.netstatus,b.netname
from t_cms_transaction a,
( select distinct netid, nettype, contenttype, netname from t_cms_netconf ) b
where a.netid = b.netid
and a.nettype = b.nettype
and a.objecttype = b.contenttype || v_sql
order by 1 v_sql: 包含多个查询字段的动态sql。故不好在相关字段建索引! a表数据量:1000W
b表数据量: 500 以上sql执行一遍要30分钟,请大家帮忙优化下!谢谢!在线等!
|| v_sql 是不是跟的都是一些条件??如果是一些条件的话可以先考虑
with tem as (
select a.transactionid, a.creator, a.creattime, a.updatetime,
a.status, a.handletimes, a.operatetype, a.objecttype,
a.objectid, a.objectname, a.objecturl, a.nettype,
a.netid, a.returntime, a.returncode, a.returndesc, a.netstatus,b.netname
from t_cms_transaction a,
( select netid, nettype, contenttype, netname from t_cms_netconf group by netid, nettype, contenttype, netname ) b
where a.netid = b.netid
and a.nettype = b.nettype
and a.objecttype = b.contenttype )select * from tem where 1 = 1 and || v_sql
order by 1 或者可以考虑 先建立零时表 应为 你的表连接 只要求得到 500 行数据
试验下 具体效率也不确定
个人猜测,可能会慢在排序这块。给你一个简单的建议,在order by 之前, 建立一个会话级别的临时表,将要排序的数据放在临时表里面然后对临时表进行排序,这样的话就会快很多。我以前的一个案例也是这么搞定的。
and a.nettype = b.nettype;
这里,netID要建立索引,NETTYPE不要建索引,建了会更加慢.
a.status, a.handletimes, a.operatetype, a.objecttype,
a.objectid, a.objectname, a.objecturl, a.nettype,
a.netid, a.returntime, a.returncode, a.returndesc, a.netstatus,b.netname
from t_cms_transaction a
WHERE ( A.a.netid ,a.nettype,a.objecttype )
IN (SELECT b.netid,b.nettype, b.contenttype || v_sql FROM t_cms_netconf b )
ORDER BY 1;试试这个,以我很多次的实验,这样效率比其他效果好一些