我的SQL语句出现很奇怪的问题!请您帮忙解答!

[shmilylee]

语句如下

select t1.msisdn,t1.state,t1.suspend_date,t1.customer_name,t1.sex,t1.deal_code,t1.start_date,t1.end_date 
from (select gsm_user_yz.msisdn,gsm_user_yz.state,gsm_user_yz.suspend_date,p_customer_yz.customer_name,
p_customer_yz.sex,user_smscall_yz.deal_code,user_smscall_yz.start_date,user_smscall_yz.end_date 
from yzzw.gsm_user_yz,yzzw.p_customer_yz,yzzw.user_smscall_yz 
where yzzw.gsm_user_yz.customer_id = p_customer_yz.customer_id and 
yzzw.gsm_user_yz.gsm_user_id = user_smscall_yz.gsm_user_id and 
user_smscall_yz.deal_code = '5026' and 
yzzw.gsm_user_yz.msisdn in(select msisdn from YZYD_GWB_MSISDNTEMP))t1 
inner join 
(select t.msisdn,max(t.start_date) start_date 
from (select gsm_user_yz.msisdn,gsm_user_yz.state,gsm_user_yz.suspend_date,p_customer_yz.customer_name,
p_customer_yz.sex,user_smscall_yz.deal_code,user_smscall_yz.start_date,user_smscall_yz.end_date 
from yzzw.gsm_user_yz,yzzw.p_customer_yz,yzzw.user_smscall_yz 
where yzzw.gsm_user_yz.customer_id = p_customer_yz.customer_id 
and yzzw.gsm_user_yz.gsm_user_id = user_smscall_yz.gsm_user_id 
and user_smscall_yz.deal_code = '5026' 
and yzzw.gsm_user_yz.msisdn in(select msisdn from YZYD_GWB_MSISDNTEMP)) t group by t.msisdn ) t2 
on t1.msisdn = t2.msisdn and t1.start_date = t2.start_date

红色的部分是个子查询,能够正确的查询出结果,但是整条语句运行起来就陷入了漫长的等待

select t1.msisdn,t1.state,t1.suspend_date,t1.customer_name,t1.sex,t1.deal_code,t1.start_date,t1.end_date
from (select gsm_user_yz.msisdn,gsm_user_yz.state,gsm_user_yz.suspend_date,p_customer_yz.customer_name,
p_customer_yz.sex,user_smscall_yz.deal_code,user_smscall_yz.start_date,user_smscall_yz.end_date
from yzzw.gsm_user_yz,yzzw.p_customer_yz,yzzw.user_smscall_yz
where yzzw.gsm_user_yz.customer_id = p_customer_yz.customer_id and
yzzw.gsm_user_yz.gsm_user_id = user_smscall_yz.gsm_user_id and
user_smscall_yz.deal_code = '5026' and
yzzw.gsm_user_yz.msisdn in(139527...,138527...))t1
inner join
(select t.msisdn,max(t.start_date) start_date
from (select gsm_user_yz.msisdn,gsm_user_yz.state,gsm_user_yz.suspend_date,p_customer_yz.customer_name,
p_customer_yz.sex,user_smscall_yz.deal_code,user_smscall_yz.start_date,user_smscall_yz.end_date
from yzzw.gsm_user_yz,yzzw.p_customer_yz,yzzw.user_smscall_yz
where yzzw.gsm_user_yz.customer_id = p_customer_yz.customer_id
and yzzw.gsm_user_yz.gsm_user_id = user_smscall_yz.gsm_user_id
and user_smscall_yz.deal_code = '5026'
and yzzw.gsm_user_yz.msisdn in(139527...,138527...)) t group by t.msisdn ) t2
on t1.msisdn = t2.msisdn and t1.start_date = t2.start_date
这里换成直接的内容速度很快,毫秒级的!

不知道为什么会出现这样的情况!

[horizonlyhw]

關注下～～

[vc555]

你贴个执行计划，还要看看索引是怎么建的。

[shmilylee]

数据库是移动公司远程的,我没有建索引什么的,也没有建存储过程(随时可能被清理掉)

执行上面一条语句我等待了几乎快半个小时也没有出结果,

进来看的朋友都给个意见吧,

谢谢啦!

[vc555]

表中总数据量多大呢？查询后筛选的数据又有多少？
你这个问题就是个查询SQL优化的问题，没什么奇怪的。
但是没执行计划，有没索引信息，如何解决

[shmilylee]

哦,你不问我都忘记说了,select msisdn from YZYD_GWB_MSISDNTEMP这个表里我只有测试的2条数据!
总数据量不到500万,出来的数据就看YZYD_GWB_MSISDNTEMP表里的数据量了!

我的疑惑在于我直接写出来 in(xxx,xxx)这样就很快,我测了2条记录是0.016秒
但是我换成in(select msisdn from YZYD_GWB_MSISDNTEMP)这条语句就是漫长的等待,还出不来结果......

[vc555]

这就是因为走的执行计划不同

[vc555]

举个例子：
SQL> select * from test;

AAA BBB
---------- --------------------
1 记录1
2 记录2
3 记录3
21 ss

已选择7行。

SQL> select * from test2;

AAA BBB
---------- --------------------
21 1,2
31 2,3

SQL> select * from test
2 where aaa in(21);

AAA BBB
---------- --------------------
21 ss
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
468 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed


SQL> select * from test
2 where aaa in(select aaa from test2);

AAA BBB
---------- --------------------
21 ss
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
10 consistent gets
0 physical reads
0 redo size
464 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed


看到了吗？在这样小的数据环境中，第二个查询的逻辑读为10，而第一个为2。更不用说数据量大的情况了。

[vc555]

SQL> create index test2_ind on test2(aaa);

索引已创建。

SQL> set autotrace on
SQL> select * from test
2 where aaa in(select aaa from test2);

AAA BBB
---------- --------------------
21 ss
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
464 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed

这时逻辑读降低为4。
所以你要贴执行计划看看sql是如何执行的，而且还要根据执行计划来建索引。

[oracledbalgtu]

没有执行计划，不给出具体每个表的index情况，表中数量。就没法优化。

[Quote=引用楼主 shmilylee 的帖子:]
语句如下

SQL codeselect t1.msisdn,t1.state,t1.suspend_date,t1.customer_name,t1.sex,t1.deal_code,t1.start_date,t1.end_date
from (select gsm_user_yz.msisdn,gsm_user_yz.state,gsm_user_yz.suspend_date,p_customer_yz.customer_name,
p_customer_yz.sex,user_smscall_yz.deal_code,user_smscall_yz.start_date,user_smscall_yz.end_date
from yzzw.gsm_user_yz,yzzw.p_customer_yz,yzzw.user_smscall_yz
whe…
[/Quote]

[njhart2003]

估计没有收集表的统计信息，优化器不能正确的基于成本优化。
建议执行：
analyze table yzzw.gsm_user_yz compute statistic;