Home » SQL & PL/SQL » SQL & PL/SQL » Number of unique keys in the table (server 2003, 11g r1)
Number of unique keys in the table [message #433360] Tue, 01 December 2009 22:48 Go to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Hi all,

I have one question regarding unique keys...
How many unique keys can we have in the table?

Thanks.

regards,
Delna
Re: Number of unique keys in the table [message #433361 is a reply to message #433360] Tue, 01 December 2009 23:07 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
http://download.oracle.com/docs/cd/B14117_01/server.101/b10743/data_int.htm#sthref3009

I dont think there is any limitation on the number of unique keys in tables but as mentioned in the link Oracle maintains an index for each unique key(basically this leads to the question as how many indexes can oracle allow on a table that is bound by the question how much physical storage you have on disk)
Oracle enforces unique integrity constraints with indexes. Oracle enforces the UNIQUE key constraint by implicitly creating a unique index on the composite unique key. Therefore, composite UNIQUE key constraints have the same limitations imposed on composite indexes: up to 32 columns can constitute a composite unique key.

http://download.oracle.com/docs/cd/B14117_01/server.101/b10743/schema.htm#CNCPT811

from link
You can create many indexes for a table as long as the combination of columns differs for each index. You can create more than one index using the same columns if you specify distinctly different combinations of the columns.

[Updated on: Tue, 01 December 2009 23:09]

Report message to a moderator

Re: Number of unique keys in the table [message #433463 is a reply to message #433360] Wed, 02 December 2009 07:46 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
2^N - 1
Re: Number of unique keys in the table [message #433547 is a reply to message #433463] Wed, 02 December 2009 22:23 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Quote:
2^N - 1


Laughing

Thanks all...

regards,
Delna
Re: Number of unique keys in the table [message #433607 is a reply to message #433360] Thu, 03 December 2009 04:56 Go to previous messageGo to next message
mamalik
Messages: 266
Registered: November 2008
Location: Pakistan
Senior Member

What Is "N" In 2^N-1?
Re: Number of unique keys in the table [message #433610 is a reply to message #433607] Thu, 03 December 2009 05:07 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Well, the only "N" that would make any sense in regards to a table would be the number of columns or the number rows.

Which of those "N"s would make more sense in regards to the possible number of unique keys?
Re: Number of unique keys in the table [message #433732 is a reply to message #433360] Thu, 03 December 2009 20:08 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
would not have thought of it first off but yes, it is a question of permutation theory. Consider this table:

create table t (a number,b number,c number);

how many indexes can you have? A permutation is a set of ordered elements. Thus for this table we can create an index with 1, 2, or 3 columns. Here is the full list of possible indexes for this table. Notice the order of columns is important.

create or replace index t_ia on t (a);
create or replace index t_ib on t (b);
create or replace index t_ic on t (c);

create or replace index t_id on t (a,b);
create or replace index t_ie on t (a,c);
create or replace index t_if on t (b,c);
create or replace index t_ig on t (b,a);
create or replace index t_ih on t (c,a);
create or replace index t_ii on t (c,b);

create or replace index t_ij on t (a,b,c);
create or replace index t_ik on t (a,c,b);
create or replace index t_il on t (b,a,c);
create or replace index t_im on t (b,c,a);
create or replace index t_in on t (c,a,b);
create or replace index t_io on t (c,b,a);

= 15 indexes

--
-- hehehe I am a moderator now, I can edit out the errors in my posts
--
n=3,  2^(N+1)=16,  16-1=15


Last I knew the max number of columns in an index was 32 so technically the max is 2^33-1 or 8,589,934,591.

** as an aside this answer is actually wrong. We have to consider that the underlying formula is only correct for tables with <= 32 columns. Oracle actually allows last I heard, up to 1,000 columns in a table so in reality I think we want permutations of N things taken K at a time or (1000!)/(1000-32)! which will not calculate out on my calculater without using engineering notation. It is something less than the more liberal N^K or 1000^32 formula in which repeatition is allowed. But enough about the math...

Of course if we are taking things seriously... assuming column names are short and you make no mistakes I estimate for an empty table, it will take 2 minutes to type the index and get it created. Assuming you work a regular work day and account for bathroom breaks, lunch time, and water cooler conversations, and you do nothing else but work on the indexes otherwise, you get six hours a day and you take the typical holidays and weekends and vacations for a 250 day year, we get the following calculations:

8,589,934,591*2/60/6/250 = 190886 years to create all those indexes. This could be considered the maximum number of indexes on a table. Of course this won't work because you will be dead long before then. So if you like, under a physically possible scenario using the same assumptions as before of creating 30 indexes/hour etc.

30*6*250*40 = 1,800,000 indexes created during a 40 year carreer consisting of doing nothing but creating indexes on the table. Maybe four or five times this number if you do only short indexes.

Of course though this is physically possible, it is also totally impractical given that the need for the indexed table will die long before it is ready, one would look for a different way. Assuming you are able to figure out some kind of code generator that will generate the index scripts for you then it is a totally different ballgame.

But, if you can't figure out how to do it in an automated fashion then in a practical environment you would be limited in the amount of time you have to write a script, and you would not actually create an index for every possible combination and you would likely have many tables for which you need to create indexes. If we allow one regular work day of six hours during which we work exclusively to create indexes on one table at the same rate of two minutes per index as before, then for each empty table we get this as one plausible definition of a practical limit of the number of indexes on a table.

30*6 or 180 indexes per table would be a practical limit based on our now superior understanding that PRACTICAL is based on the limitations of people and the processes we create rather than the machines and software performing for us.

So from now on, the answer to "HOW MANY INDEXES CAN I CREATE ON A TABLE" is 180. Given the logic above, this answer is good for all databases (Oracle, SqlServer, DB2, Terradata ...). This is the answer I am going to give whenever anyone asks me, not that this is a question I get asked a lot.

Good luck, Kevin

[Updated on: Sat, 05 December 2009 14:02]

Report message to a moderator

Re: Number of unique keys in the table [message #433757 is a reply to message #433732] Fri, 04 December 2009 02:06 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
Quote:

n=3, 2^N=16

is n=N equal, if yes then 2^3=8 not 16

then it would be (2^(n+1))-1 Smile

[Updated on: Fri, 04 December 2009 02:06]

Report message to a moderator

Re: Number of unique keys in the table [message #433807 is a reply to message #433361] Fri, 04 December 2009 06:03 Go to previous messageGo to next message
jagadeesh1985
Messages: 4
Registered: November 2009
Location: Hyderabad
Junior Member
There is no limit for that.It is up to your requirement.
Re: Number of unique keys in the table [message #433816 is a reply to message #433807] Fri, 04 December 2009 06:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What does your post add to the previous ones? Above all when it is wrong.

Regards
Michel
Re: Number of unique keys in the table [message #433828 is a reply to message #433757] Fri, 04 December 2009 07:50 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
Its_me_ved wrote on Fri, 04 December 2009 03:06
Quote:

n=3, 2^N=16

is n=N equal, if yes then 2^3=8 not 16

then it would be (2^(n+1))-1 Smile


Uh-oh! Look what I started. I too like Kevin somehow flubbed the simple math an had (2^3 - 1) = 15. And it's such a shame because his answer was beautifully explained.
I was never good at statistics. I came up with the formula quickly that I was incorrect. Now, I don't want to wrack my brain for the correct permutation formula and get it wring again.
Re: Number of unique keys in the table [message #433865 is a reply to message #433360] Fri, 04 December 2009 10:50 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
hehehe, I am a moderator now... I can edit the math errors out of my posts and no one will be the wiser.

Of course I would point out that Delna's question was not really how many indexes can we create on a table but rather now many UNIQUE keys can we have in a table. This question is ambiguous of course for we do not know if it is referring to rows or constraint definitions. Each row would present one unique key each of the primary and unique constraints on the table so we could interpret it that way, or we could interpret the question to be asking how many UNIQUE indexes can be created, and we decided to interpret based on the later.

Kevin

[Updated on: Fri, 04 December 2009 10:54]

Report message to a moderator

Re: Number of unique keys in the table [message #433881 is a reply to message #433828] Fri, 04 December 2009 14:12 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
Kevin Sir has explained it so beautifully..many thanks to him...

Regards,
Ved

[Updated on: Fri, 04 December 2009 14:36]

Report message to a moderator

Re: Number of unique keys in the table [message #434107 is a reply to message #433881] Mon, 07 December 2009 03:51 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I think that the number of different unique constraints that you can have on a table with n columns is the sum over values of j from 1 to m of the function
m!/(m-j)!

or:
 m
 --   m!
 \  -----
 /  (m-j)!
 --
j=1


I'd be suprised if this helps.
Re: Number of unique keys in the table [message #434153 is a reply to message #433360] Mon, 07 December 2009 08:28 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Yes, I mention it in my post that I keep correcting.

Yes, you are correct, it does not help.

However, it all points out to the importance of the question. Like with many questions, the answer is meaningless, the thought process exercised in finding it is important.

Kevin
Previous Topic: Update table c1 for all entries that matches table c2 based on matching column a
Next Topic: loop with condition
Goto Forum:
  


Current Time: Sun Dec 11 02:18:22 CST 2016

Total time taken to generate the page: 0.07524 seconds