STAR_TRANSFORMATION_ENABLED如何设置

修改的话用
alter session set star_transformation_enabled=true;

我用的是alter system 可以吗
位图索引用修改这个嘛？

alter system需要用sys用户登录才可以修改

在你上个帖子中提过了
http://topic.csdn.net/u/20090925/09/8b6ceb1e-5d56-469d-80d5-ec0962f3408f.html
运行一个查询，如果只需表中相对较少的数据，CBO会使用索引；但是如果估计通过索引获取的行数超过了一个阈值，优化器将开始全表扫描。
=======================================
所以能提供一下
四个字段的时候查询返回数量多少？
五个字段的时候返回数量是多少？

再用/*+ INDEX (表或或表的别名索引名) */强制使用索引后把执行计划贴出来看看

我的这个sql语句是一个很大的查询。里面有很多的查询条件
例如
select  mbrid,min(dos) min_dos
from
(select distinct mbrid,dos from fo05 where
(
dx1 in('40201', '40211', '40291', '40401', '40411',  '40491','4280', '4281', '42820','42821','42822','42823',' 42840','42841','42842','42843')
or dx2 in('40201', '40211', '40291', '40401', '40411',  '40491','4280', '4281', '42820','42821','42822','42823',' 42840','42841','42842','42843')
or dx3 in('40201', '40211', '40291', '40401', '40411',  '40491','4280', '4281', '42820','42821','42822','42823',' 42840','42841','42842','42843')
or dx8 in('40201', '40211', '40291', '40401', '40411',  '40491','4280', '4281', '42820','42821','42822','42823',' 42840','42841','42842','42843')
--or dx5 in('40201', '40211', '40291', '40401', '40411',  '40491','4280', '4281', '42820','42821','42822','42823',' 42840','42841','42842','42843')
)
--select count(dx5) from fo05
and
(
    (
     ((cpt between '99201' and '99205') or (cpt between '99211' and '99215') or (cpt between '99241' and '99245') or (cpt between '99271' and '99275')
          or (cpt between '99301' and '99313') or (cpt between '99315' and '99316') or (cpt between '99318' and '99337') or (cpt between '99341' and '99350')
          or (cpt between '99354' and '99355') or (cpt between '99381' and '99387') or (cpt between '99391' and '99397') or (cpt between '99401' and '99429')
          or (cpt= '99450') or (cpt between '99455' and '99456') or  (cpt between '99217' and '99220')  or (cpt between '99234' and '99236'))
          or ((cpt1 between '99201' and '99205') or (cpt1 between '99211' and '99215') or (cpt1 between '99241' and '99245') or (cpt1 between '99271' and '99275')
          or (cpt1 between '99301' and '99313') or (cpt1 between '99315' and '99316') or (cpt1 between '99318' and '99337') or (cpt1 between '99341' and '99350')
          or (cpt1 between '99354' and '99355') or (cpt1 between '99381' and '99387') or (cpt1 between '99391' and '99397') or (cpt1 between '99401' and '99429')
          or (cpt1= '99450') or (cpt1 between '99455' and '99456')or  (cpt1 between '99217' and '99220')  or (cpt2 between '99234' and '99236'))
          ....
or ((cpt9 between '99201' and '99205') or (cpt9 between '99211' and '99215') or (cpt9 between '99241' and '99245') or (cpt9 between '99271' and '99275')
          or (cpt9 between '99301' and '99313') or (cpt9 between '99315' and '99316') or (cpt9 between '99318' and '99337') or (cpt9 between '99341' and '99350')
          or (cpt9 between '99354' and '99355') or (cpt9 between '99381' and '99387') or (cpt9 between '99391' and '99397') or (cpt9 between '99401' and '99429')
          or (cpt9= '99450') or (cpt9 between '99455' and '99456')or  (cpt9 between '99217' and '99220')  or (cpt9 between '99234' and '99236'))
          )
    or
    (
    ((rev between '0500' and '0529') or (rev between '0570' and '0599') or (rev between '0770' and '0779') or (rev between '0820' and '0859' )
    or (rev between '0982' and '0983') or (rev = '0882'))
    or((rev1 between '0500' and '0529') or (rev1 between '0570' and '0599') or (rev1 between '0770' and '0779') or (rev1 between '0820' and '0859')
    or (rev1 between '0982' and '0983') or (rev1 = '0882'))
    or ((rev2 between '0500' and '0529') or (rev2 between '0570' and '0599') or (rev2 between '0770' and '0779') or (rev2 between '0820' and '0859')
    or (rev2 between '0982' and '0983') or (rev2 = '0882'))
    or ((rev3 between '0500' and '0529') or (rev3 between '0570' and '0599') or (rev3 between '0770' and '0779') or (rev3 between '0820' and '0859')
    or (rev3 between '0982' and '0983') or (rev3 = '0882'))
    or ((rev4 between '0500' and '0529') or (rev4 between '0570' and '0599') or (rev4 between '0770' and '0779') or (rev4 between '0820' and '0859')
    or (rev4 between '0982' and '0983') or (rev4 = '0882'))
    or ((rev5 between '0500' and '0529') or (rev5 between '0570' and '0599') or (rev5 between '0770' and '0779') or (rev5 between '0820' and '0859')
    or (rev5 between '0982' and '0983') or (rev5 = '0882'))
    or ((rev6 between '0500' and '0529') or (rev6 between '0570' and '0599') or (rev6 between '0770' and '0779') or (rev6 between '0820' and '0859')
    or (rev6 between '0982' and '0983') or (rev6 = '0882'))
    or ((rev7 between '0500' and '0529') or (rev7 between '0570' and '0599') or (rev7 between '0770' and '0779') or (rev7 between '0820' and '0859')
    or (rev7 between '0982' and '0983') or (rev7 = '0882'))
    or ((rev8 between '0500' and '0529') or (rev8 between '0570' and '0599') or (rev8 between '0770' and '0779') or (rev8 between '0820' and '0859')
    or (rev8 between '0982' and '0983') or (rev8 = '0882'))
    or ((rev9 between '0500' and '0529') or (rev9 between '0570' and '0599') or (rev9 between '0770' and '0779') or (rev9 between '0820' and '0859')
    or (rev9 between '0982' and '0983') or (rev9 = '0882'))
  )
) ) group by mbrid having count(mbrid)>=2;
是这样的一个查询下面的那些cpt 还有rev都没有问题
只有那个dx  只能是4个字段 5个的时候就不可以了  就不再执行索引了

这个是执行计划你们看看
这个是五个字段的时候，也就是不执行索引的时候SELECT STATEMENT, GOAL = ALL_ROWS Cost=25294 Cardinality=405 Bytes=6885 Time=304
FILTER
  HASH GROUP BY Cost=25294 Cardinality=405 Bytes=6885 Time=304
   VIEW Object owner=HIT Cost=25294 Cardinality=100683 Bytes=1711611 Time=304
    HASH UNIQUE Cost=25294 Cardinality=100683 Bytes=18022257 Time=304
     TABLE ACCESS FULL Object owner=HIT Object name=FO05 Cost=21333 Cardinality=100683 Bytes=18022257 Time=256

调试易

STAR_TRANSFORMATION_ENABLED如何设置

解决方案 »