Hi,

A quick question on selectivity of indexes. I 've a table with 3 columns "id", "name" and "job". "id" is the primary key of the table.

I created a bitmap index on the column "job" since its values are mostly repeated.

With this basic structure, I tried to get the execution plan using

explain plan set
statement_id = 'Test'
for
select job, id, name from test
where job = 'VICE PRESIDENT'

for which I got the result as:

SELECT STATEMENT COST= 7
TABLE ACCESS FULL TEST

But, when I change the query to the one as follows, it uses the bitmap index.

explain plan set
statement_id = 'Test'
for
select job from test
where job = 'VICE PRESIDENT'

SELECT STATEMENT COST= 1
BITMAP CONVERSION TO ROWIDS
BITMAP INDEX SINGLE VALUE JOB_IDX

In the first case, the optimizer does a full table scan whereas in the second case it makes use of the bitmap index. If changing columns in "select" list affects the optimizer access path, what 's the point in using a bitmap index?

I 'd appreciate if you can clarify this point.

Thanks in advance

Have a great day !!!