-
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
-
add a hint in your query to force rule based optimizer
I think it´s something like
select /*+ rule */ .........
from ........
where ........
-
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.
-
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
-
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) */
not
/ * + 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
Radhakrishnan.M
-
Do a partition on the Country.
Radhakrishnan.M
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|