select distinct mbrid,dos from fi05 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 dx4 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')
or dx6 in('40201', '40211', '40291', '40401', '40411', '40491','4280', '4281', '42820','42821','42822','42823',' 42840','42841','42842','42843')
or dx7 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 dx9 in('40201', '40211', '40291', '40401', '40411', '40491','4280', '4281', '42820','42821','42822','42823',' 42840','42841','42842','42843')
)
像这样的一个查询,应如何建立索引啊,建立什么索引才能有效的提高执行的效率呢??
怎么能通过plsql中的执行计划看出来 用没有用到索引啊?
这个是我自己建的单个的dx1到dx9位图索引 的执行计划
但是我看不太懂 不知道用没用到这个索引啊……SELECT STATEMENT, GOAL = ALL_ROWS Cost=14546 Cardinality=1 Bytes=17
FILTER
HASH GROUP BY Cost=14546 Cardinality=1 Bytes=17
VIEW Object owner=HIT Cost=14546 Cardinality=85771 Bytes=1458107
HASH UNIQUE Cost=14546 Cardinality=85771 Bytes=17583055
TABLE ACCESS FULL Object owner=HIT Object name=FI05 Cost=10701 Cardinality=85771 Bytes=17583055
对于这样的number类型的数据,而且基本不唯一的。建bitmap index位图索引比较好
SELECT STATEMENT, GOAL = ALL_ROWS Cost=14546 Cardinality=1 Bytes=17
FILTER
HASH GROUP BY Cost=14546 Cardinality=1 Bytes=17
VIEW Object owner=HIT Cost=14546 Cardinality=85771 Bytes=1458107
HASH UNIQUE Cost=14546 Cardinality=85771 Bytes=17583055
TABLE ACCESS FULL Object owner=HIT Object name=FI05 Cost=10701 Cardinality=85771 Bytes=17583055