Re: Bitmap Idx

ok .. point taken but histograms only work if you arent using bind vriables (heard that was changing in 8i). I have seen this behaviour before with 8i and bitmapped indexes. if you set the optimizer_index_cost_adj down to some value ie 10 or 20 it will pick up the bitmapped index.

by defualt the optimizer_index_cost_adj is set to 100, that means that the cost of access an index is full wieghted. if you set this value down it reduce the wieght (cost) the optimizer will associate with the access of the index.

for more trivia if you access dba_tab_col_statistics on the column(s) you have created the indexes on (bitmapped or not) check the density column the lower the number the greater the number of distinct values The Density Column is calculated as follows:

DENSITY = (SAMPLE_SIZE / DISTCNT) / SAMPLE_SIZE. If one performs the following calculation :

1/Density

The resulting value will be the DISTCNT.

Therefore Density will always be a real number between 0 and 1. The smaller the value the more unique values exist in the column.

not if you don't want to muck around with all those init.ora parameters you can always hint the code and create and outline from it.

"mel" <jmel_at_mailnews.com> wrote in message news:3b2a23d3$0$209$45beb828_at_newscene.com...
>
> You missed the point of what I was saying, in fact u missed the point of
 the
> orginal message which was why after creating a bit map oracle didnt use
 it.
>
> Also from the Oracle manual:
>
> http://www.oradoc.com/ora816/server.816/a76965/c20a_opt.htm
>
> Histograms are not useful for columns with the following characteristics:
>
> All predicates on the column use bind variables.
>
> The column data is uniformly distributed.
>
> The column is not used in WHERE clauses of queries.
>
> The column is unique and is used only with equality predicates.
>
>
>
>
> NOTE: that they are not useful if :
> The column data is uniformly distributed.
>
> which is exaclty the point i was trying to make! if the data is evenly
> distributed the cardinality doesn't matter much low or high or whatever.
> Oracle will assume that a full table scan is the best option.
>
>
> And as i noted if the column is not evenly distrubuted then he needs to
 use a
> historgram to make sure Oracle knows that and uses it appropriately.
 However,
> even with that it may determine that a full table scan is the best.
>
> So just because a column has low cardinality doesnt mean its the best
 candiate
> for a bit map. its distribution over a table is also critical
>
>
> also how its used with other columns with or without indexes. For
 example
> having a bitmap on a gender column and a date colum a query that say give
 me
> all males for the lst 2 years will probalby use a full scan
> but a query asking for all males in x month will use bitmap.
>
>
>
> In article <RPcW6.27760$3y3.4748939_at_typhoon.nyroc.rr.com>, "John Morais"
> <jmorais_at_rochester.rr.com> wrote:
> >I beg to differ Mel ... Bitmap indexes are especially useful if you have
 a
> >low cardinality.. I have attached part of an Oracle document in this
 message
> >when to use bitmapped indexes :
> >
> >3) When to use bitmapped Indexes
> > -----------------------------
> >
> >- the column has a low cardinality: few distinct value
> >
> >- bitmapped indexes are especially helpful for complex ad hoc queries
 with
> > lengthy WHERE clauses or aggregate queries (containing SUM, COUNT, or
> >other
> > aggregate functions)
> >
> >- the table has many rows (with 1.000.000 rows is 10.000 distinct values
> > possibly acceptable)
> >
> >- there are frequent, possibly ad hoc, queries on the table
> >
> >- the environment is data warehouse-oriented (DSS system). Bitmap indexes
> > are not ideal for online transaction processing (OLTP) environments
> > because of their locking behavior. It is not possible to lock a
> > single bitmap position.
> >
> > The smallest amount of a bitmap that can be locked is a bitmap
> > segment, which can be up to half a data block in size. Changing the
> > value of a row results in a bitmap segment becoming locked, in effect
> > blocking changes on a number of rows.
> >
> > This is a serious disadvantage when there are many UPDATE, INSERT
> > or DELETE statements being issued by users. It is not a problem when
> > data is loaded or updated in bulk actions, as in data warehouse
> > systems. This because the index needs to be rebuilt for every update,
> >inser or delete.. AND I MEAN REBUILT..
> >
> >"mel" <jmel_at_mailnews.com> wrote in message
> >news:3b2933cb$0$270$45beb828_at_newscene.com...
> >>
> >> Oracle determines usage depending on various factors.
> >>
> >> 1 oracle assumes that the values for a bit map are evenly distributed
 over
 the
> >> table.
> >>
> >>
> >> 2 If the cardinaltity is very low and since it assumes that it is
 evenly
> >> spread then it will assume that a full table scan if best. The reason
 being
> >> that if for example a column has 3 values a, b,c and they are evely
 spread
> >> over tha table i.e. every 3rd column is an a, then doing a full table
 scan
 is
> >> MUCH faster than reading the index and going to every 3rd row,
> >>
> >> 3 a hint will force the use even if it is not the best.
> >>
> >>
> >> so if you have a low cardinality column that is evely spread over the
 table,
> >> oracle will not use the index.
> >>
> >>
> >> what if it is low cardinality and not evenly spread. Well oracle needs
 to
> >> know this. You have to do an analyze WITH histograms so that oracle
 can
> >> develop a detailed knowledge of the distriubution.
> >>
> >> in that case let us assume that out of 1 miilion rows a occurs 500k,
 b
 490k
> >> and c 10k
> >>
> >> then when u query oracle will use a bit map for c but a full table can
 for a
> >> and b, because a full scan is cheaper than looking at the index and
 then a
> >> row.
> >>
> >>
> >> so do an analyze with historgrams and then let oracle decide on the
 plan
> >>
> >>
> >> In article <II2W6.27474$3y3.4364794_at_typhoon.nyroc.rr.com>, "John
 Morais"
> >> <jmorais_at_rochester.rr.com> wrote:
> >> >If your are running CBO first thing is analyze the table.
> >> >
> >> >"Sunder" <sunder.nochilur_at_us.bosch.com> wrote in message
> >> >news:3B28B11D.AD6B2A6_at_us.bosch.com...
> >> >> I have a table called TABLEA with columns COL1, COL2, COL3, COL4
> >> >> I have created Bitmap indexes on COL1 and COL2.
> >> >>
> >> >> When I do a explain plan on
> >> >>
> >> >> select count(!) from TABLEA where COL1 = 'A';
> >> >>
> >> >> The indexes are not used.
> >> >>
> >> >> If i do an explain plan on
> >> >>
> >> >> select /*+ INDEX(TABLEA,COL1_IDX) */
> >> >> count(1) from TABLEA where COL1 = 'A';
> >> >>
> >> >> The index is used.
> >> >>
> >> >> My question : how do I get the bitmap index to be used by default.
> >> >> (without using the hint ).
> >> >>
> >> >> Thanks
> >> >> Sunder
> >> >>
> >> >
> >> >
> >
> >
Received on Fri Jun 15 2001 - 14:13:11 CDT