-
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 !!!
-
Collect histograms for the bitmapped column in order to help the optimizer.
Oracle Certified Master
Oracle Certified Professional 6i,8i,9i,10g,11g,12c
email: ocp_9i@yahoo.com
-
Selectivity of Indexes
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
-
Re: Selectivity of Indexes
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:
Code:
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:
Code:
ANALYZE TABLE emp COMPUTE STATISTICS FOR COLUMN dept_id;
Oracle Certified Master
Oracle Certified Professional 6i,8i,9i,10g,11g,12c
email: ocp_9i@yahoo.com
-
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.
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|