[DB2-L] Mathematician wanted - number of potential indexes

PUSCH Othmar

[DB2-L] Mathematician wanted - number of potential indexes
Hi Phil, dear out there !

From my point of view ya get the MAXIMUM number of candidate indexes (Ignoring the ascending/descending choice) by means of a “variation_without_retry” (= a permutation of all elements of all combinations of a given class) … the simple formula:

∑ nPk = ∑ (n! / (n-k)!) … for k = 1 and k_has_to_run_till_n …. n ε N / (0)

e.g.: n =2 --> 2!/1! + 2!/0! = 2 + 2 = 4
e.g.: n=3 ---> 3!/2! + 3!/1! + 3!/0! = 6/2 + 6/1 + 6/1 = 15

Phil, please keep in mind:

1) n! speeds up really quick … please see, according Stirling’s formula, below how strong it goes up !

2) I’m only a z/OS-guy (BCP, JES2, RACF, TSS, ACF/2, VTAM and TCPIP) but I believe in that there is/was a limit of the number of columns in an index key (64 or so what) --> a DB2-DBA knows this better.

Kinds / Othmar

Stirling gave us this (--> ideal for a great n):

[cid:[login to unmask email]

Von: IDUG DB2-L [mailto:[login to unmask email] Im Auftrag von Phil Grainger
Gesendet: Dienstag, 26. Jänner 2010 13:00
An: [login to unmask email]
Betreff: [DB2-L] Mathematician wanted - number of potential indexes

Hi all

I’m sure there is someone out there who is better at maths than me and can answer this question

I’m trying to figure out the MAXIMUM number of candidate indexes on a table with “n” columns

Ignoring the ascending/descending choice, for tables with small numbers of columns it’s easy

Table with 1 column has 1 candidate index (col1)
Table with 2 columns has 4 candidate indexes (col1, col2, col1+col2, col2+col1)
Table with 3 columns has 15 candidate indexes (col1, col2, col3, col1+col2 etc. etc.)

Table with “n” columns has ?? candidate indexes

All I know for sure is that the number of candidate indexes goes up FAR faster than the number of columns in the table – but mathematically how are they related?


Phil Grainger
Grainger Database Solutions Ltd


[cid:[login to unmask email] ] < http://www.idug.org/ >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >