Selectivity of Indexes

[majub]

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 !!!

[julian]

Collect histograms for the bitmapped column in order to help the optimizer.

[majub]

Dear Julian,

Thanks for your reply. How can I collect the histogram for the bitmap indexed column? Please advise.

Thanks and regards

Maju Bellamin
Bahrain

[julian]

Originally posted by majub
Dear Julian,

Thanks for your reply. How can I collect the histogram for the bitmap indexed column? Please advise.

Thanks and regards

Maju Bellamin
Bahrain

Assume that you have the table EMP with the column DEPT_ID which has a bitmap index on it.

If you want to generate the table statistics on EMP and column statistics on the column DEPT_ID with maximum number of buckets being 90, you run:

ANALYZE TABLE emp COMPUTE STATISTICS FOR TABLE FOR COLUMN dept_id SIZE 90;

Oracle has a default number of buckets equal to 75. For default value you simply run:

ANALYZE TABLE emp COMPUTE STATISTICS FOR COLUMN dept_id;

[jmodic]

Originally posted by majub
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?

In your second case, optimizer can get the complete result set just by examining your bitmap index. Since only JOB is listed in the select list, it can obtain it from the bitmap index and there is no need to wisit table at all. So optimizer figured out that this is the cheapest method, so it went for it.

But in your first case you wanted also other columns in the result set. Optimizer could use bitmap index because of your WHERE clause, but then for each entry found in the index it should also wisit corresponding table blocks to get other columns from your select list. In this case optimizer figured it out that it is cheaper to simply perform a full table scan.