Oracle 8.1.7 is used, and the optimizer is using rule-based instead of cost-based, can bitmap index be used?
select t2.client_id, t1.APPROVE_USER, to_char(t1.APPROVE_TIME, 'YYYYMMDDHHMMSS') APPROVE_TIME
from cacc_amendment_history t1,
(select a.client_id, max(b.amendment_id) as amendment_id
from clients_amendment a, cacc_amendment_history b
where a.amendment_id = b.amendment_id
and b.APPROVE_STATUS = 'APP'
group by a.client_id) t2
where t1.amendment_id = t2.amendment_id
order by t2.client_id;
I do explain and found that table scan is used on table cacc_amendment_history, if I create a index on cacc_amendment_history(approve_status), index scan is used. However, if I create a bitmap index on cacc_amendment_history(approve_status), table scan is used.