Question

sql语句优化。oracle中，这两个sql语句，由于数据量非常的大。有几G。查询非常的慢。求高手帮忙优化

（1）select c.characvalueid, c.sequencekey, l.resourceid from
tr_characvalue c, tr_resourcecharac l
where c.characvalueid = l.characvalueid and l.resourceid
IN(SELECT DISTINCT resourceid FROM tr_teammember WHERE teamid =20160
UNION SELECT DISTINCT resourceid FROM tr_resource WHERE genresteamid =20160)
order by c.characid, c.sequencekey

（2）select u.resourceid, u.userfieldid from
tr_resourcevalue u, tr_userfield p
where u.userfieldid = p.userfieldid and u.resourceid
IN(SELECT DISTINCT resourceid FROM tr_teammember WHERE teamid =20160
UNION SELECT DISTINCT resourceid FROM tr_resource WHERE genresteamid =20160)
order by u.userfieldid

Answer 1

-- 以第2个sql 为例
1. 在数据库库系统中，如果经常用到一些大的查询语句，
应该要考虑将这些查询建立模型，并在模型上建立索引。
create table tr_resource2teammb as
SELECT DISTINCT resourceid
FROM tr_teammember WHERE teamid =20160
UNION
SELECT DISTINCT resourceid
FROM tr_resource WHERE genresteamid =20160;
2. 建立索引，要保证做关联的表的字段上都有索引。
create index idx_tr_resource2teammb_1 on tr_resource2teammb(resourceid);
create index idx_tr_resourcevalue_1 on tr_resourcevalue(userfieldid);
create index idx_tr_resourcevalue_2 on tr_resourcevalue(resourceid);
create index tr_userfield on tr_userfield(userfieldid);
3. 然后就是sql方面的优化，用 exists 替换 in
select select u.resourceid, u.userfieldid from
tr_resourcevalue u, tr_userfield p
where u.userfieldid = p.userfieldid
and exists (
select 1 from tr_resource2teammb t where u.resourceid=t.resourceid
)
order by u.userfieldid
4. 如果仍然很慢的话，要看一下
tr_resourcevalue,tr_userfield 表的是不是经常发生变化，
因为 表的属性已经存在 数据库的数据字典中，查询计划的定制也与
表的属性有关(对一条SQL按F5可以查看查询计划)，
表结构或者表数据或者表索引发生变化，如果不及时更新到数据字典中，
会影响查询速度。以下语句用于更新表在数据库的中的属性。
analyze table tr_resourcevalue estimate statistics

5. 如果仍然很慢，就要看下数据库系统配置方面后者机器硬件的问题了。
-- 这方面我就不太懂了

追问

谢谢，只是分只能给一个人了

Answer 2

1)如果可以用exist 就不要使用in 同时您看下相关联的表字段characvalueid resourceid，加上索引
select c.characvalueid, c.sequencekey, l.resourceid
from tr_characvalue c, tr_resourcecharac l
where c.characvalueid = l.characvalueid
and exists (select 1
from tr_teammember tt
WHERE tt.teamid = 20160
and tt.resourceid = l.resourceid)
union
select c.characvalueid, c.sequencekey, l.resourceid
from tr_characvalue c, tr_resourcecharac l
where c.characvalueid = l.characvalueid
and exists (select 1
from tr_resource tr
WHERE genresteamid = 20160
and tr.resourceid = l.resourceid)
--order by c.characid, c.sequencekey 这个没有给您排序。

或1楼提到的改成这个
select c.characvalueid, c.sequencekey, l.resourceid
from tr_characvalue c, tr_resourcecharac l
where c.characvalueid = l.characvalueid
and (exists (select 1
FROM tr_teammember
WHERE teamid = 20160
and resourceid = l.resourceid
)
or exists (select 1
FROM tr_resource
WHERE genresteamid = 20160
and resourceid = l.resourceid
)

2）userfieldid 两个表加索引
select u.resourceid, u.userfieldid
from tr_resourcevalue u, tr_userfield p
where u.userfieldid = p.userfieldid
and（ exists (select 1
FROM tr_teammember
WHERE teamid = 20160
and resourceid = u.resourceid
)
or exists (select 1
FROM tr_resource
WHERE genresteamid = 20160
and resourceid = u.resourceid
)
)
order by u.userfieldid

Answer 3

/** try this: */

-- 1)
select
c.characvalueid,
c.sequencekey,
l.resourceid
from tr_characvalue c
join tr_resourcecharac l on c.characvalueid = l.characvalueid
where 1=1
and (1=2
or exists (select 1
FROM tr_teammember
WHERE teamid = 20160
and resourceid = l.resourceid
)
or exists (select 1
FROM tr_resource
WHERE genresteamid = 20160
and resourceid = l.resourceid
)
)
order by c.characid, c.sequencekey

-- 2)
select u.resourceid, u.userfieldid
from tr_resourcevalue u
join tr_userfield p on u.userfieldid = p.userfieldid
where 1=1
and (1=2
or exists (select 1
FROM tr_teammember
WHERE teamid = 20160
and resourceid = u.resourceid
)
or exists (select 1
FROM tr_resource
WHERE genresteamid = 20160
and resourceid = u.resourceid
)
)
order by u.userfieldid

追问

谢谢。我这样写确实提升性能提升了不少。查询速度快了，能解释下为何要加“1=2”这个条件呢。我发现不加这个条件就变成死循环了一样，造成内存溢出了

追答

其实没啥特殊的作用，只是为了方便格式化、调整逻辑，提供可读性。
若不用，可以这样:
...
and (exists (select 1
FROM tr_teammember
WHERE teamid = 20160
and resourceid = u.resourceid
)
or exists (select 1
FROM tr_resource
WHERE genresteamid = 20160
and resourceid = u.resourceid
)
)

Answer 4

如果你的ORACLE是9I或者10G

可以通过在SQLPLUS中执行如下SQL命令得到执行计划：

set autot trace;

set timing on;

执行你要执行的SQL语句就可以得到SQL语句的执行计划了。

有什么问题给我留言

希望能帮助你，祝你好运
另外,团IDC网上有许多产品团购,便宜有口碑

Answer 5

1)
c.characvalueid,l.characvalueid ,l.resourceid ,teamid =20160 ,genresteamid =20160
最好都有索引.
数据量太大就不要用ORDER BY

select c.characvalueid, c.sequencekey, l.resourceid from
tr_characvalue c, tr_resourcecharac l
where c.characvalueid = l.characvalueid and l.resourceid
IN(SELECT DISTINCT resourceid FROM tr_teammember WHERE teamid =20160
UNION all SELECT DISTINCT resourceid FROM tr_resource WHERE genresteamid =20160)
--order by c.characid, c.sequencekey