SEO

#1 (**permalink**) 02-26-2008, 07:25 AM

Hi,

Does indexing a very big table (about 5Mio records) on the columnA
which can hold only values Y,N,<NULL> make sense?

thanks
chris

#2 (**permalink**) 02-26-2008, 07:25 AM

ciapecki wrote:
> Hi,
>
> Does indexing a very big table (about 5Mio records) on the columnA
> which can hold only values Y,N,<NULL> make sense?
>
Yes, that's what bitmap indexes were made for.

Thomas

#3 (**permalink**) 02-26-2008, 07:25 AM

On 3 Aug., 09:54, Thomas Kellerer <JUAXQOSZF...@spammotel.com> wrote:
> ciapecki wrote:
> > Hi,
>
> > Does indexing a very big table (about 5Mio records) on the columnA
> > which can hold only values Y,N,<NULL> make sense?
>
> Yes, that's what bitmap indexes were made for.
>
> Thomas

Thanks Thomas,

Is there any rule when to use BMap and when BTree?
I know for a few values BMap, for many different ones BTree, but maybe
there is some mathematical equation that helps to determine which
Index is better.

chris

#4 (**permalink**) 02-26-2008, 07:25 AM

ciapecki wrote:
> On 3 Aug., 09:54, Thomas Kellerer <JUAXQOSZF...@spammotel.com> wrote:
>> ciapecki wrote:
>>> Hi,
>>> Does indexing a very big table (about 5Mio records) on the columnA
>>> which can hold only values Y,N,<NULL> make sense?
>> Yes, that's what bitmap indexes were made for.
>>
>> Thomas
>
> Thanks Thomas,
>
> Is there any rule when to use BMap and when BTree?
> I know for a few values BMap, for many different ones BTree, but maybe
> there is some mathematical equation that helps to determine which
> Index is better.

Apart from comparing execution plans, I suggest to read the relevant
chapter in the concepts manual:

http://download-uk.oracle.com/docs/c...htm#sthref1008

Quote:
"If the number of distinct values of a column is less than 1% of the
number of rows in the table, or if the values in a column are repeated
more than 100 times, then the column is a candidate for a bitmap index."

Thomas

#5 (**permalink**) 02-26-2008, 07:25 AM

On 3 Aug., 10:03, Thomas Kellerer <JUAXQOSZF...@spammotel.com> wrote:
> ciapecki wrote:
> > On 3 Aug., 09:54, Thomas Kellerer <JUAXQOSZF...@spammotel.com> wrote:
> >> ciapecki wrote:
> >>> Hi,
> >>> Does indexing a very big table (about 5Mio records) on the columnA
> >>> which can hold only values Y,N,<NULL> make sense?
> >> Yes, that's what bitmap indexes were made for.
>
> >> Thomas
>
> > Thanks Thomas,
>
> > Is there any rule when to use BMap and when BTree?
> > I know for a few values BMap, for many different ones BTree, but maybe
> > there is some mathematical equation that helps to determine which
> > Index is better.
>
> Apart from comparing execution plans, I suggest to read the relevant
> chapter in the concepts manual:
>
> http://download-uk.oracle.com/docs/c...102/b14220/sch...
>
> Quote:
> "If the number of distinct values of a column is less than 1% of the
> number of rows in the table, or if the values in a column are repeated
> more than 100 times, then the column is a candidate for a bitmap index."
>
> Thomas

this is great,

thanks a lot Thomas

chris

#6 (**permalink**) 02-26-2008, 07:25 AM

> > >>> Does indexing a very big table (about 5Mio records) on the columnA
> > >>> which can hold only values Y,N,<NULL> make sense?
> > >> Yes, that's what bitmap indexes were made for.

FYI , you can also index only the N ( or Y) value's in the column.
This is done if e.g. the boolean is an indicator whether a row has
gone through a processing run or not, and the application just need to
find the N values.
Bitmap indexes are not ok if the table is updated intensively.

Helma

#7 (**permalink**) 02-26-2008, 07:25 AM

On 3 Aug., 10:21, Helma <bad_elef...@hotmail.com> wrote:
> > > >>> Does indexing a very big table (about 5Mio records) on the columnA
> > > >>> which can hold only values Y,N,<NULL> make sense?
> > > >> Yes, that's what bitmap indexes were made for.
>
> FYI , you can also index only the N ( or Y) value's in the column.
> This is done if e.g. the boolean is an indicator whether a row has
> gone through a processing run or not, and the application just need to
> find the N values.
> Bitmap indexes are not ok if the table is updated intensively.
>
> Helma

Hi Helma,

It will be actually a view and will be refreshed everyday (once a
day).
But thanks for the warning.

BTW. how can you index only N's for example?

chris

#8 (**permalink**) 02-26-2008, 07:25 AM

On Aug 3, 4:47 am, ciapecki <ciape...@gmail.com> wrote:
> It will be actually a view and will be refreshed everyday (once a
> day).
> But thanks for the warning.
>
> BTW. how can you index only N's for example?
>
> chris

Before deciding to use bitmap indexes, you may want to take a look at
a couple articles that describe the characteristics of bitmap indexes:
http://jonathanlewis.wordpress.com/2...itmap-indexes/
http://jonathanlewis.wordpress.com/2...ing-in-action/
http://www.dbazine.com/oracle/or-articles/jlewis3

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.

#9 (**permalink**) 02-26-2008, 07:25 AM

> BTW. how can you index only N's for example?

Hello Chris,

I hope i am not unkind to you, but for syntax matters, may i redirect
you to the manuals?

H.
Hint: you need to look at function based indexes, creating a function
that only returns a value for the recordvalues you need to index.

#10 (**permalink**) 02-26-2008, 07:25 AM

"Thomas Kellerer" <JUAXQOSZFGQQ@spammotel.com> wrote in message
news:5hg5aiF3kia4nU1@mid.individual.net...
> ciapecki wrote:
>> Hi,
>>
>> Does indexing a very big table (about 5Mio records) on the columnA
>> which can hold only values Y,N,<NULL> make sense?
>>
> Yes, that's what bitmap indexes were made for.
>

Hi Thomas

A Bitmap index is of no use if both Y and N are roughly evenly distributed
and you have no other predicate in the query.

Returning approximately 2.5 millions rows through a bitmap index would be
dramatically slower than a full table scan.

A single bitmap index only would be useless in this scenario, even more so
if the table is subjected to any transactional based DML load.

Cheers

Richard

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

indexing a column with only 2 or 3 values