Bitmap indexes

[Troels Arvin]

Hello,

At

http://en.wikipedia.org/wiki/Comparison_of_relational_database_management_systems

it's stated that DB2 doesn't have bitmap indexes. This seemed strange to
me, so I tried looking in the DB2 UDB (for LUW) manual without luck.
Googling turned up vector indexes, but it seems that those are only
available for the mainframe and AS/400 versions of UDB.

Can someone summarize UDB's support for indexes suitable for working with
OLAP, cubes, etc? - Especially: Do the various flavors of UDB still have
differences in their index type support?

--
Greetings from Troels Arvin, Copenhagen, Denmark

[peteh]

Re: Bitmap indexes

This is not a direct answer to the "bitmap index" question, but for
OLAP/cube-type performance structures, the multi-dimensional-cluster
(or MDC) certainly is applicable. This has also been described as a
"block index", but its real name is multidimensiona l cluster. Reseach
here or at ibm.com

Pete H

[kenfar]

Re: Bitmap indexes

Troels Arvin wrote:[color=blue]
> At
>http://en.wikipedia.org/wiki/Compari...gement_systems
> it's stated that DB2 doesn't have bitmap indexes. This seemed strange[/color]
to[color=blue]
> me, so I tried looking in the DB2 UDB (for LUW) manual without luck.
> Googling turned up vector indexes, but it seems that those are only
> available for the mainframe and AS/400 versions of UDB.[/color]

That article is wrong on a few accounts - lack of support for temp
tables, materialized views, and bitmaps. I just updated it (hurray for
wikis).

DB2 handles bitmap indexes differently than Oracle - it generates them
dynamically.

[color=blue]
> Can someone summarize UDB's support for indexes suitable for working
> with OLAP, cubes, etc?[/color]

DB2 supports b-tree indexes as well as dynamic bitmap indexes. I find
its clustering via MDC to generally be of more value than indexing with
OLAP however. And MDC can work in conjunction with inter-partition
parallelism.
[color=blue]
> Especially: Do the various flavors of UDB still have
> differences in their index type support?[/color]

Not that I'm aware of.

Here's a link that might be useful to you:

http://publib.boulder.ibm.com/infocenter/db2help/index.jsp?topic=/com.ibm.db2v8.doc/db2help/conhow2.htm

ken

[Troels Arvin]

Re: Bitmap indexes

On Tue, 08 Mar 2005 07:37:24 -0800, kenfar wrote:[color=blue]
> That article is wrong on a few accounts - lack of support for temp tables,
> materialized views, and bitmaps. I just updated it (hurray for wikis).[/color]

:-)
[color=blue]
> DB2 supports b-tree indexes as well as dynamic bitmap indexes. I find its
> clustering via MDC to generally be of more value than indexing with OLAP
> however. And MDC can work in conjunction with inter-partition
> parallelism.[/color]

Have printed some MDC documentation for later reading; thanks.
[color=blue]
> Here's a link that might be useful to you:
> http://publib.boulder.ibm.com/infoce...lp/conhow2.htm[/color]

I get a "The requested subject is not available" error message from that
URL.

--
Greetings from Troels Arvin, Copenhagen, Denmark

[Mark Townsend]

Re: Bitmap indexes

kenfar wrote:
[color=blue]
>
> That article is wrong on a few accounts - lack of support for temp
> tables, materialized views, and bitmaps. I just updated it (hurray for
> wikis).
>
> DB2 handles bitmap indexes differently than Oracle - it generates them
> dynamically.
>[/color]

I guess it comes down to what your definition of an index is.

To me an index is stored for reuse multiple times, so a "dynamicall y
generated bit mapped index" is a little bit of anathema. As a corollary,
does a hash table built during a hash join then also qualify as a 'hash
index' ?

Note also the wiki definition of an index.

"When talking about databases, indexing is a technique used by most
current database management systems to speed up particular kinds of
queries (usually by internally generating and storing redundant
information to more quickly locate table entries)."

[kenfar]

Re: Bitmap indexes

>> DB2 handles bitmap indexes differently than Oracle - it generates
them[color=blue][color=green]
>> dynamically.[/color]
> I guess it comes down to what your definition of an index is.[/color]

