index limits ? - DB2 Database

platho

Hello,

I bounced into the max 25 columns index limits on DB2 v7.2 on NT.
Is this still so in other operating systems or in v8 ? Are there plans to
change this in the future ? Any workarounds or hints or tips? I think
Oracle allows 30 fields in an index ...

Thank you very much for an answer

Regards

Nov 12 '05 #1

Subscribe Post Reply

2488

Mark A

"platho" <pl****@nospam-pandora.be> wrote in message
news:ea*******************@phobos.telenet-ops.be...

Hello,

I bounced into the max 25 columns index limits on DB2 v7.2 on NT.
Is this still so in other operating systems or in v8 ? Are there plans to
change this in the future ? Any workarounds or hints or tips? I think
Oracle allows 30 fields in an index ...

Thank you very much for an answer

Regards

According to the SQL Limits section of the Version 8 SQL Reference (Appendix
8), here are the limits:

Longest index key including all overhead (in bytes) 1024
Most columns in an index key 16

Same limits in DB2 Version 7.2 SQL Reference manual.

I assume that you need that many columns because of a unique index. You
would never need that many columns for performance. I hope you don't mind me
telling you this, but if you need more than 16 the design is "questionable."

Workaround is to create a translation table with a unique key
(non-meaningful) that has all the columns that make it unique. Then use that
single unique key in all other tables (instead of the many columns). This is
called good design.

Nov 12 '05 #2

platho

"Mark A" <ma@switchboard.net> wrote in message
news:Lo*****************@news.uswest.net...

"platho" <pl****@nospam-pandora.be> wrote in message
news:ea*******************@phobos.telenet-ops.be...
Hello,

I bounced into the max 25 columns index limits on DB2 v7.2 on NT.
Is this still so in other operating systems or in v8 ? Are there plans to change this in the future ? Any workarounds or hints or tips? I think
Oracle allows 30 fields in an index ...

Thank you very much for an answer

Regards
According to the SQL Limits section of the Version 8 SQL Reference

(Appendix 8), here are the limits:

Longest index key including all overhead (in bytes) 1024
Most columns in an index key 16

Same limits in DB2 Version 7.2 SQL Reference manual.

I assume that you need that many columns because of a unique index. You
would never need that many columns for performance. I hope you don't mind me telling you this, but if you need more than 16 the design is "questionable."
Workaround is to create a translation table with a unique key
(non-meaningful) that has all the columns that make it unique. Then use that single unique key in all other tables (instead of the many columns). This is called good design.

Thanks for you answer, I mistyped and said 25 instead of 15 ....
Anyway this was my opinion also but the customer wouldn't believe me and
immediately pointed to Oracla, which is capable of doing this without any
problems.

thanks again for the confirmation,

Nov 12 '05 #3

Mark A

> Thanks for you answer, I mistyped and said 25 instead of 15 ....

Anyway this was my opinion also but the customer wouldn't believe me and
immediately pointed to Oracla, which is capable of doing this without any
problems.

thanks again for the confirmation,

It's possible to do a lot of stuff with software that is not advisable. If
you client has no actual application that needs that many columns in a
unique index, and they are just comparing "numbers" in order to choose a
DBMS product, you should point out to them that having even 16 columns in a
unique key is very poor design, and having that limitation is a blessing in
disguise to prevent a poor design from being implemented. You should also
point out that as the index gets larger, performance suffers (not to mention
having to process that many columns in the WHERE clause of an SQL statement.

So the 16 column limitation is a safety feature (not a limitation) to
prevent bad design being implemented that Oracle does not have.

Nov 12 '05 #4

KMBlaskie

I'm sorry...but if your pressing the column limit on an index....it is time to
get a big stick and go beat the designers senseless.....

Denormalize that puppy!

Nov 12 '05 #5

Similar topics

beginning index for generators

by: kosh | last post by:

I was wondering if there is or there could be some way to pass a generator an optional starting index so that if it supported that slicing could be made more efficient. Right now if you do use a...

Python

Index and foreign key

by: Olivier Crèvecoeur | last post by:

Hello, Excuse me for my poor english. I would kike know if create index on the foreign key it's necessary or if Oracle, are optimized for using foreign key whithout index. Best regards ...

Oracle Database

Is there a restriction for an index creation?

by: Markus Keuthmann | last post by:

Anybody knows a restriction for an index creation on a text column? How deep can an index for a text go? Somebody told me that a MySQL-Index has a restriction on 255 strings! And DB2? Is there...

DB2 Database

type of array index?

by: shmartonak | last post by:

For maximum portability what should the type of an array index be? Can any integer type be used safely? Or should I only use an unsigned type? Or what? If I'm using pointers to access array...

C / C++

THE IMPORTANCE OF MAKING THE GOOGLE INDEX DOWNLOADABLE

by: gen_tricomi | last post by:

THE IMPORTANCE OF MAKING THE GOOGLE INDEX DOWNLOADABLE I write here to make a request on behalf of all the programmers on earth who have been or are intending to use the Google web search API...

Python

122

"index" method only for mutable sequences??

by: C.L. | last post by:

I was looking for a function or method that would return the index to the first matching element in a list. Coming from a C++ STL background, I thought it might be called "find". My first stop was...

Python

Direct computation of integer limits in K&R2?

by: santosh | last post by:

Hello all, In K&R2 one exercise asks the reader to compute and print the limits for the basic integer types. This is trivial for unsigned types. But is it possible for signed types without...

C / C++

Storing value with limits in object

by: Josip | last post by:

I'm trying to limit a value stored by object (either int or float): class Limited(object): def __init__(self, value, min, max): self.min, self.max = min, max self.n = value def...

Python

Environmental limits

by: vippstar | last post by:

n1256.pdf (C99 TC3), 5.2.4.1 Translation limits p1 says: Does that mean that *any* program using an array of two or more elements other than char is allowed to be rejected by an implementation?...

C / C++

How to turn on java script in a villaon keypad mobile phone

by: Charles Arthur | last post by:

How do i turn on java script on a villaon, callus and itel keypad mobile phone

Java

Migrating Website to Cloud - Emmanuel Katto

by: emmanuelkatto | last post by:

Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel

General

What is ONU?

by: marktang | last post by:

ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...

General

Problem With Comparison Operator <=> in G++

by: Oralloy | last post by:

Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...

C / C++

Maximizing Business Potential: The Nexus of Website Design and Digital Marketing

by: jinu1996 | last post by:

In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...

Online Marketing

The easy way to turn off automatic updates for Windows 10/11

by: Hystou | last post by:

Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...

Windows Server

Discussion: How does Zigbee compare with other wireless protocols in smart home applications?

by: tracyyun | last post by:

Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...

General

AI Job Threat for Devs

by: agi2029 | last post by:

Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...

Career Advice

Access Europe - Using VBA to create a class based on a table - Wed 1 May

by: isladogs | last post by:

The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new...

Microsoft Access / VBA