Mathematician wanted - number of potential indexes

Phil Grainger

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?



Thanks



Phil Grainger

Grainger Database Solutions Ltd


_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-content/index.html has THOUSANDS of free technical presentations!
DB2 LUW, DB2 z/OS, Performance, Installation, Tuning, Coding, BI, Warehouses, - among
many more categories of help waiting for you!
Whether you are an old hand or a DB2 newbie, we have presentations for every level.
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Roger Hecq

Re: Mathematician wanted - number of potential indexes
(in response to Phil Grainger)
I can't find the slate that I etched my math notes on, but I believe
that the answer is n factorial. N factorial will give you the number of
permutations on n objects.

Roger Hecq
MF IB USA DB Support
203-719-0492 / 19-337-0492



________________________________

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Phil Grainger
Sent: Tuesday, January 26, 2010 7:00 AM
To: [login to unmask email]
Subject: [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?



Thanks



Phil Grainger

Grainger Database Solutions Ltd


________________________________

IDUG - The Worldwide DB2 User Community! < 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 >


_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-content/index.html has THOUSANDS of free technical presentations!
DB2 LUW, DB2 z/OS, Performance, Installation, Tuning, Coding, BI, Warehouses, - among
many more categories of help waiting for you!
Whether you are an old hand or a DB2 newbie, we have presentations for every level.
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Phil Grainger

Re: Mathematician wanted - number of potential indexes
(in response to Roger Hecq)
That's what I first thought, but 3! Is 3 x 2 x 1 = 6, nowhere near the
actual 15 candidates for a 3 column table



So it's accelerating faster than factorial would account for



Phil Grainger

Grainger Database Solutions Ltd

_____

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Roger Hecq
Sent: 26 January 2010 13:07
To: [login to unmask email]
Subject: Re: [DB2-L] Mathematician wanted - number of potential indexes



I can't find the slate that I etched my math notes on, but I believe that
the answer is n factorial. N factorial will give you the number of
permutations on n objects.

Roger Hecq
MF IB USA DB Support
203-719-0492 / 19-337-0492





_____

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Phil Grainger
Sent: Tuesday, January 26, 2010 7:00 AM
To: [login to unmask email]
Subject: [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?



Thanks



Phil Grainger

Grainger Database Solutions Ltd



_____

< http://www.idug.org > IDUG - The Worldwide DB2 User Community!

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 >


_____

< http://www.idug.org > IDUG - The Worldwide DB2 User Community!

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 >


_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-content/index.html has THOUSANDS of free technical presentations!
DB2 LUW, DB2 z/OS, Performance, Installation, Tuning, Coding, BI, Warehouses, - among
many more categories of help waiting for you!
Whether you are an old hand or a DB2 newbie, we have presentations for every level.
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Michael Ebert

Re: Mathematician wanted - number of potential indexes
(in response to Phil Grainger)
Actually

f(0) = 0
f(n) = n*(f(n-1)+1) = n*f(n-1)+n

so it goes up a bit faster than n!.

Dr. Michael Ebert
DB2 & Oracle Database Administrator
aMaDEUS Data Processing
Erding / Munich, Germany




Roger Hecq <[login to unmask email]>
To
[login to unmask email]
cc

bcc

Subject
Re: [DB2-L] Mathematician wanted - number of potential indexes





Roger Hecq <[login to unmask email]>
Please respond to : IDUG DB2-L <[login to unmask email]>
Sent by: IDUG DB2-L <[login to unmask email]>
26-01-10 14:06


I can't find the slate that I etched my math notes on, but I believe that
the answer is n factorial. N factorial will give you the number of
permutations on n objects.
Roger Hecq
MF IB USA DB Support
203-719-0492 / 19-337-0492


From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Phil Grainger
Sent: Tuesday, January 26, 2010 7:00 AM
To: [login to unmask email]
Subject: [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?

Thanks

Phil Grainger
Grainger Database Solutions Ltd



The IDUG DB2-L Listserv is only part of your membership in IDUG. If you
are not already an IDUG member, please register here.



The IDUG DB2-L Listserv is only part of your membership in IDUG. If you
are not already an IDUG member, please register here. Visit our website at
http://www.ubs.com

This message contains confidential information and is intended only
for the individual named. If you are not the named addressee you
should not disseminate, distribute or copy this e-mail. Please
notify the sender immediately by e-mail if you have received this
e-mail by mistake and delete this e-mail from your system.

E-mails are not encrypted and cannot be guaranteed to be secure or
error-free as information could be intercepted, corrupted, lost,
destroyed, arrive late or incomplete, or contain viruses. The sender
therefore does not accept liability for any errors or omissions in the
contents of this message which arise as a result of e-mail transmission.
If verification is required please request a hard-copy version. This
message is provided for informational purposes and should not be
construed as a solicitation or offer to buy or sell any securities
or related financial instruments.


UBS reserves the right to retain all messages. Messages are protected
and accessed only in legally justified cases.

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 *
http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-content/index.html has THOUSANDS of free technical
presentations!
DB2 LUW, DB2 z/OS, Performance, Installation, Tuning, Coding, BI,
Warehouses, - among
many more categories of help waiting for you!
Whether you are an old hand or a DB2 newbie, we have presentations for
every level.
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is
the home of IDUG's DB2-L




IMPORTANT - CONFIDENTIALITY NOTICE - This e-mail is intended only for
the use of the individual or entity shown above as addressees. It may
contain information which is privileged, confidential or otherwise
protected from disclosure under applicable laws. If the reader of this
transmission is not the intended recipient, you are hereby notified that
any dissemination, printing, distribution, copying, disclosure or the
taking of any action in reliance on the contents of this information is
strictly prohibited. If you have received this transmission in error,
please immediately notify us by reply e-mail or using the address below
and delete the message and any attachments from your system.

Amadeus Data Processing GmbH
Geschäftsführer: Eberhard Haag
Sitz der Gesellschaft: Erding
HR München 48 199
Berghamer Strasse 6
85435 Erding
Germany

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-content/index.html has THOUSANDS of free technical presentations!
DB2 LUW, DB2 z/OS, Performance, Installation, Tuning, Coding, BI, Warehouses, - among
many more categories of help waiting for you!
Whether you are an old hand or a DB2 newbie, we have presentations for every level.
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Ted Cowles

Re: Mathematician wanted - number of potential indexes
(in response to Michael Ebert)
Looks like the pattern for your example could be:

Columns = 1, Indexes = 1
Columns = 2, Indexes = 2 + 2*1 = 4
Columns = 3, Indexes = 3 + 3*2 + 3*2*1 = 15
Columns = 4, Indexes = 4 + 4*3 + 4*3*2 + 4*3*2*1 = 64
etc..


Theodore W. Cowles
Northeast Utilities - ITMVS
107 Selden Street
Berlin, CT 06037-1616
phone: (860) 665-2738
fax: (860) 665-3318
email: [login to unmask email]



Phil Grainger
<[login to unmask email]
ABASESOLUTIONS.CO To
M> [login to unmask email]
Sent by: IDUG cc
DB2-L
<[login to unmask email] Subject
ORG> [DB2-L] Mathematician wanted -
number of potential indexes


01/26/2010 07:10
AM

Please respond to
IDUG DB2-L
<[login to unmask email]
2-L.ORG>






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?

Thanks

Phil Grainger
Grainger Database Solutions Ltd






(Embedded image moved to file: pic02392.jpg)IDUG - The Worldwide DB2 User
Community!


The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are
not already an IDUG member, please register here.


**********************************************************************
This e-mail, including any files or attachments transmitted with
it, is confidential and/or proprietary and is intended for a
specific purpose and for use only by the individual or entity to
whom it is addressed. Any disclosure, copying or distribution of
this e-mail or the taking of any action based on its contents,
other than for its intended purpose, is strictly prohibited. If
you have received this e-mail in error, please notify the sender
immediately and delete it from your system. Any views or opinions
expressed in this e-mail are not necessarily those of Northeast
Utilities, its subsidiaries and affiliates (NU). E-mail
transmission cannot be guaranteed to be error-free or secure or
free from viruses, and NU disclaims all liability for any resulting
damage, errors, or omissions.
**********************************************************************

Roger Hecq

Re: Mathematician wanted - number of potential indexes
(in response to Ted Cowles)
So, how many rows will that add to the catalog if you do Freqval Both for all of those indexes? * :-)

Roger Hecq
MF IB USA DB Support
203-719-0492 / 19-337-0492



________________________________

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Michael Ebert
Sent: Tuesday, January 26, 2010 8:55 AM
To: [login to unmask email]
Subject: Re: [DB2-L] Mathematician wanted - number of potential indexes



Actually

f(0) = 0
f(n) = n*(f(n-1)+1) = n*f(n-1)+n

so it goes up a bit faster than n!.

Dr. Michael Ebert
DB2 & Oracle Database Administrator
aMaDEUS Data Processing
Erding / Munich, Germany




Roger Hecq <[login to unmask email]>
To
[login to unmask email]
cc
bcc
Subject
Re: [DB2-L] Mathematician wanted - number of potential indexes



Roger Hecq <[login to unmask email]>

Please respond to : IDUG DB2-L <[login to unmask email]>

Sent by: IDUG DB2-L <[login to unmask email]>
26-01-10 14:06




I can't find the slate that I etched my math notes on, but I believe that the answer is n factorial. N factorial will give you the number of permutations on n objects.

Roger Hecq
MF IB USA DB Support
203-719-0492 / 19-337-0492




________________________________

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Phil Grainger
Sent: Tuesday, January 26, 2010 7:00 AM
To: [login to unmask email]
Subject: [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?

Thanks

Phil Grainger
Grainger Database Solutions Ltd



________________________________


IDUG - The Worldwide DB2 User Community! < 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 >



________________________________


IDUG - The Worldwide DB2 User Community! < 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 > Visit our website at http://www.ubs.com

This message contains confidential information and is intended only
for the individual named. If you are not the named addressee you
should not disseminate, distribute or copy this e-mail. Please
notify the sender immediately by e-mail if you have received this
e-mail by mistake and delete this e-mail from your system.

E-mails are not encrypted and cannot be guaranteed to be secure or
error-free as information could be intercepted, corrupted, lost,
destroyed, arrive late or incomplete, or contain viruses. The sender
therefore does not accept liability for any errors or omissions in the
contents of this message which arise as a result of e-mail transmission.
If verification is required please request a hard-copy version. This
message is provided for informational purposes and should not be
construed as a solicitation or offer to buy or sell any securities
or related financial instruments.


UBS reserves the right to retain all messages. Messages are protected
and accessed only in legally justified cases.

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-content/index.html has THOUSANDS of free technical presentations!
DB2 LUW, DB2 z/OS, Performance, Installation, Tuning, Coding, BI, Warehouses, - among
many more categories of help waiting for you!
Whether you are an old hand or a DB2 newbie, we have presentations for every level.
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L





IMPORTANT - CONFIDENTIALITY NOTICE - This e-mail is intended only for the use of the individual or entity shown above as addressees. It may contain information which is privileged, confidential or otherwise protected from disclosure under applicable laws. If the reader of this transmission is not the intended recipient, you are hereby notified that any dissemination, printing, distribution, copying, disclosure or the taking of any action in reliance on the contents of this information is strictly prohibited. If you have received this transmission in error, please immediately notify us by reply e-mail or using the address below and delete the message and any attachments from your system.

Amadeus Data Processing GmbH
Geschäftsführer: Eberhard Haag
Sitz der Gesellschaft: Erding
HR München 48 199
Berghamer Strasse 6
85435 Erding
Germany

________________________________


IDUG - The Worldwide DB2 User Community! < 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 >


_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-content/index.html has THOUSANDS of free technical presentations!
DB2 LUW, DB2 z/OS, Performance, Installation, Tuning, Coding, BI, Warehouses, - among
many more categories of help waiting for you!
Whether you are an old hand or a DB2 newbie, we have presentations for every level.
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Michael Ebert

Re: Mathematician wanted - number of potential indexes
(in response to Roger Hecq)
Actually (to add to my previous email), the number of possible indexes
approaches e*n!-1 (e=2.71828...) for large n, so it has the same growth
order as n!.
Checking some more, the final answer is floor(e*n!-1)...

Dr. Michael Ebert
DB2 & Oracle Database Administrator
aMaDEUS Data Processing
Erding / Munich, Germany




Roger Hecq <[login to unmask email]>
To
[login to unmask email]
cc

bcc

Subject
Re: [DB2-L] Mathematician wanted - number of potential indexes





Roger Hecq <[login to unmask email]>
Please respond to : IDUG DB2-L <[login to unmask email]>
Sent by: IDUG DB2-L <[login to unmask email]>
26-01-10 14:06


I can't find the slate that I etched my math notes on, but I believe that
the answer is n factorial. N factorial will give you the number of
permutations on n objects.
Roger Hecq
MF IB USA DB Support
203-719-0492 / 19-337-0492


From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Phil Grainger
Sent: Tuesday, January 26, 2010 7:00 AM
To: [login to unmask email]
Subject: [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?

Thanks

Phil Grainger
Grainger Database Solutions Ltd



The IDUG DB2-L Listserv is only part of your membership in IDUG. If you
are not already an IDUG member, please register here.



The IDUG DB2-L Listserv is only part of your membership in IDUG. If you
are not already an IDUG member, please register here. Visit our website at
http://www.ubs.com

This message contains confidential information and is intended only
for the individual named. If you are not the named addressee you
should not disseminate, distribute or copy this e-mail. Please
notify the sender immediately by e-mail if you have received this
e-mail by mistake and delete this e-mail from your system.

E-mails are not encrypted and cannot be guaranteed to be secure or
error-free as information could be intercepted, corrupted, lost,
destroyed, arrive late or incomplete, or contain viruses. The sender
therefore does not accept liability for any errors or omissions in the
contents of this message which arise as a result of e-mail transmission.
If verification is required please request a hard-copy version. This
message is provided for informational purposes and should not be
construed as a solicitation or offer to buy or sell any securities
or related financial instruments.


UBS reserves the right to retain all messages. Messages are protected
and accessed only in legally justified cases.

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 *
http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-content/index.html has THOUSANDS of free technical
presentations!
DB2 LUW, DB2 z/OS, Performance, Installation, Tuning, Coding, BI,
Warehouses, - among
many more categories of help waiting for you!
Whether you are an old hand or a DB2 newbie, we have presentations for
every level.
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is
the home of IDUG's DB2-L




IMPORTANT - CONFIDENTIALITY NOTICE - This e-mail is intended only for
the use of the individual or entity shown above as addressees. It may
contain information which is privileged, confidential or otherwise
protected from disclosure under applicable laws. If the reader of this
transmission is not the intended recipient, you are hereby notified that
any dissemination, printing, distribution, copying, disclosure or the
taking of any action in reliance on the contents of this information is
strictly prohibited. If you have received this transmission in error,
please immediately notify us by reply e-mail or using the address below
and delete the message and any attachments from your system.

Amadeus Data Processing GmbH
Geschäftsführer: Eberhard Haag
Sitz der Gesellschaft: Erding
HR München 48 199
Berghamer Strasse 6
85435 Erding
Germany

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-content/index.html has THOUSANDS of free technical presentations!
DB2 LUW, DB2 z/OS, Performance, Installation, Tuning, Coding, BI, Warehouses, - among
many more categories of help waiting for you!
Whether you are an old hand or a DB2 newbie, we have presentations for every level.
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Francis Leblanc

Re: Mathematician wanted - number of potential indexes
(in response to Michael Ebert)
I think what you're actually looking for is the number of permutations which is dependent on both the number of columns in the table and the number of columns in the index.

The basic formula for permutations is expressed as nPk = n!/(n-k)! where n is the number of columns in the table, and k is the number of columns in the key.

Example1: 3 columns in the table and 3 columns in the index would be 3P3=3!/(3-3)!=6/1=6
Example2: 3 columns in the table and 2 columns in the index would be 3P2=3!/(3-2)!=6/1=6
Example3: 3 columns in the table and 1 column in the index could be 3P1=3!/(3-1)!=6/2=3

It gets a bit more complicated because you're looking for all of the previous examples combined, i.e., 3P3+3P2+3P1.

For a 4 column table, you're looking for 4P4+4P3+4P2+4P1.
4P4=4!/(4-4)!=(4x3x2x1)/1=24/1=24
4P3=4!/(4-3)!=(4x3x2x1)/1=24/1=24
4P2=4!/(4-2)!=(4x3x2x1)/(2x1)=24/2=12
4P1=4!/(4-1)!=(4x3x2x1)/(3x2x1)=24/6=4

Since the maximum number of columns in an index key is 64, that would at least suggest a theoretical limit.

You might be able to get some additional information by googling "permutations".

Good luck.

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Phil Grainger
Sent: Tuesday, January 26, 2010 8:45 AM
To: [login to unmask email]
Subject: Re: [DB2-L] Mathematician wanted - number of potential indexes

That's what I first thought, but 3! Is 3 x 2 x 1 = 6, nowhere near the actual 15 candidates for a 3 column table

So it's accelerating faster than factorial would account for

Phil Grainger
Grainger Database Solutions Ltd
________________________________
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Roger Hecq
Sent: 26 January 2010 13:07
To: [login to unmask email]
Subject: Re: [DB2-L] Mathematician wanted - number of potential indexes

I can't find the slate that I etched my math notes on, but I believe that the answer is n factorial. N factorial will give you the number of permutations on n objects.

Roger Hecq
MF IB USA DB Support
203-719-0492 / 19-337-0492


________________________________
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Phil Grainger
Sent: Tuesday, January 26, 2010 7:00 AM
To: [login to unmask email]
Subject: [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?

Thanks

Phil Grainger
Grainger Database Solutions Ltd

________________________________

[ http://www.idug.org/images/M_images/idug%20org.jpg ] < 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 >

This E-Mail has been scanned for viruses.

________________________________

[ http://www.idug.org/images/M_images/idug%20org.jpg ] < 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 >

________________________________

[ http://www.idug.org/images/M_images/idug%20org.jpg ] < 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 >

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-content/index.html has THOUSANDS of free technical presentations!
DB2 LUW, DB2 z/OS, Performance, Installation, Tuning, Coding, BI, Warehouses, - among
many more categories of help waiting for you!
Whether you are an old hand or a DB2 newbie, we have presentations for every level.
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Philip Sevetson

Re: Mathematician wanted - number of potential indexes
(in response to Francis Leblanc)
The general answer is actually non-recursive, but I can't figure out how to collapse it. It uses the coefficients of a binomial distribution (what's the proper name for that!?!)
f(0) = 1*0! (where 0! Is understood to be 0)
f(1) = 1*0! + 1*1!
f(2) = 1*0! + 2*1! + 1*2!
f(3) = 1*0! + 3+1! + 3*2! + 1*3!
f(4) = 1*0! + 4+1! + 6*2! + 4*3! + 1*4!
f(5) = 1*0! + 5+1! + 10*2! + 10*3! + 5*4! + 1*5!
Etc.
I'm not sure how to collapse that; but it allows you to know f(n) without knowing f(n-1) first.

--Phil Sevetson, only half satisfied


________________________________
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Michael Ebert
Sent: Tuesday, January 26, 2010 8:55 AM
To: [login to unmask email]
Subject: Re: [DB2-L] Mathematician wanted - number of potential indexes


Actually

f(0) = 0
f(n) = n*(f(n-1)+1) = n*f(n-1)+n

so it goes up a bit faster than n!.

Dr. Michael Ebert
DB2 & Oracle Database Administrator
aMaDEUS Data Processing
Erding / Munich, Germany


Roger Hecq <[login to unmask email]>
To

[login to unmask email]

cc



bcc



Subject

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









Roger Hecq <[login to unmask email]>

Please respond to : IDUG DB2-L <[login to unmask email]>

Sent by: IDUG DB2-L <[login to unmask email]>
26-01-10 14:06




I can't find the slate that I etched my math notes on, but I believe that the answer is n factorial. N factorial will give you the number of permutations on n objects.

Roger Hecq
MF IB USA DB Support
203-719-0492 / 19-337-0492



________________________________
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Phil Grainger
Sent: Tuesday, January 26, 2010 7:00 AM
To: [login to unmask email]
Subject: [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?

Thanks

Phil Grainger
Grainger Database Solutions Ltd



________________________________

[cid:_2_05A842A405A83DF0004C720FC12576B7 ] < 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 >



________________________________

[cid:_2_0565081C057C8868004C720FC12576B7 ] < 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 > Visit our website at http://www.ubs.com

This message contains confidential information and is intended only
for the individual named. If you are not the named addressee you
should not disseminate, distribute or copy this e-mail. Please
notify the sender immediately by e-mail if you have received this
e-mail by mistake and delete this e-mail from your system.

E-mails are not encrypted and cannot be guaranteed to be secure or
error-free as information could be intercepted, corrupted, lost,
destroyed, arrive late or incomplete, or contain viruses. The sender
therefore does not accept liability for any errors or omissions in the
contents of this message which arise as a result of e-mail transmission.
If verification is required please request a hard-copy version. This
message is provided for informational purposes and should not be
construed as a solicitation or offer to buy or sell any securities
or related financial instruments.


UBS reserves the right to retain all messages. Messages are protected
and accessed only in legally justified cases.

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-content/index.html has THOUSANDS of free technical presentations!
DB2 LUW, DB2 z/OS, Performance, Installation, Tuning, Coding, BI, Warehouses, - among
many more categories of help waiting for you!
Whether you are an old hand or a DB2 newbie, we have presentations for every level.
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L




IMPORTANT - CONFIDENTIALITY NOTICE - This e-mail is intended only for the use of the individual or entity shown above as addressees. It may contain information which is privileged, confidential or otherwise protected from disclosure under applicable laws. If the reader of this transmission is not the intended recipient, you are hereby notified that any dissemination, printing, distribution, copying, disclosure or the taking of any action in reliance on the contents of this information is strictly prohibited. If you have received this transmission in error, please immediately notify us by reply e-mail or using the address below and delete the message and any attachments from your system.

Amadeus Data Processing GmbH
Geschäftsführer: Eberhard Haag
Sitz der Gesellschaft: Erding
HR München 48 199
Berghamer Strasse 6
85435 Erding
Germany

________________________________

[ http://www.idug.org/images/M_images/idug%20org.jpg ] < 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 >

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-content/index.html has THOUSANDS of free technical presentations!
DB2 LUW, DB2 z/OS, Performance, Installation, Tuning, Coding, BI, Warehouses, - among
many more categories of help waiting for you!
Whether you are an old hand or a DB2 newbie, we have presentations for every level.
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Aurora Dell'Anno

Re: Mathematician wanted - number of potential indexes
(in response to Philip Sevetson)
it's something to do with quantumumumumuuuuuum, I think.



Thanks.


Aurora


Aurora Emanuela Dell'Anno
CA
Sr. Engineering Services Architect
Tel: +44 (0)1753 577 733
Mobile: +44 (0)7768 235 339
[login to unmask email]
<mailto:[login to unmask email]>
http://www.ca.com/


P please don't print this e-mail unless you really need to!




________________________________

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Michael Ebert
Sent: 26 January 2010 14:31
To: [login to unmask email]
Subject: Re: [DB2-L] Mathematician wanted - number of potential indexes



Actually (to add to my previous email), the number of possible indexes approaches e*n!-1 (e=2.71828...) for large n, so it has the same growth order as n!.
Checking some more, the final answer is floor(e*n!-1)...

Dr. Michael Ebert
DB2 & Oracle Database Administrator
aMaDEUS Data Processing
Erding / Munich, Germany




Roger Hecq <[login to unmask email]>
To
[login to unmask email]
cc
bcc
Subject
Re: [DB2-L] Mathematician wanted - number of potential indexes



Roger Hecq <[login to unmask email]>

Please respond to : IDUG DB2-L <[login to unmask email]>

Sent by: IDUG DB2-L <[login to unmask email]>
26-01-10 14:06




I can't find the slate that I etched my math notes on, but I believe that the answer is n factorial. N factorial will give you the number of permutations on n objects.

Roger Hecq
MF IB USA DB Support
203-719-0492 / 19-337-0492




________________________________

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Phil Grainger
Sent: Tuesday, January 26, 2010 7:00 AM
To: [login to unmask email]
Subject: [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?

Thanks

Phil Grainger
Grainger Database Solutions Ltd



________________________________


IDUG - The Worldwide DB2 User Community! < 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 >



________________________________


IDUG - The Worldwide DB2 User Community! < 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 > Visit our website at http://www.ubs.com

This message contains confidential information and is intended only
for the individual named. If you are not the named addressee you
should not disseminate, distribute or copy this e-mail. Please
notify the sender immediately by e-mail if you have received this
e-mail by mistake and delete this e-mail from your system.

E-mails are not encrypted and cannot be guaranteed to be secure or
error-free as information could be intercepted, corrupted, lost,
destroyed, arrive late or incomplete, or contain viruses. The sender
therefore does not accept liability for any errors or omissions in the
contents of this message which arise as a result of e-mail transmission.
If verification is required please request a hard-copy version. This
message is provided for informational purposes and should not be
construed as a solicitation or offer to buy or sell any securities
or related financial instruments.


UBS reserves the right to retain all messages. Messages are protected
and accessed only in legally justified cases.

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-content/index.html has THOUSANDS of free technical presentations!
DB2 LUW, DB2 z/OS, Performance, Installation, Tuning, Coding, BI, Warehouses, - among
many more categories of help waiting for you!
Whether you are an old hand or a DB2 newbie, we have presentations for every level.
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L





IMPORTANT - CONFIDENTIALITY NOTICE - This e-mail is intended only for the use of the individual or entity shown above as addressees. It may contain information which is privileged, confidential or otherwise protected from disclosure under applicable laws. If the reader of this transmission is not the intended recipient, you are hereby notified that any dissemination, printing, distribution, copying, disclosure or the taking of any action in reliance on the contents of this information is strictly prohibited. If you have received this transmission in error, please immediately notify us by reply e-mail or using the address below and delete the message and any attachments from your system.

Amadeus Data Processing GmbH
Geschäftsführer: Eberhard Haag
Sitz der Gesellschaft: Erding
HR München 48 199
Berghamer Strasse 6
85435 Erding
Germany

________________________________


IDUG - The Worldwide DB2 User Community! < 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 >


_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-content/index.html has THOUSANDS of free technical presentations!
DB2 LUW, DB2 z/OS, Performance, Installation, Tuning, Coding, BI, Warehouses, - among
many more categories of help waiting for you!
Whether you are an old hand or a DB2 newbie, we have presentations for every level.
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Larry Jardine

Re: Mathematician wanted - number of potential indexes
(in response to Aurora Dell'Anno)
Don't forget, there is another factor: asc/desc for each column!

Larry Jardine
Production DBA
DBA Plan/Product Requests: https://wiki.aetna.com/confluence/display/dbaplan/Requests


________________________________
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Sevetson, Phil
Sent: Tuesday, January 26, 2010 10:42 AM
To: [login to unmask email]
Subject: Re: [DB2-L] Mathematician wanted - number of potential indexes

The general answer is actually non-recursive, but I can't figure out how to collapse it. It uses the coefficients of a binomial distribution (what's the proper name for that!?!)
f(0) = 1*0! (where 0! Is understood to be 0)
f(1) = 1*0! + 1*1!
f(2) = 1*0! + 2*1! + 1*2!
f(3) = 1*0! + 3+1! + 3*2! + 1*3!
f(4) = 1*0! + 4+1! + 6*2! + 4*3! + 1*4!
f(5) = 1*0! + 5+1! + 10*2! + 10*3! + 5*4! + 1*5!
Etc.
I'm not sure how to collapse that; but it allows you to know f(n) without knowing f(n-1) first.

--Phil Sevetson, only half satisfied


________________________________
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Michael Ebert
Sent: Tuesday, January 26, 2010 8:55 AM
To: [login to unmask email]
Subject: Re: [DB2-L] Mathematician wanted - number of potential indexes


Actually

f(0) = 0
f(n) = n*(f(n-1)+1) = n*f(n-1)+n

so it goes up a bit faster than n!.

Dr. Michael Ebert
DB2 & Oracle Database Administrator
aMaDEUS Data Processing
Erding / Munich, Germany


Roger Hecq <[login to unmask email]>
To

[login to unmask email]

cc



bcc



Subject

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









Roger Hecq <[login to unmask email]>

Please respond to : IDUG DB2-L <[login to unmask email]>

Sent by: IDUG DB2-L <[login to unmask email]>
26-01-10 14:06




I can't find the slate that I etched my math notes on, but I believe that the answer is n factorial. N factorial will give you the number of permutations on n objects.

Roger Hecq
MF IB USA DB Support
203-719-0492 / 19-337-0492



________________________________
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Phil Grainger
Sent: Tuesday, January 26, 2010 7:00 AM
To: [login to unmask email]
Subject: [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?

Thanks

Phil Grainger
Grainger Database Solutions Ltd



________________________________

[cid:_2_05A842A405A83DF0004C720FC12576B7 ] < 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 >



________________________________

[cid:_2_0565081C057C8868004C720FC12576B7 ] < 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 > Visit our website at http://www.ubs.com

This message contains confidential information and is intended only
for the individual named. If you are not the named addressee you
should not disseminate, distribute or copy this e-mail. Please
notify the sender immediately by e-mail if you have received this
e-mail by mistake and delete this e-mail from your system.

E-mails are not encrypted and cannot be guaranteed to be secure or
error-free as information could be intercepted, corrupted, lost,
destroyed, arrive late or incomplete, or contain viruses. The sender
therefore does not accept liability for any errors or omissions in the
contents of this message which arise as a result of e-mail transmission.
If verification is required please request a hard-copy version. This
message is provided for informational purposes and should not be
construed as a solicitation or offer to buy or sell any securities
or related financial instruments.


UBS reserves the right to retain all messages. Messages are protected
and accessed only in legally justified cases.

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-content/index.html has THOUSANDS of free technical presentations!
DB2 LUW, DB2 z/OS, Performance, Installation, Tuning, Coding, BI, Warehouses, - among
many more categories of help waiting for you!
Whether you are an old hand or a DB2 newbie, we have presentations for every level.
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L




IMPORTANT - CONFIDENTIALITY NOTICE - This e-mail is intended only for the use of the individual or entity shown above as addressees. It may contain information which is privileged, confidential or otherwise protected from disclosure under applicable laws. If the reader of this transmission is not the intended recipient, you are hereby notified that any dissemination, printing, distribution, copying, disclosure or the taking of any action in reliance on the contents of this information is strictly prohibited. If you have received this transmission in error, please immediately notify us by reply e-mail or using the address below and delete the message and any attachments from your system.

Amadeus Data Processing GmbH
Geschäftsführer: Eberhard Haag
Sitz der Gesellschaft: Erding
HR München 48 199
Berghamer Strasse 6
85435 Erding
Germany

________________________________

[ http://www.idug.org/images/M_images/idug%20org.jpg ] < 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 >

________________________________

[ http://www.idug.org/images/M_images/idug%20org.jpg ] < 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 >


This e-mail may contain confidential or privileged information. If
you think you have received this e-mail in error, please advise the
sender by reply e-mail and then delete this e-mail immediately.
Thank you. Aetna

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/rug/index.html - with almost 150 IDUG Regional User Groups,
there is probably one near you!
Regional User Groups are your local connection to the Worldwide DB2 User Community
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Ted MacNEIL

Re: Mathematician wanted - number of potential indexes
(in response to Larry Jardine)
>Don't forget, there is another factor:  asc/desc for each column!
 

Yes, but the original poster (Phil) asked us to ignore that one.
-
Too busy driving to stop for gas!

Roger Hecq

Re: Mathematician wanted - number of potential indexes
(in response to Ted MacNEIL)
Great. Thanks. As if we weren't already sufficiently confused. :-)

Roger Hecq
MF IB USA DB Support
203-719-0492 / 19-337-0492



________________________________

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Jardine, Lawrence J
Sent: Thursday, January 28, 2010 12:39 PM
To: [login to unmask email]
Subject: Re: [DB2-L] Mathematician wanted - number of potential indexes


Don't forget, there is another factor: asc/desc for each column!

Larry Jardine
Production DBA
DBA Plan/Product Requests: https://wiki.aetna.com/confluence/display/dbaplan/Requests < https://wiki.aetna.com/confluence/display/dbaplan/Requests >


________________________________

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Sevetson, Phil
Sent: Tuesday, January 26, 2010 10:42 AM
To: [login to unmask email]
Subject: Re: [DB2-L] Mathematician wanted - number of potential indexes



The general answer is actually non-recursive, but I can't figure out how to collapse it. It uses the coefficients of a binomial distribution (what's the proper name for that!?!)

f(0) = 1*0! (where 0! Is understood to be 0)

f(1) = 1*0! + 1*1!

f(2) = 1*0! + 2*1! + 1*2!

f(3) = 1*0! + 3+1! + 3*2! + 1*3!

f(4) = 1*0! + 4+1! + 6*2! + 4*3! + 1*4!

f(5) = 1*0! + 5+1! + 10*2! + 10*3! + 5*4! + 1*5!

Etc.

I'm not sure how to collapse that; but it allows you to know f(n) without knowing f(n-1) first.



--Phil Sevetson, only half satisfied





________________________________

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Michael Ebert
Sent: Tuesday, January 26, 2010 8:55 AM
To: [login to unmask email]
Subject: Re: [DB2-L] Mathematician wanted - number of potential indexes




Actually

f(0) = 0
f(n) = n*(f(n-1)+1) = n*f(n-1)+n

so it goes up a bit faster than n!.

Dr. Michael Ebert
DB2 & Oracle Database Administrator
aMaDEUS Data Processing
Erding / Munich, Germany




Roger Hecq <[login to unmask email]>

To

[login to unmask email]

cc



bcc



Subject

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









Roger Hecq <[login to unmask email]>

Please respond to : IDUG DB2-L <[login to unmask email]>

Sent by: IDUG DB2-L <[login to unmask email]>
26-01-10 14:06




I can't find the slate that I etched my math notes on, but I believe that the answer is n factorial. N factorial will give you the number of permutations on n objects.

Roger Hecq
MF IB USA DB Support
203-719-0492 / 19-337-0492



________________________________

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Phil Grainger
Sent: Tuesday, January 26, 2010 7:00 AM
To: [login to unmask email]
Subject: [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?

Thanks

Phil Grainger
Grainger Database Solutions Ltd



________________________________

IDUG - The Worldwide DB2 User Community! < 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 >



________________________________

IDUG - The Worldwide DB2 User Community! < 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 > Visit our website at http://www.ubs.com

This message contains confidential information and is intended only
for the individual named. If you are not the named addressee you
should not disseminate, distribute or copy this e-mail. Please
notify the sender immediately by e-mail if you have received this
e-mail by mistake and delete this e-mail from your system.

E-mails are not encrypted and cannot be guaranteed to be secure or
error-free as information could be intercepted, corrupted, lost,
destroyed, arrive late or incomplete, or contain viruses. The sender
therefore does not accept liability for any errors or omissions in the
contents of this message which arise as a result of e-mail transmission.
If verification is required please request a hard-copy version. This
message is provided for informational purposes and should not be
construed as a solicitation or offer to buy or sell any securities
or related financial instruments.


UBS reserves the right to retain all messages. Messages are protected
and accessed only in legally justified cases.

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-content/index.html has THOUSANDS of free technical presentations!
DB2 LUW, DB2 z/OS, Performance, Installation, Tuning, Coding, BI, Warehouses, - among
many more categories of help waiting for you!
Whether you are an old hand or a DB2 newbie, we have presentations for every level.
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L





IMPORTANT - CONFIDENTIALITY NOTICE - This e-mail is intended only for the use of the individual or entity shown above as addressees. It may contain information which is privileged, confidential or otherwise protected from disclosure under applicable laws. If the reader of this transmission is not the intended recipient, you are hereby notified that any dissemination, printing, distribution, copying, disclosure or the taking of any action in reliance on the contents of this information is strictly prohibited. If you have received this transmission in error, please immediately notify us by reply e-mail or using the address below and delete the message and any attachments from your system.

Amadeus Data Processing GmbH
Geschäftsführer: Eberhard Haag
Sitz der Gesellschaft: Erding
HR München 48 199
Berghamer Strasse 6
85435 Erding
Germany

________________________________

IDUG - The Worldwide DB2 User Community! < 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 >


________________________________

IDUG - The Worldwide DB2 User Community! < 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 >

This e-mail may contain confidential or privileged information. If you think you have received this e-mail in error, please advise the sender by reply e-mail and then delete this e-mail immediately. Thank you. Aetna

________________________________


IDUG - The Worldwide DB2 User Community! < 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 >


_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/rug/index.html - with almost 150 IDUG Regional User Groups,
there is probably one near you!
Regional User Groups are your local connection to the Worldwide DB2 User Community
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Mike Bell

Re: Mathematician wanted - number of potential indexes
(in response to Roger Hecq)
but remember that with v9? You can now read indexes backward so it is not
required to have both ascending and descending index. I am not certain that
it can match each level though.

If you have col1 asc, col2 desc, col3 asc can you do
Order by col1 asc, col2 asc ,col desc?

Is it Friday yet, and what happended to DB2-L? way to quiet for a bunch of
bored DBA's.

Mike

-----Original Message-----
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Jardine, Lawrence
J
Sent: Thursday, January 28, 2010 11:39 AM
To: [login to unmask email]
Subject: Re: [DB2-L] Mathematician wanted - number of potential indexes

Don't forget, there is another factor: asc/desc for each column!

Larry Jardine
Production DBA
DBA Plan/Product Requests:
https://wiki.aetna.com/confluence/display/dbaplan/Requests
< https://wiki.aetna.com/confluence/display/dbaplan/Requests >


________________________________

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Sevetson, Phil
Sent: Tuesday, January 26, 2010 10:42 AM
To: [login to unmask email]
Subject: Re: [DB2-L] Mathematician wanted - number of potential indexes



The general answer is actually non-recursive, but I can’t figure out how to
collapse it. It uses the coefficients of a binomial distribution (what’s
the proper name for that!?!)

f(0) = 1*0! (where 0! Is understood to be 0)

f(1) = 1*0! + 1*1!

f(2) = 1*0! + 2*1! + 1*2!

f(3) = 1*0! + 3+1! + 3*2! + 1*3!

f(4) = 1*0! + 4+1! + 6*2! + 4*3! + 1*4!

f(5) = 1*0! + 5+1! + 10*2! + 10*3! + 5*4! + 1*5!

Etc.

I’m not sure how to collapse that; but it allows you to know f(n) without
knowing f(n-1) first.



--Phil Sevetson, only half satisfied





________________________________

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Michael Ebert
Sent: Tuesday, January 26, 2010 8:55 AM
To: [login to unmask email]
Subject: Re: [DB2-L] Mathematician wanted - number of potential indexes




Actually

f(0) = 0
f(n) = n*(f(n-1)+1) = n*f(n-1)+n

so it goes up a bit faster than n!.

Dr. Michael Ebert
DB2 & Oracle Database Administrator
aMaDEUS Data Processing
Erding / Munich, Germany




Roger Hecq <[login to unmask email]>

To

[login to unmask email]

cc



bcc



Subject

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









Roger Hecq <[login to unmask email]>

Please respond to : IDUG DB2-L <[login to unmask email]>

Sent by: IDUG DB2-L <[login to unmask email]>
26-01-10 14:06




I can't find the slate that I etched my math notes on, but I believe that
the answer is n factorial. N factorial will give you the number of
permutations on n objects.

Roger Hecq
MF IB USA DB Support
203-719-0492 / 19-337-0492



________________________________

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Phil Grainger
Sent: Tuesday, January 26, 2010 7:00 AM
To: [login to unmask email]
Subject: [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?

Thanks

Phil Grainger
Grainger Database Solutions Ltd



________________________________

IDUG - The Worldwide DB2 User Community! < 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 >



________________________________

IDUG - The Worldwide DB2 User Community! < 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 > Visit our website at http://www.ubs.com

This message contains confidential information and is intended only for the
individual named. If you are not the named addressee you should not
disseminate, distribute or copy this e-mail. Please notify the sender
immediately by e-mail if you have received this e-mail by mistake and delete
this e-mail from your system.

E-mails are not encrypted and cannot be guaranteed to be secure or
error-free as information could be intercepted, corrupted, lost, destroyed,
arrive late or incomplete, or contain viruses. The sender therefore does
not accept liability for any errors or omissions in the contents of this
message which arise as a result of e-mail transmission.
If verification is required please request a hard-copy version. This
message is provided for informational purposes and should not be construed
as a solicitation or offer to buy or sell any securities or related
financial instruments.


UBS reserves the right to retain all messages. Messages are protected and
accessed only in legally justified cases.

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 *
http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-content/index.html has THOUSANDS of free technical
presentations!
DB2 LUW, DB2 z/OS, Performance, Installation, Tuning, Coding, BI,
Warehouses, - among many more categories of help waiting for you!
Whether you are an old hand or a DB2 newbie, we have presentations for every
level.
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is
the home of IDUG's DB2-L





IMPORTANT - CONFIDENTIALITY NOTICE - This e-mail is intended only for
the use of the individual or entity shown above as addressees. It may
contain information which is privileged, confidential or otherwise protected
from disclosure under applicable laws. If the reader of this transmission
is not the intended recipient, you are hereby notified that any
dissemination, printing, distribution, copying, disclosure or the taking of
any action in reliance on the contents of this information is strictly
prohibited. If you have received this transmission in error, please
immediately notify us by reply e-mail or using the address below and delete
the message and any attachments from your system.

Amadeus Data Processing GmbH
Geschäftsführer: Eberhard Haag
Sitz der Gesellschaft: Erding
HR München 48 199
Berghamer Strasse 6
85435 Erding
Germany

________________________________

IDUG - The Worldwide DB2 User Community! < 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 >


________________________________

IDUG - The Worldwide DB2 User Community! < 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 >

This e-mail may contain confidential or privileged information. If you think
you have received this e-mail in error, please advise the sender by reply
e-mail and then delete this e-mail immediately. Thank you. Aetna

________________________________


IDUG - The Worldwide DB2 User Community! < 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 >

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/rug/index.html - with almost 150 IDUG Regional User Groups,
there is probably one near you!
Regional User Groups are your local connection to the Worldwide DB2 User Community
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Martin Hubel

Re: Mathematician wanted - number of potential indexes
(in response to Mike Bell)
I think we are after the maximum number of indexes possible, so we don't want any index doing double duty.

My worst real life cases to date:

5 indexes on the same 3 columns.

14 indexes on a 9 column table

89 indexes on a Siebel table (everyone says over 100, but I didn't see it.)

I'm sure these will be topped.

--Martin

>> but remember that with v9? You can now read indexes backward so it is not
>> required to have both ascending and descending index. I am not certain
>> that
>> it can match each level though.

>> If you have col1 asc, col2 desc, col3 asc can you do
>> Order by col1 asc, col2 asc ,col desc?

>> Is it Friday yet, and what happended to DB2-L? way to quiet for a bunch of
>> bored DBA's.

>> Mike

>> -----Original Message-----
>> From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Jardine,
>> Lawrence
>> J
>> Sent: Thursday, January 28, 2010 11:39 AM
>> To: [login to unmask email]
>> Subject: Re: [DB2-L] Mathematician wanted - number of potential indexes

>> Don't forget, there is another factor: asc/desc for each column!
>>
>> Larry Jardine
>> Production DBA
>> DBA Plan/Product Requests:
>> https://wiki.aetna.com/confluence/display/dbaplan/Requests
>> <https://wiki.aetna.com/confluence/display/dbaplan/Requests>
>>

>> ________________________________

>> From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Sevetson, Phil
>> Sent: Tuesday, January 26, 2010 10:42 AM
>> To: [login to unmask email]
>> Subject: Re: [DB2-L] Mathematician wanted - number of potential indexes



>> The general answer is actually non-recursive, but I can’t figure out how
>> to
>> collapse it. It uses the coefficients of a binomial distribution (what’s
>> the proper name for that!?!)

>> f(0) = 1*0! (where 0! Is understood to be 0)

>> f(1) = 1*0! + 1*1!

>> f(2) = 1*0! + 2*1! + 1*2!

>> f(3) = 1*0! + 3+1! + 3*2! + 1*3!

>> f(4) = 1*0! + 4+1! + 6*2! + 4*3! + 1*4!

>> f(5) = 1*0! + 5+1! + 10*2! + 10*3! + 5*4! + 1*5!

>> Etc.

>> I’m not sure how to collapse that; but it allows you to know f(n) without
>> knowing f(n-1) first.

>>

>> --Phil Sevetson, only half satisfied

>>

>>

>> ________________________________

>> From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Michael Ebert
>> Sent: Tuesday, January 26, 2010 8:55 AM
>> To: [login to unmask email]
>> Subject: Re: [DB2-L] Mathematician wanted - number of potential indexes

>>


>> Actually

>> f(0) = 0
>> f(n) = n*(f(n-1)+1) = n*f(n-1)+n

>> so it goes up a bit faster than n!.

>> Dr. Michael Ebert
>> DB2 & Oracle Database Administrator
>> aMaDEUS Data Processing
>> Erding / Munich, Germany




>> Roger Hecq <[login to unmask email]>

>> To

>> [login to unmask email]

>> cc

>>

>> bcc

>>

>> Subject

>> Re: [DB2-L] Mathematician wanted - number of potential indexes

>>

>>

>>

>>

>> Roger Hecq <[login to unmask email]>

>> Please respond to : IDUG DB2-L <[login to unmask email]>

>> Sent by: IDUG DB2-L <[login to unmask email]>
>> 26-01-10 14:06




>> I can't find the slate that I etched my math notes on, but I believe that
>> the answer is n factorial. N factorial will give you the number of
>> permutations on n objects.

>> Roger Hecq
>> MF IB USA DB Support
>> 203-719-0492 / 19-337-0492

>>

>> ________________________________

>> From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Phil Grainger
>> Sent: Tuesday, January 26, 2010 7:00 AM
>> To: [login to unmask email]
>> Subject: [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?
>>
>> Thanks
>>
>> Phil Grainger
>> Grainger Database Solutions Ltd

>>

>> ________________________________

>> IDUG - The Worldwide DB2 User Community! <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>

>>

>> ________________________________

>> IDUG - The Worldwide DB2 User Community! <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> Visit our website at http://www.ubs.com

>> This message contains confidential information and is intended only for
>> the
>> individual named. If you are not the named addressee you should not
>> disseminate, distribute or copy this e-mail. Please notify the sender
>> immediately by e-mail if you have received this e-mail by mistake and
>> delete
>> this e-mail from your system.
>>
>> E-mails are not encrypted and cannot be guaranteed to be secure or
>> error-free as information could be intercepted, corrupted, lost,
>> destroyed,
>> arrive late or incomplete, or contain viruses. The sender therefore does
>> not accept liability for any errors or omissions in the contents of this
>> message which arise as a result of e-mail transmission.
>> If verification is required please request a hard-copy version. This
>> message is provided for informational purposes and should not be construed
>> as a solicitation or offer to buy or sell any securities or related
>> financial instruments.


>> UBS reserves the right to retain all messages. Messages are protected and
>> accessed only in legally justified cases.

>> _____________________________________________________________________

>> * IDUG North America * Tampa, Florida, * May 10-14 2010 *
>> http://IDUG.ORG/NA *
>> _____________________________________________________________________

>> http://www.idug.org/db2-content/index.html has THOUSANDS of free technical
>> presentations!
>> DB2 LUW, DB2 z/OS, Performance, Installation, Tuning, Coding, BI,
>> Warehouses, - among many more categories of help waiting for you!
>> Whether you are an old hand or a DB2 newbie, we have presentations for
>> every
>> level.
>> _____________________________________________________________________

>> If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is
>> the home of IDUG's DB2-L





>> IMPORTANT - CONFIDENTIALITY NOTICE - This e-mail is intended only for
>> the use of the individual or entity shown above as addressees. It may
>> contain information which is privileged, confidential or otherwise
>> protected
>> from disclosure under applicable laws. If the reader of this transmission
>> is not the intended recipient, you are hereby notified that any
>> dissemination, printing, distribution, copying, disclosure or the taking
>> of
>> any action in reliance on the contents of this information is strictly
>> prohibited. If you have received this transmission in error, please
>> immediately notify us by reply e-mail or using the address below and
>> delete
>> the message and any attachments from your system.

>> Amadeus Data Processing GmbH
>> Geschäftsführer: Eberhard Haag
>> Sitz der Gesellschaft: Erding
>> HR München 48 199
>> Berghamer Strasse 6
>> 85435 Erding
>> Germany

>> ________________________________

>> IDUG - The Worldwide DB2 User Community! <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>


>> ________________________________

>> IDUG - The Worldwide DB2 User Community! <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>

>> This e-mail may contain confidential or privileged information. If you
>> think
>> you have received this e-mail in error, please advise the sender by reply
>> e-mail and then delete this e-mail immediately. Thank you. Aetna

>> ________________________________


>> IDUG - The Worldwide DB2 User Community! <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>

>> _____________________________________________________________________

>> * IDUG North America * Tampa, Florida, * May 10-14 2010 *
>> http://IDUG.ORG/NA *
>> _____________________________________________________________________

>> http://www.idug.org/rug/index.html - with almost 150 IDUG Regional User
>> Groups,
>> there is probably one near you!
>> Regional User Groups are your local connection to the Worldwide DB2 User
>> Community
>> _____________________________________________________________________

>> If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is
>> the home of IDUG's DB2-L






====================
Martin Hubel
MHC Inc.
[login to unmask email]
+1 905-764-7498
+1 416-670-7498 Mobile
Skype: db2hubel
Yahoo IM: db2hubel

Charter Member - IBM Gold Consultant Program
IBM Information Champion

Fight the Right Fires - Let us show you how
====================


The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here.

Peter Suhner

Re: Mathematician wanted - number of potential indexes
(in response to Martin Hubel)

Mike,
actually you can't. An index with col1 asc, col2 desc, col3 asc will work in the inverse mode for all columns only.
Which gives you col1 desc, col2 asc, col3 desc in this example.

Even though it's almost Friday again, the digital world is still not completely unlimited...

Peter

_______________________
peter suhner
[login to unmask email]


> Date: Thu, 28 Jan 2010 13:04:53 -0600
> From: [login to unmask email]
> Subject: Re: [DB2-L] Mathematician wanted - number of potential indexes
> To: [login to unmask email]
>
> but remember that with v9? You can now read indexes backward so it is not
> required to have both ascending and descending index. I am not certain that
> it can match each level though.
>
> If you have col1 asc, col2 desc, col3 asc can you do
> Order by col1 asc, col2 asc ,col desc?
>
> Is it Friday yet, and what happended to DB2-L? way to quiet for a bunch of
> bored DBA's.
>
> Mike
>
> -----Original Message-----
> From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Jardine, Lawrence
> J
> Sent: Thursday, January 28, 2010 11:39 AM
> To: [login to unmask email]
> Subject: Re: [DB2-L] Mathematician wanted - number of potential indexes
>
> Don't forget, there is another factor: asc/desc for each column!
>
> Larry Jardine
> Production DBA
> DBA Plan/Product Requests:
> https://wiki.aetna.com/confluence/display/dbaplan/Requests
> < https://wiki.aetna.com/confluence/display/dbaplan/Requests >
>
>
> ________________________________
>
> From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Sevetson, Phil
> Sent: Tuesday, January 26, 2010 10:42 AM
> To: [login to unmask email]
> Subject: Re: [DB2-L] Mathematician wanted - number of potential indexes
>
>
>
> The general answer is actually non-recursive, but I can’t figure out how to
> collapse it. It uses the coefficients of a binomial distribution (what’s
> the proper name for that!?!)
>
> f(0) = 1*0! (where 0! Is understood to be 0)
>
> f(1) = 1*0! + 1*1!
>
> f(2) = 1*0! + 2*1! + 1*2!
>
> f(3) = 1*0! + 3+1! + 3*2! + 1*3!
>
> f(4) = 1*0! + 4+1! + 6*2! + 4*3! + 1*4!
>
> f(5) = 1*0! + 5+1! + 10*2! + 10*3! + 5*4! + 1*5!
>
> Etc.
>
> I’m not sure how to collapse that; but it allows you to know f(n) without
> knowing f(n-1) first.
>
>
>
> --Phil Sevetson, only half satisfied
>
>
>
>
>
> ________________________________
>
> From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Michael Ebert
> Sent: Tuesday, January 26, 2010 8:55 AM
> To: [login to unmask email]
> Subject: Re: [DB2-L] Mathematician wanted - number of potential indexes
>
>
>
>
> Actually
>
> f(0) = 0
> f(n) = n*(f(n-1)+1) = n*f(n-1)+n
>
> so it goes up a bit faster than n!.
>
> Dr. Michael Ebert
> DB2 & Oracle Database Administrator
> aMaDEUS Data Processing
> Erding / Munich, Germany
>
>
>
>
> Roger Hecq <[login to unmask email]>
>
> To
>
> [login to unmask email]
>
> cc
>
>
>
> bcc
>
>
>
> Subject
>
> Re: [DB2-L] Mathematician wanted - number of potential indexes
>
>
>
>
>
>
>
>
>
> Roger Hecq <[login to unmask email]>
>
> Please respond to : IDUG DB2-L <[login to unmask email]>
>
> Sent by: IDUG DB2-L <[login to unmask email]>
> 26-01-10 14:06
>
>
>
>
> I can't find the slate that I etched my math notes on, but I believe that
> the answer is n factorial. N factorial will give you the number of
> permutations on n objects.
>
> Roger Hecq
> MF IB USA DB Support
> 203-719-0492 / 19-337-0492
>
>
>
> ________________________________
>
> From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Phil Grainger
> Sent: Tuesday, January 26, 2010 7:00 AM
> To: [login to unmask email]
> Subject: [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?
>
> Thanks
>
> Phil Grainger
> Grainger Database Solutions Ltd
>
>
>
> ________________________________
>
> IDUG - The Worldwide DB2 User Community! < 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 >
>
>
>
> ________________________________
>
> IDUG - The Worldwide DB2 User Community! < 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 > Visit our website at http://www.ubs.com
>
> This message contains confidential information and is intended only for the
> individual named. If you are not the named addressee you should not
> disseminate, distribute or copy this e-mail. Please notify the sender
> immediately by e-mail if you have received this e-mail by mistake and delete
> this e-mail from your system.
>
> E-mails are not encrypted and cannot be guaranteed to be secure or
> error-free as information could be intercepted, corrupted, lost, destroyed,
> arrive late or incomplete, or contain viruses. The sender therefore does
> not accept liability for any errors or omissions in the contents of this
> message which arise as a result of e-mail transmission.
> If verification is required please request a hard-copy version. This
> message is provided for informational purposes and should not be construed
> as a solicitation or offer to buy or sell any securities or related
> financial instruments.
>
>
> UBS reserves the right to retain all messages. Messages are protected and
> accessed only in legally justified cases.
>
> _____________________________________________________________________
>
> * IDUG North America * Tampa, Florida, * May 10-14 2010 *
> http://IDUG.ORG/NA *
> _____________________________________________________________________
>
> http://www.idug.org/db2-content/index.html has THOUSANDS of free technical
> presentations!
> DB2 LUW, DB2 z/OS, Performance, Installation, Tuning, Coding, BI,
> Warehouses, - among many more categories of help waiting for you!
> Whether you are an old hand or a DB2 newbie, we have presentations for every
> level.
> _____________________________________________________________________
>
> If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is
> the home of IDUG's DB2-L
>
>
>
>
>
> IMPORTANT - CONFIDENTIALITY NOTICE - This e-mail is intended only for
> the use of the individual or entity shown above as addressees. It may
> contain information which is privileged, confidential or otherwise protected
> from disclosure under applicable laws. If the reader of this transmission
> is not the intended recipient, you are hereby notified that any
> dissemination, printing, distribution, copying, disclosure or the taking of
> any action in reliance on the contents of this information is strictly
> prohibited. If you have received this transmission in error, please
> immediately notify us by reply e-mail or using the address below and delete
> the message and any attachments from your system.
>
> Amadeus Data Processing GmbH
> Geschäftsführer: Eberhard Haag
> Sitz der Gesellschaft: Erding
> HR München 48 199
> Berghamer Strasse 6
> 85435 Erding
> Germany
>
> ________________________________
>
> IDUG - The Worldwide DB2 User Community! < 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 >
>
>
> ________________________________
>
> IDUG - The Worldwide DB2 User Community! < 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 >
>
> This e-mail may contain confidential or privileged information. If you think
> you have received this e-mail in error, please advise the sender by reply
> e-mail and then delete this e-mail immediately. Thank you. Aetna
>
> ________________________________
>
>
> IDUG - The Worldwide DB2 User Community! < 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 >
>
> _____________________________________________________________________
>
> * IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
> _____________________________________________________________________
>
> http://www.idug.org/rug/index.html - with almost 150 IDUG Regional User Groups,
> there is probably one near you!
> Regional User Groups are your local connection to the Worldwide DB2 User Community
> _____________________________________________________________________
>
> If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

_________________________________________________________________
Hotmail: Trusted email with Microsoft’s powerful SPAM protection.
https://signup.live.com/signup.aspx?id=60969
_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/rug/index.html - with almost 150 IDUG Regional User Groups,
there is probably one near you!
Regional User Groups are your local connection to the Worldwide DB2 User Community
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Joe Geller

Re: Mathematician wanted - number of potential indexes
(in response to Peter Suhner)
It's fairly easy to calculate. If you have n columns, then if you have m
columns in the index, the number of permutations is n!/(n-m)!. Therefore
the total number of index possibilities is the sum of m=1 to n of n!/(n-m)!

So, for n=4 you would have

4!/3! + 4!/2! + 4!/1! + 4!/0!
24/6 + 24/2 + 24/1 + 24/1
4+12+24+24 = 64

For n=5 you would have

5!/4! + 5!/3! + 5!/2! + 5!/1! + 5!/0!
120/24 + 120/6 + 120/2 + 120/1 + 120/1
5+20+60+120+120 = 325

I wasn't sure if there is a general formula for the entire answer, but
Michael seems to have it.
The above is easily programmed to give you an answer for any number of
columns (or for those of you DBAs who don't program, you could do it with
recursive SQL :) ).

On Tue, 26 Jan 2010 14:30:49 +0000, Michael Ebert <[login to unmask email]> wrote:

>Actually (to add to my previous email), the number of possible indexes
>approaches e*n!-1 (e=2.71828...) for large n, so it has the same growth
>order as n!.
>Checking some more, the final answer is floor(e*n!-1)...
>
>Dr. Michael Ebert
>DB2 & Oracle Database Administrator
>aMaDEUS Data Processing
>Erding / Munich, Germany
>
>
>
>
>Roger Hecq <[login to unmask email]>
>To
>[login to unmask email]
>cc
>
>bcc
>
>Subject
>Re: [DB2-L] Mathematician wanted - number of potential indexes
>
>
>
>
>
>Roger Hecq <[login to unmask email]>
>Please respond to : IDUG DB2-L <[login to unmask email]>
>Sent by: IDUG DB2-L <[login to unmask email]>
>26-01-10 14:06
>
>
>I can't find the slate that I etched my math notes on, but I believe that
>the answer is n factorial. N factorial will give you the number of
>permutations on n objects.
>Roger Hecq
>MF IB USA DB Support
>203-719-0492 / 19-337-0492
>
>
>From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Phil Grainger
>Sent: Tuesday, January 26, 2010 7:00 AM
>To: [login to unmask email]
>Subject: [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?
>
>Thanks
>
>Phil Grainger
>Grainger Database Solutions Ltd
>
>
>
>The IDUG DB2-L Listserv is only part of your membership in IDUG. If you
>are not already an IDUG member, please register here.
>
>
>
>The IDUG DB2-L Listserv is only part of your membership in IDUG. If you
>are not already an IDUG member, please register here. Visit our website at
>http://www.ubs.com
>
>This message contains confidential information and is intended only
>for the individual named. If you are not the named addressee you
>should not disseminate, distribute or copy this e-mail. Please
>notify the sender immediately by e-mail if you have received this
>e-mail by mistake and delete this e-mail from your system.
>
>E-mails are not encrypted and cannot be guaranteed to be secure or
>error-free as information could be intercepted, corrupted, lost,
>destroyed, arrive late or incomplete, or contain viruses. The sender
>therefore does not accept liability for any errors or omissions in the
>contents of this message which arise as a result of e-mail transmission.
>If verification is required please request a hard-copy version. This
>message is provided for informational purposes and should not be
>construed as a solicitation or offer to buy or sell any securities
>or related financial instruments.
>
>
>UBS reserves the right to retain all messages. Messages are protected
>and accessed only in legally justified cases.
>
>_____________________________________________________________________
>
>* IDUG North America * Tampa, Florida, * May 10-14 2010 *
>http://IDUG.ORG/NA *
>_____________________________________________________________________
>
>http://www.idug.org/db2-content/index.html has THOUSANDS of free technical
>presentations!
>DB2 LUW, DB2 z/OS, Performance, Installation, Tuning, Coding, BI,
>Warehouses, - among
>many more categories of help waiting for you!
>Whether you are an old hand or a DB2 newbie, we have presentations for
>every level.
>_____________________________________________________________________
>
>If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is
>the home of IDUG's DB2-L
>
>
>
>
>IMPORTANT - CONFIDENTIALITY NOTICE - This e-mail is intended only for
>the use of the individual or entity shown above as addressees. It may
>contain information which is privileged, confidential or otherwise
>protected from disclosure under applicable laws. If the reader of this
>transmission is not the intended recipient, you are hereby notified that
>any dissemination, printing, distribution, copying, disclosure or the
>taking of any action in reliance on the contents of this information is
>strictly prohibited. If you have received this transmission in error,
>please immediately notify us by reply e-mail or using the address below
>and delete the message and any attachments from your system.
>
>Amadeus Data Processing GmbH
>Gesch�ftsf�hrer: Eberhard Haag
>Sitz der Gesellschaft: Erding
>HR M�nchen 48 199
>Berghamer Strasse 6
>85435 Erding
>Germany
>
>_____________________________________________________________________
>
>* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
>_____________________________________________________________________
>
>http://www.idug.org/db2-content/index.html has THOUSANDS of free technical
presentations!
>DB2 LUW, DB2 z/OS, Performance, Installation, Tuning, Coding, BI,
Warehouses, - among
>many more categories of help waiting for you!
>Whether you are an old hand or a DB2 newbie, we have presentations for
every level.
>_____________________________________________________________________
>
>If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is
the home of IDUG's DB2-L

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.IDUG.org membership is now free.
Do you have people in your office who are not an IDUG member?
Show them how to access the information and help train the next generation of DB2 Users!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Patrick Bossman

Re: Mathematician wanted - number of potential indexes
(in response to Joe Geller)
You could create both NPI vs DPSI.
RANDOM / NOT RANDOM
UNIQUE, UNIQUE WHERE NOT NULL, non-unique.

You can only have 64 columns in an index.
The length of indexed columns is also limited.
http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/topic/com.ibm.db29.doc.ugref/db2z_limits.htm

Regards,
Pat

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.IDUG.org membership is now free.
Do you have people in your office who are not an IDUG member?
Show them how to access the information and help train the next generation of DB2 Users!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Joe Geller

Re: Mathematician wanted - number of potential indexes
(in response to Patrick Bossman)
And, the SQL to get the answer is:

with nx (nxc,n) as
(select 1, 2 from sysibm.sysdummy1
union all
select n * nxc + n, n+1 from nx where n <= 12)
select n-1, nxc from nx;

Note that while an index can have 64 columns, I only generate the answers up
to 12, because of overflow on the integer data type.

Joe

On Tue, 26 Jan 2010 13:55:01 +0000, Michael Ebert
<[login to unmask email]> wrote:

>Actually
>
>f(0) = 0
>f(n) = n*(f(n-1)+1) = n*f(n-1)+n
>
>so it goes up a bit faster than n!.
>
>Dr. Michael Ebert
>DB2 & Oracle Database Administrator
>aMaDEUS Data Processing
>Erding / Munich, Germany
>
>
>
>
>Roger Hecq <[login to unmask email]>
>To
>[login to unmask email]
>cc
>
>bcc
>
>Subject
>Re: [DB2-L] Mathematician wanted - number of potential indexes
>
>
>
>
>
>Roger Hecq <[login to unmask email]>
>Please respond to : IDUG DB2-L <[login to unmask email]>
>Sent by: IDUG DB2-L <[login to unmask email]>
>26-01-10 14:06
>
>
>I can't find the slate that I etched my math notes on, but I believe that
>the answer is n factorial. N factorial will give you the number of
>permutations on n objects.
>Roger Hecq
>MF IB USA DB Support
>203-719-0492 / 19-337-0492
>
>
>From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Phil
Grainger
>Sent: Tuesday, January 26, 2010 7:00 AM
>To: [login to unmask email]
>Subject: [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?
>
>Thanks
>
>Phil Grainger
>Grainger Database Solutions Ltd
>
>
>
>The IDUG DB2-L Listserv is only part of your membership in IDUG. If you
>are not already an IDUG member, please register here.
>
>
>
>The IDUG DB2-L Listserv is only part of your membership in IDUG. If you
>are not already an IDUG member, please register here. Visit our website at
>http://www.ubs.com
>
>This message contains confidential information and is intended only
>for the individual named. If you are not the named addressee you
>should not disseminate, distribute or copy this e-mail. Please
>notify the sender immediately by e-mail if you have received this
>e-mail by mistake and delete this e-mail from your system.
>
>E-mails are not encrypted and cannot be guaranteed to be secure or
>error-free as information could be intercepted, corrupted, lost,
>destroyed, arrive late or incomplete, or contain viruses. The sender
>therefore does not accept liability for any errors or omissions in the
>contents of this message which arise as a result of e-mail transmission.
>If verification is required please request a hard-copy version. This
>message is provided for informational purposes and should not be
>construed as a solicitation or offer to buy or sell any securities
>or related financial instruments.
>
>
>UBS reserves the right to retain all messages. Messages are protected
>and accessed only in legally justified cases.
>
>__________________________________________________________
___________
>
>* IDUG North America * Tampa, Florida, * May 10-14 2010 *
>http://IDUG.ORG/NA *
>__________________________________________________________
___________
>
>http://www.idug.org/db2-content/index.html has THOUSANDS of free
technical
>presentations!
>DB2 LUW, DB2 z/OS, Performance, Installation, Tuning, Coding, BI,
>Warehouses, - among
>many more categories of help waiting for you!
>Whether you are an old hand or a DB2 newbie, we have presentations for
>every level.
>__________________________________________________________
___________
>
>If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is
>the home of IDUG's DB2-L
>
>
>
>
>IMPORTANT - CONFIDENTIALITY NOTICE - This e-mail is intended only
for
>the use of the individual or entity shown above as addressees. It may
>contain information which is privileged, confidential or otherwise
>protected from disclosure under applicable laws. If the reader of this
>transmission is not the intended recipient, you are hereby notified that
>any dissemination, printing, distribution, copying, disclosure or the
>taking of any action in reliance on the contents of this information is
>strictly prohibited. If you have received this transmission in error,
>please immediately notify us by reply e-mail or using the address below
>and delete the message and any attachments from your system.
>
>Amadeus Data Processing GmbH
>Gesch�ftsf�hrer: Eberhard Haag
>Sitz der Gesellschaft: Erding
>HR M�nchen 48 199
>Berghamer Strasse 6
>85435 Erding
>Germany
>
>__________________________________________________________
___________
>
>* IDUG North America * Tampa, Florida, * May 10-14 2010 *
http://IDUG.ORG/NA *
>__________________________________________________________
___________
>
>http://www.idug.org/db2-content/index.html has THOUSANDS of free
technical presentations!
>DB2 LUW, DB2 z/OS, Performance, Installation, Tuning, Coding, BI,
Warehouses, - among
>many more categories of help waiting for you!
>Whether you are an old hand or a DB2 newbie, we have presentations for
every level.
>__________________________________________________________
___________
>
>If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is
the home of IDUG's DB2-L

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.IDUG.org membership is now free.
Do you have people in your office who are not an IDUG member?
Show them how to access the information and help train the next generation of DB2 Users!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L