DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: Tuning Queries - using bitmap index

Hybrid View

  1. #1
    Join Date
    Oct 2000
    Posts
    5

    Post

    Hi Group,

    I have created the customer table as:

    create table CUSTOMER(
    CustomerID VARCHAR2(6) CONSTRAINT customer_customerID_PK PRIMARY KEY,
    CompanyName VARCHAR2(30),
    LName VARCHAR2(10),
    FName VARCHAR2(10),
    Street VARCHAR2(20),
    City VARCHAR2(10),
    Zipcode VARCHAR2(7),
    Country VARCHAR2(10),
    Phone# VARCHAR2(8)
    );
    A Bitmap index is created on the country column :

    create bitmap index cust_bit_ctry_idx on customer(country);

    I try to run the explain plan for a query :
    Explain plan for Select country from customer where country = 'Canada';
    On running the utlxpls script it shows that the query uses a full table scan and does not utilize the bitmap index.

    My question is how can I make use of this bitmap index?

    Thanks in advance,
    SD

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    add a hint in your query to force rule based optimizer
    I think it´s something like

    select /*+ rule */ .........
    from ........
    where ........

  3. #3
    Join Date
    Oct 2000
    Posts
    5

    Well,
    Here I want to use the Cost based Optimizer.
    So, I gave the hint / * + Index(customer) */ in my query but it still uses full table scan instead of using the index.

    Thanks,
    SD.


  4. #4
    Join Date
    Sep 2000
    Posts
    128
    Have you analysed the table and Index...

    I had the same problem the other day too (on a country field too funny enough!), so I created a Histogram on the column and the optimiser chose the index again.

    analyze table customer compute statistics for columns country size 10;

    Terry

  5. #5
    Join Date
    Apr 2000
    Location
    roma
    Posts
    131
    if your index will not look performant your optimizer(cost based) will chose to don't use that index.

  6. #6
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    I think you left a blank space between * and +

    /*+ Index(customer) */

    not

    / * + Index(customer) */

  7. #7
    Join Date
    Sep 2000
    Posts
    384
    If you still have problems with the Bitmap Indexes then
    try partitions.There is no limit to the number of partitions a tables can have.

    Try that option also
    Radhakrishnan.M

  8. #8
    Join Date
    Sep 2000
    Posts
    384
    Do a partition on the Country.
    Radhakrishnan.M

  9. #9
    Join Date
    Oct 2000
    Posts
    5

    Thumbs up Tuning queries- using bitmap index

    Thank you all for your suggestions.
    My query does utilize the bitmap index by using the
    INDEX_COMBINE hint, without partitioning.

    Bye now,
    SD

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