I have a large table which contains some 3 million records.
The table has been partitioned using the statement_date. The table will contain data for 12 months. The following fields will drive my query which are low cardinality columns .These are the max cardinality.
statement_date --> cardinality 12
CHANNEL_CODE --> cardinality 50
CAMPAIGN_CODE --> cardinality 100
source_code --> cardinality 5000
sub_product_code --> cardinality 5
DATE_OPENED --> cardinality 100
The query will be a dynamic query ..i will use any 3 fields of these 6 columns.
I want to know whether creating bitmapping indexes on every field will be effecient or creating a single bitmap index on these six fields..
Create standalone bitmap indexes but only on the columns with low cardinality. By low, I mean at most 5.
5 distinct values on a 3 million rows table as the upper acceptable limit for bitmap indexes??? I realy don't think so. Definitely the columns with cardinality of 100 are perfectly acceptable candidates for bitmap indexes here. And in case that distinct values are highly clustered together than even clumn with cardinality of 5000 might be a good candidate for bitmap (particularly if it will be commonly used in combination with other bitmaps in execution plans)!
Originally posted by julian
By low, I mean at most 5.
However I strongly agree with Julian on the point that you should create sepparate single-column bitmaps for those columns, provided that this table is not subject to frequent concurent changes (kind of OLTP system).
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
Click Here to Expand Forum to Full Width