I have created the customer table as:
create table CUSTOMER(
CustomerID VARCHAR2(6) CONSTRAINT customer_customerID_PK PRIMARY KEY,
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,
add a hint in your query to force rule based optimizer
I think itīs something like
select /*+ rule */ .........
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.
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;
if your index will not look performant your optimizer(cost based) will chose to don't use that index.
I think you left a blank space between * and +
/*+ Index(customer) */
/ * + Index(customer) */
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
Do a partition on the Country.
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.
Click Here to Expand Forum to Full Width