sql语句的优化问题

Question

我现在有这样的一个sql语句select distinct b.mbrid,b.min_dos  from pf a,ma b where 
a.mbrid = b.mbrid and a.dos >b.min_dos
and
(
   regexp_like(dx1,'^174[0-9]{0,2}$') or dx1 = '2330'
  or regexp_like(dx2,'^174[0-9]{0,2}$') or dx2 = '2330'
   or regexp_like(dx3,'^174[0-9]{0,2}$') or dx3 = '2330'
   or regexp_like(dx4,'^174[0-9]{0,2}$') or dx4 = '2330'
   or regexp_like(dx5,'^174[0-9]{0,2}$') or dx5 = '2330'
   or regexp_like(dx6,'^174[0-9]{0,2}$') or dx6 = '2330'
   or regexp_like(dx7,'^174[0-9]{0,2}$') or dx7 = '2330'
   or regexp_like(dx8,'^174[0-9]{0,2}$') or dx8 = '2330'
   or regexp_like(dx9,'^174[0-9]{0,2}$') or dx9 = '2330'
)pf的数据量是1000W 而 ma的数据量是4454 
pf的字段有 mbrid，dos，dx1，dx2，dx3，dx4，dx5，dx6，dx7，dx8，dx9
ma的字段有 mbrid，min_dos
现在pf的dx1-9 这9个字段都是创建有位图索引的！ 但是现在由于使用了正则表达式导致了不走位图索引了
现在还有什么方法能优化一下啊这个是执行计划 大家看一下
SELECT STATEMENT, GOAL = ALL_ROWS Cost=21 Cardinality=1 Bytes=65 Time=1
 HASH UNIQUE Cost=21 Cardinality=1 Bytes=65 Time=1
  TABLE ACCESS BY INDEX ROWID Object owner=HIT Object name=PF Cost=18 Cardinality=1 Bytes=39 Time=1
   NESTED LOOPS Cost=20 Cardinality=1 Bytes=65 Time=1
    TABLE ACCESS FULL Object owner=HIT Object name=MA Cost=2 Cardinality=1 Bytes=26 Time=1
    INDEX RANGE SCAN Object owner=HIT Object name=PF_PRIMARY Cost=3 Cardinality=19 Time=1

Answer 1

不太清楚，但是distinct可以用exists代替效率会快些

Answer 2

第一个问题，为什么表pf中有1000W而执行计划中的cost这么小？有没有做过表分析？oracle的性能是非常依赖于准确地表分析的。

Answer 3

使用regexp_like会导致FULL TABLE SCAN,解决方案有两种
*. 改成like。你的问题完全可以改成like，
(dx1 like '174%' and substr(dx1,4,1) between '0' and '9' and substr(dx1,5,1) in ('0','2'))
*. 建立function-based index。但不符合你的要求。因为regexp_like中的条件肯定是动态的。

Answer 4

第三点，对于很多OR条件，建议改成UNION ALL，从而能利用INDEX。

Answer 5

怎么进行表分析啊 ？
是这个嘛 analze table pf compute statistics for table for al indexed columns for all indexes;

Answer 6

表分析有两种办法，第一种用analyse table，是Oracle的传统写法，但已经不建议使用了。第二种用dbms_stats包，基本语法如下
begin
  -- 分析某个表
  dbms_stats.gather_table_stats(user,'MY_TABLE');
  -- 分析整个schema下的对象
  dbms_stats.gather_schema_stats(USER);
end;
/

Answer 7

用like实现不了regexp_like的功能,比如上面的regexp_like(dx1,'^174[0-9]{0,2}$')
可以匹配的是174后面所有的0位,1位和2位数字.而(dx1 like '174%' and substr(dx1,4,1) between '0' and '9' and substr(dx1,5,1) in ('0','2'))匹配的是
174后面第四位0-9中的一个数字,第五位0或2.
完全不一样啊

Answer 8

zcs_1是对的。我没仔细看，但还是可以用like+substr来完成的。
(dx1 like '174%' and (substr(dx1,4) is null or substr(dx1,4) between '0' and '99'))其实这是在检查dx1字段是否为174开头3/4/5位数字。

Answer 9

regexp_like正则表达式的功能很难用like来实现,
(dx1 like '174%' and (substr(dx1,4) is null or substr(dx1,4) between '0' and '99'))还是无法实现正则表达式的功能.
substr(dx1,4) between '0' and '99')只能限制第4位在0到9之间的数,第5位则没有限制.所以对于dx1为1740x,还是可以匹配上的.