Solved

Index problem : max lenght exceed

Posted on 2001-09-17
4
954 Views
Last Modified: 2010-08-05
Hi,

I'm working with Oracle 8.1, and I have a little problem.
I need to create an index (UNIQUE) on 10 columns. The problem is that 3 of this columns have are VARCHAR2 (255).

So each time I tried to create my table I have this error message : ORA-01450: maximum key length (758) exceeded.

I've tried to create the constrainte in Alter table instead in the create table... but it gives the same message.

And I cannot remove any field in the constraint... there's a way to do the job ?

Thanks.

Mary.



CODE :

ALTER TABLE InterventionPlans
    ADD CONSTRAINT UniqueInterventionPlans
    UNIQUE(AgencyId, ResponseTypeId, RecommendationId, Data1,
      Data2, Data3, AlarmLevel, FromWeekDay, FromTime, ToWeekDay, ToTime)
0
Comment
Question by:msl22
4 Comments
 
LVL 3

Accepted Solution

by:
mathavra earned 50 total points
ID: 6488482
This primarily depends upon your DB_BLOCK_SIZE. I think your DB_BLOCK_SIZE is set to 2K. Here are the maximum key length associated with the DB_BLOCK_SIZE value. If possible change the BLOCK size and create the database again to acheive the higher value.

NOTE: You cannot change the DB_BLOCK_SIZE once the database has been created. To change it, you need to create the entire database again.

DB_BLOCK_SIZE     MAX KEY LENGTH
--------------------------------
2K (2048)         758 Bytes
4K (4096)         1578 Bytes
8K (8192)         3218 Bytes
16K (16384)       6498 Bytes


From a Oracle document:
-----------------------

This is how is the max. number calculated?

The maximum key size means:

The total index length + length of the key (2 Bytes) + ROWID (6 Bytes) + the length of the rowid (1 byte).

The total index length is computed as the sum of the width of all indexed columns plus the number of indexed columns. Date fields have a length of 7, character fields have their defined length, and numeric fields have a length of 22. Numeric length = (precision/2) + 1. If negative, add +1. For Funtion-based indexes, we must calculate the length of the return type.

This index key size is limited by the value of db_block_size, because a key value may not span multiple blocks. In fact, it is required that any index block must contain at least TWO index entries per block.

Therefore, the maximum key length for an index will be less than half of the DB_BLOCK_SIZE. The Oracle 8i Administrator's Guide states that the maximum size of a single index entry is approximately one-half the data block size. However, when considering that we must also leave space in the blockaccording to PCTFREE, INITRANS, and space for block overhead (Block Header,ROW Directory, Table Directory, etc) the actual space that can be used for the Index key is actually just over 1/3 of the DB_BLOCK_SIZE.

Using default values for these storage options, the maximum length for indexes is as follows for different block sizes:

DB_BLOCK_SIZE: Maximum Index Key Length:
=============== =====================

2K (2048) 758 Bytes
4K (4096) 1578 Bytes
8K (8192) 3218 Bytes
16K (16384) 6498 Bytes

If you hit a maximum key length in an index according to the DB_BLOCK_SIZE, you may need to recreate the database with a larger block size. The otheralternative is to limit the size of the index. This is slightly more difficult with a Function-based index, when the return type is a varchar or RAW.

To limit the size of a function-based index you should consider using the SUBSTR or SUBSTRB function, to limit the number of Characters or Bytes returned. For more information on SUBTR and SUBSTRB, refer to the Oracle8i SQL Reference Guide.

Hope this helps!

Mathav

0
 

Expert Comment

by:psteinhe
ID: 6505844
There might be something wrong in your design if you have that many columns in your key. You might want to use a system-generated sequence number instead and store the other columns as not null attributes. With that many columns, joins will be real messy.
0
 
LVL 6

Expert Comment

by:Mindphaser
ID: 7045880
Please update and finalize this old, open question. Please:

1) Award points ... if you need Moderator assistance to split points, comment here with details please or advise us in Community Support with a zero point question and this question link.
2) Ask us to delete it if it has no value to you or others
3) Ask for a refund so that we can move it to our PAQ at zero points if it did not help you but may help others.

EXPERT INPUT WITH CLOSING RECOMMENDATIONS IS APPRECIATED IF ASKER DOES NOT RESPOND.

Thanks,

** Mindphaser - Community Support Moderator **

P.S.  Click your Member Profile, choose View Question History to go through all your open and locked questions to update them.
0
 
LVL 49

Expert Comment

by:DanRollins
ID: 7058522
Recommended disposition:

    Accept mathavra's comment(s) as an answer.

DanRollins -- EE database cleanup volunteer
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now