Selectivity of Indexes
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Selectivity of Indexes

Hybrid View

  1. #1
    Join Date
    May 2002
    Posts
    2
    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 !!!

  2. #2
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Collect histograms for the bitmapped column in order to help the optimizer.

    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g
    email: ocp_9i@yahoo.com

  3. #3
    Join Date
    May 2002
    Posts
    2

    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

  4. #4
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938

    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
    email: ocp_9i@yahoo.com

  5. #5
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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
  •  



Click Here to Expand Forum to Full Width