My understanding is that in DB2, the optimiser makes the decision if an index should be dense, sparse, reversed, bitmap etc.

Is this correct?
Is there any way when creating an index to specify the type of index you want?

and one last one...

How do I ascertain if my index is dense, sparse, reversed, bitmap etc?

The optimizer makes the decision of whether to use an index or not. DB2 can create bitmaps on the fly and use them if the optimizer wants to use that access method to find the intersection or union of two or more indexes, but they are not permanent indexes and there is nothing the DBA does to make this happen. You can see whether a bit map is created at execution time by looking at the EXPLAIN for the SQL statement. I believe they are usually indicated by an IXAND, IXOR, etc, in the EXPLAIN.

DB2 can do reverse scans if allowed in the CREATE INDEX statement. The order of the columns in an index (ascending or descending) is determined when the index is created by the DBA in the CREATE INDEX.

Not sure what you mean by dense or sparse.

However, if you use the ORGANIZE BY DIMENSIONS in the CREATE TABLE statement, a clustering block index is automatically maintained for each specified dimension, and a block index, consisting of all columns used in the clause, is maintained if none of the clustering block indexes includes them all.

If you use ORGANIZE BY KEY SEQUENCE in the CREATE TABLE statement, the table is organized in ascending key sequence with a fixed size based on the specified range of key sequence values, and each possible key value in the defined range has a predetermined location in the physical table. Maybe this is what you mean by sparse index, but not sure.

Thanks Marcus.

I think REVERSE SCANS are different to REVERSE INDEXES. Aren't they not?
REVERSE INDEXES are used to avoid contention. Reverse scans are for descending order in queries. I might have that *rseways. So correct me if I am wrong.

Dense indexes are when the index points to an exact row in a table.
Sparse indexes are when the index points to the block / page / something or larger granualrity than a single row.

Wikipedia is pretty good here: Index (database) - Wikipedia, the free encyclopedia

Thanks for you answer and if you have any more great.

Based on the definition of Reverse Indexes in the Wikipedia article you quoted, DB2 does not have those as far as I know unless it is doing something behind the scenes that we don't know about. I also don't believe that DB2 has the contention problems that are being addressed by Reverse Indexes. However, it may be more of problem using a DB2 PureScale Cluster (but PureScale handles such page contention significantly better than Oracle RAC).

Sparse indexes would be for MDC, and regular indexes in DB2 would be Dense. However, the article seems to be written by someone without specific knowledge of DB2.

the article I think is more at a higher level to cover all dbs.

I think that the article describes general concept of indexes and basic characteristics of various type of indexes,
not covered deep and detail of indexes especially its implementations.

For example:
Each DBMS have more options to adapt various environments and requirements.
DB2 has "PAGE SPLIT HIGH" option for key values increasing with each insertion which may be used same purpose as Reverse index.

excellent point. Agree.