The pros & cons of each strategy are up for debate. But given past
experience, not a pleasant debate. Since it would probably be nothing
more than a rehashing of a thread for a year ago, anyone can google
for it if they're interested.
[color=blue]
> Note also the wiki definition of an index.[/color]

Wikipedia is pretty cool, but its info is spotty.

[kenfar]

Re: Bitmap indexes

Sorry about that, didn't realize that the infocenter url didn't have
the search criteria in it.

Here's the full url to infocenter:

http://publib.boulder.ibm.com/infocenter/db2help/index.jsp?topic=/com.ibm.db2v8.doc/db2help/conhow2.htm

[color=blue]
>From there search on star joins - and you'll find bitmap index[/color]
operations described within the article 'Strategies for selecting
optimal joins'

ken

[Troels Arvin]

Re: Bitmap indexes

On Tue, 08 Mar 2005 09:35:41 -0800, kenfar wrote:
[color=blue]
> Here's the full url to infocenter:
> http://publib.boulder.ibm.com/infoce...lp/conhow2.htm
>[color=green]
>>From there search on star joins - and you'll find bitmap index[/color]
> operations described within the article 'Strategies for selecting optimal
> joins'[/color]

This URL looks like it's e-mail/news-safe:

http://publib.boulder.ibm.com/infocenter/db2help/topic/com.ibm.db2.udb.doc/admin/c0005314.htm

--
Greetings from Troels Arvin, Copenhagen, Denmark

[Mark A]

Re: Bitmap indexes

"Mark Townsend" <markbtownsend@ comcast.net> wrote in message[color=blue]
>
> I guess it comes down to what your definition of an index is.
>
> To me an index is stored for reuse multiple times, so a "dynamicall y
> generated bit mapped index" is a little bit of anathema. As a corollary,
> does a hash table built during a hash join then also qualify as a 'hash
> index' ?
>
> Note also the wiki definition of an index.
>
> "When talking about databases, indexing is a technique used by most
> current database management systems to speed up particular kinds of
> queries (usually by internally generating and storing redundant
> information to more quickly locate table entries)."
>[/color]
If one were to look at decision support benchmarks published by the TPC, one
would see that DB2 does very well in terms of performance against all
competitors. Database vendors use different methods to achieve their
results, and 99.99% of the time it is foolish for customers to try and
predict how an database product will perform just by looking at its
"feature" list.

http://www.tpc.org/tpch/results/tpch_perf_results.asp

If you don't like the TPC benchmark, conduct your own. Don't rely on
"feature list" to understand the internals of how the database achieves its
performance.

[Mark Townsend]

Re: Bitmap indexes

>From there search on star joins - and you'll find bitmap index[color=blue]
> operations described within the article 'Strategies for selecting
> optimal joins'[/color]

Right - and that's sort of my point. In Oracle parlance we would call
this a bit mapped join (for which Oracle provides bit mapped join
indexes). Orthogonal to but not the same as a bit mapped index. Note
that it helps optimize the "fact to multiple dimensions" join problem,
especially where your predicates are based on the dimensional values (as
do bit mapped join indexes in Oracle), but doesn't help much with
census style queries solely against the fact table (where a bit mapped
index would).

Redbrick does bit map indexes a la Oracle, and IBM now owns Redbrick,
which presumably gives them a clear and present IPR to do full BMIs if
they want to. So perhaps you will see 'stored' BMIs in some future
version of DB2 ?

[Mark Townsend]

Re: Bitmap indexes

Mark A wrote:
[color=blue]
> If one were to look at decision support benchmarks published by the TPC, one
> would see that DB2 does very well in terms of performance against all
> competitors. Database vendors use different methods to achieve their
> results, and 99.99% of the time it is foolish for customers to try and
> predict how an database product will perform just by looking at its
> "feature" list.[/color]

Strangely enough, I don't think Oracle uses BMIs in the TPC-H. I'm
guessing that generally the data model and queries as defined in H don't
benefit too much from them.
[color=blue]
> Don't rely on
> "feature list" to understand the internals of how the database achieves its
> performance.[/color]

Too true. However, it pays to know what a specific feature actually does
in a specific vendors implementation to understand how it can affect
performance. We all, unfortunately, tend to overload terms, thereby
muddying the waters a little.