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: Always Steps to Reproduce: . Additional info: Oracle 8.1.7.4 with 8k block size.
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.
Hm, how realistic would it be to just recommend a larger blocksize on 8i?
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.
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.
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 this: # 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
[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.
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.
Thanks, Branimir. Fixed.
Er... we have the same thing with cms_items(ancestors)
Ok, let's see if that's the last one :). Fixed.