Bug 97479 - Creation of two indexes fails
Summary: Creation of two indexes fails
Alias: None
Product: Red Hat Web Application Framework
Classification: Retired
Component: installation
Version: nightly
Hardware: All
OS: Linux
Target Milestone: ---
Assignee: ccm-bugs-list
QA Contact: Jon Orris
Depends On:
Blocks: rc0blockers
TreeView+ depends on / blocked
Reported: 2003-06-16 15:00 UTC by Branimir Dolicki
Modified: 2007-04-18 16:54 UTC (History)
1 user (show)

Fixed In Version:
Doc Type: Bug Fix
Doc Text:
Clone Of:
Last Closed: 2003-06-27 16:34:31 UTC

Attachments (Terms of Use)

Description Branimir Dolicki 2003-06-16 15:00:51 UTC
From Bugzilla Helper:
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.2.1) Gecko/20030225

Description of problem:
When loading the core data model into our 8i instance with block size of 8192,
this happens:

Create index cat_cat_deflt_ancestors_idx on cat_categories(default_ancestors)
ERROR at line 1:
ORA-01450: maximum key length (3218) exceeded

And also when loading the cms datamodel:

create index cms_items_ancestors_idx on cms_items(ancestors)
ERROR at line 1:
ORA-01450: maximum key length (3218) exceeded

I think this doesn't happen on 9i.  On 8i, for the block size of 8192 bytes that
we usually use (and recommend) this happens.

I guess, we might write a piece of clever PL/SQL to find out maximum key length
(depending on database version) and then use that when creating those two tables...

Or we could simply make these two columns varchar2(3128) instead of
varchar2(4000) and let people change it themselves if they need longer keys.

Version-Release number of selected component (if applicable):

How reproducible:

Steps to Reproduce:

Additional info:

Oracle with 8k block size.

Comment 1 Daniel Berrangé 2003-06-16 15:12:01 UTC
We've seen this before on a 8i database. Aram told us that for a given
blocksize, different versions of oracle can manage different key lengths.
Apparently 9i manages about 1/2 blocksize, where as 8i is noticably less.

Comment 2 Richard Li 2003-06-16 19:54:41 UTC
Hm, how realistic would it be to just recommend a larger blocksize on 8i?

Comment 3 Branimir Dolicki 2003-06-17 07:57:31 UTC
AFAIK, most people use 8k.  Now, without going into details of pros and cons of
various block sizes it seems that these two indices are not enough justification
for mandating, say 16k block size for the entire instance.  That is not to say
that some people who have really good reason to have index size bigger than 3218
bytes wouldn't tune their block size and issue a few alter table statements to
achieve large index size.

So, I really think that the installation script should be robust enough to adjust
itself to the block size.  We should also add a paragraphs about these things
in installation docs so people can tune these things afterwards.

Comment 4 Richard Li 2003-06-23 17:50:39 UTC
What you are proposing might be the right solution but that would require a bit
of added infrastructure, which we don't have.

Right now we programmatically generate the SQL from PDL (mostly) and have the
user manually source them using SQL*Plus.

The only solution that I think we can do without building a programmatic SQL
loading environment (which doesn't seem to be worth it to me for just this one
case) is more accurate documentation.

The clever PL/SQL procedure is an idea but it seems to me to be a big
maintenance and testing headache.

Comment 5 Branimir Dolicki 2003-06-23 18:32:12 UTC
I agree.   I guess the most straightforward thing to do is to make 3128
the default.  The next thing would be to add a parameter to the config.vars like

# You can set this to 4000 if you are using Oracle 9i and your block size is 8k.
# For other block sizes on your database and relation of maximum index size
# and block size consult your database documentation.
max_index_size = 3128

Comment 6 Richard Li 2003-06-23 19:27:42 UTC
[richardl@conundrum p4]$ p4 describe -s 32744
Change 32744 by richardl@richardl-afs on 2003/06/23 15:27:04

	   Fix BZ97479 by changing path denormalization column from VARCHAR(4000) to
	   VARCHAR(3218). This is OK because maximum length for category is NUMBER(38)
	   which means the reduced column can still handle a category hierarchy
	   84 levels deep.

Comment 7 Branimir Dolicki 2003-06-27 13:00:19 UTC
I'm reopening this because it turned out that even with varchar2(3218) you
still get ORA-01450: maximum key length (3218) exceeded.  By doing a binary
search I found out that the maximum width that will work is varchar2(3209).
It looks like Oracle needs the nine bytes of the key for something.

Comment 8 Richard Li 2003-06-27 13:03:03 UTC
Thanks, Branimir. Fixed.

Comment 9 Branimir Dolicki 2003-06-27 16:29:30 UTC
Er... we have the same thing with cms_items(ancestors)

Comment 10 Richard Li 2003-06-27 16:34:31 UTC
Ok, let's see if that's the last one :). Fixed.

Note You need to log in before you can comment on or make changes to this bug.