number类型建立一个bitmap索引
SELECT * FROM test WHERE FLAG = 1
执行结果:
TABLE ACCESS FULL 12573
分析:出现全表扫描而没有执行位图索引,可能是flag为1的数据量和表的数据量相当,所以优化器将其优化掉了.
varchar2类型字段上建立一个bitmap索引
SELECT * FROM INF_CONSUMER WHERE DISTRIBUTION_FLAG = '1'
结果(执行计划里到外):
BITMAP INDEX SINGLE VALUE 0
TABLE ACCESS BY INDEX ROWID 4
SELECT * FROM INF_CONSUMER WHERE DISTRIBUTION_FLAG = 1 类型的字段转换导致索引失效
结果:
TABLE ACCESS FULL 12617
NUMBER与varchar比较,varchar始终会被自动转换为number
select * from t where a = '111111'
a为number类型
经过oracle自动转换为:
select * from t where a = to_number('111111')
select * from t where a = 111111
a为varchar类型
经过oracle自动转换为:
select * from t where to_number(a) = 111111
SELECT * FROM test WHERE FLAG = 1
执行结果:
TABLE ACCESS FULL 12573
分析:出现全表扫描而没有执行位图索引,可能是flag为1的数据量和表的数据量相当,所以优化器将其优化掉了.
varchar2类型字段上建立一个bitmap索引
SELECT * FROM INF_CONSUMER WHERE DISTRIBUTION_FLAG = '1'
结果(执行计划里到外):
BITMAP INDEX SINGLE VALUE 0
TABLE ACCESS BY INDEX ROWID 4
SELECT * FROM INF_CONSUMER WHERE DISTRIBUTION_FLAG = 1 类型的字段转换导致索引失效
结果:
TABLE ACCESS FULL 12617
NUMBER与varchar比较,varchar始终会被自动转换为number
select * from t where a = '111111'
a为number类型
经过oracle自动转换为:
select * from t where a = to_number('111111')
select * from t where a = 111111
a为varchar类型
经过oracle自动转换为:
select * from t where to_number(a) = 111111