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

Question

语句如下
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
这里换成直接的内容速度很快,毫秒级的!不知道为什么会出现这样的情况!

Answer 1

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

Answer 2

哦,你不问我都忘记说了,select msisdn from YZYD_GWB_MSISDNTEMP这个表里我只有测试的2条数据!
总数据量不到500万,出来的数据就看YZYD_GWB_MSISDNTEMP表里的数据量了!我的疑惑在于我直接写出来 in(xxx,xxx)这样就很快,我测了2条记录是0.016秒
但是我换成in(select msisdn from YZYD_GWB_MSISDNTEMP)这条语句就是漫长的等待,还出不来结果......

Answer 3

举个例子：
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,3SQL> 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。更不用说数据量大的情况了。

Answer 4

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是如何执行的，而且还要根据执行计划来建索引。

Answer 5

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

Answer 6

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