请帮忙优化以下sql语句，海量存储

redred5

我发现了一个sql语句要运行15个小时，看查询分析发现都是用了全表扫描，没有用上索引，
以下是查询分析

SELECT a.match_nbr,
            b.key_month,
            b.serv_id,
            b.serv_seq_nbr,
            b.exchange_id,
            b.serv_type_id,
            b.district_id,
            b.partner_id,
            b.billing_type_id,
            b.acct_seq_nbr,
            b.acct_id,
            b.ticket_type_cd,
            b.calling_area_code,
            b.calling_nbr,
            b.start_date,
            b.start_time,
            b.end_time,
            b.duration,
            b.ext_charge charge,
            b.due_charge,
            b.t_count,
            b.rate,
            b.operation_serial,
            b.task_id
       FROM dim_short_nbr a,
            base_ticket b
      WHERE a.state='S0A'
        AND b.called_nbr like a.match_nbr||'%'
  INTO TEMP tmp_short_nbr;



Estimated Cost: 1291256064
Estimated # of Rows Returned: 1015735808

  1) ibd.b: SEQUENTIAL SCAN

  2) ibd.a: SEQUENTIAL SCAN

        Filters: (ibd.b.called_nbr LIKE (ibd.a.match_nbr || '%' )AND ibd.a.state
= 'S0A' )
NESTED LOOP JOIN

我在base_ticket表上的called_nbr字段也创建了索引，

我加了SELECT { + INDEX (a.called_nbr)}优化器指示也仍然不管用，还是不能用到索引，其中base_ticket表有3000多万条记录，dim_short_nbr数据量不大，如果用了like字段就不能用索引的话
请问该如何启用base_ticket上的索引？或者有其他方法优化此语句？

[ 本帖最后由 redred5 于 2006-2-15 11:14 编辑 ]

xxyyy

你的where子句中用了某列的算术表达式b.called_nbr like a.match_nbr||'%'，这肯定会全表扫描的，而且不会用索引。如何优化，只要把这个算术表达式分解即可。
我不知你的a.match_nbr的列类型，假设为char(10),则此表达式可以写成b.called_nbr[1,10] = a.match_nbr. 这样就能用上索引了。
如果a.match_nbr的列类型是long,int,decimal,money,varchar,date,datetime等类型，就不能用上述优化。要根据你的业务类型具体分析。

herl

a.state上建个索引是否也会有效？

xxyyy

原帖由 herl 于 2006-2-15 21:14 发表
a.state上建个索引是否也会有效？

如果a.stat的值含有大量重复值，在此列上建索引的效率不明显。如果重复值少，效果会提高很多。

fush76

原帖由 xxyyy 于 2006-2-15 15:39 发表
你的where子句中用了某列的算术表达式b.called_nbr like a.match_nbr||'%'，这肯定会全表扫描的，而且不会用索引。如何优化，只要把这个算术表达式分解即可。

我不知你的a.match_nbr的列类型，假设为char(10),则此表达式可以写成b.called_nbr[1,10] = a.match_nbr. 这样就能用上索引了。


...

您说的好像更不对了，写成b.called_nbr[1,10] = a.match_nbr. 这样更不会用索引了．


一般的原则是，表达式不要放在"="的左边．

xxyyy

原帖由 fush76 于 2006-2-16 08:29 发表

您说的好像更不对了，写成b.called_nbr[1,10] = a.match_nbr. 这样更不会用索引了．


一般的原则是，表达式不要放在"="的左边．

这个是用索引的，因为b.called_nbr[1,10]表达式取子串，是从列的第一个字符开始的，这是用索引的。
如果从第二个或者其他的开始取，就不会用到索引。

fush76

ＴＯ xxyyy,其实您说的 b.called_nbr[1,10] 与 like 的效果是一样的．

xxyyy

原帖由 fush76 于 2006-2-16 08:53 发表
ＴＯ xxyyy,其实您说的 b.called_nbr[1,10] 与 like 的效果是一样的．

不一样，你用like，那么在表达式的右边要有｜｜‘％’，这样就用不到索引了

xjfirst

先把dim_short_nbr表中state='S0A'的值写入一张临时表，再把base_ticket与先前创建的临时表做关连，这样做会快不少。

redred5

原帖由 xxyyy 于 2006-2-15 15:39 发表
你的where子句中用了某列的算术表达式b.called_nbr like a.match_nbr||'%'，这肯定会全表扫描的，而且不会用索引。如何优化，只要把这个算术表达式分解即可。
我不知你的a.match_nbr的列类型，假设为char(10),则 ...

这个字段类型是varchar的，可能不行了~
但是你说的“只要把这个算术表达式分解即可“该怎么分解？

原帖由 xjfirst 于 2006-2-16 12:25 发表
先把dim_short_nbr表中state='S0A'的值写入一张临时表，再把base_ticket与先前创建的临时表做关连，这样做会快不少。

没用吧？dim_short_nbr表才290多条记录，