Bitmap index on column with 200 distinct keys

[goodmans]

Pleasle suggest me the limit of distinct keys in a bitmap index.

I have a big table
600 cols
30m rows.

And I have a column with 200 distinct keys. Which index is recommanded on it?

Your help is appreciated.

Regards
G

 

[Dagon]

Are you proposing to create a concatenated bitmap index on the 200 columns ? I would not recommend that. Bitmap indexes are meant to be used in combination with each other and the general rule is that you should index only a single column rather than multiple columns. They work well in situations where none of the attributes individually are very selective but several in combination are.

 

[goodmans]

Nope, I have a column named "cmp_no" which contains 200 distinct keys. Shall i create bitmap index on it?

Regards
G

 

[Dagon]

A bitmap index seems reasonable for that type of column. As I said, BMIs were intended to be used across several columns, although you can get benefit from having an index on just one column if the cardinality of the value used in the query is low.

I'd recommend that you include the "for all indexed columns" option to get histogram information when running DBMS_STATS. Also, you may wish to review some of the queries to see if it would be better to use hard-coded values rather than bind variables (e.g. column_value = 'X' rather than colum_value = :b1). Since a bind variable will give no information to Oracle on the actual value, it will use a generic plan rather than tailoring the plan to the specific value.

Obviously, there are also downsides to this because of increased parsing time and less reuse in the SGA, so it would be better for one-off and occasional queries rather than queries which are run continually during the day.

 

[goodmans]

Thanks mate.

But the problem is I am joining this big table with another big table. Its just like joining sales(big) table with sales details(big) again. When I am joining two full big tables it seems like oracle optimizer is not taking the indexes and doing full table scan. I dont know query taking same time with indexes and with out indexes on these both tables as its scanning the full table. But we dont want to take any change so creating only bitmap indexes on these two big tables.

Regards
G

 

[Dagon]

Are you saying you want to use this column (with 200 distinct values) to join two tables together ? I don't see how that's possibly since a joining column would have to be unique.

The BMI will probably only be useful as restriction on the number of rows (e.g. column_name = 'X'). I've never seen them used when joining tables, since they are not really designed for the sort of unique cardinality that a joining column would have.

If you're joining two large tables together, it does probably make sense for Oracle to do a scan of both of them. To join millions of rows in one table to millions of rows in another table using an index would probably be slower than scanning both tables and then doing a merge or hash join. It all depends on what other conditions you have on the query and whether these can be used to reduce the number of rows coming back from either table.

 

[goodmans]

no no sorry i guess i am confusing you .

I got
Table A with 600 columns, 20-million rows.
Table B with 10 column, 30-million rows.

Both tables got a column called.
product_code (possible distinct keys are 200 for this column in both tables.

My query is like

select
a.produce_id, b.sales
from a , b
where
a.bk=b.bk and
a.product_id=b.product_id

(I got bitree index on BK column of both tables),

Since I am already having bitree indexes on both tables, and it is part of query, does the existence of bitmap index on product_id make any impact? do I actually need it?

Regards
G

 

[Dagon]

I think it's highly unlikely Oracle would use the bitmap index for a query of that sort, but you could confirm it by running explain plan or SQL*Trace.

 

[carp]

If you haven't done so already, you might try placing a BMI on the columns of both tables and then see if Oracle uses the indexes.

Often times, the overhead of merging non-BMI with a BMI is so high, the optimizer determines a FTS is actually more efficient.