Skip to Main Content
  • Questions
  • Getting ORA-01404: ALTER COLUMN will make an index too large

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Premkumar.

Asked: February 19, 2021 - 1:02 pm UTC

Last updated: February 22, 2021 - 9:57 am UTC

Version: 12C

Viewed 100+ times

You Asked

Hi Team,

I have been trying to alter the size of a column in a table which is part of a composite primary key. When I try altering the size of that particular column, I got "ORA-01404: ALTER COLUMN will make an index too large" error. Can you please help us understanding why we should get this error and how to verify the size of the index to resolve this. Thank you!

and we said...

Each index entry (the values from the indexed columns + rowids) must be able to fit in an index block. By default, a block is 8,192 bytes. So each index entry must be smaller than that, minus some overheads. Which works out to around 6,400 bytes.

You'll hit this error if you try and increase the length of a column so the largest possible values you can store are bigger than this.

For example, this tries to extend the length of primary key from 5,000 => 8,000, which is way too big to fit in a block:

create table t (
  c1 varchar2(4000), c2 varchar2(1000),
  primary key ( c1, c2 )
);

alter table t 
  modify c2 varchar2(4000);
  
ORA-01404: ALTER COLUMN will make an index too large


This is the same problem as the ORA-01450 error you can get when creating an index on columns too large. I discuss solutions for this at:

https://blogs.oracle.com/sql/how-to-fix-ora-01450-maximum-key-length-6398-exceeded-errors

You can't use a function-based index to support a primary key. While you can hide the function-based index behind a virtual column, this means the virtual column will be in your primary key. This likely adds too much complication to your application to be workable.

So when it comes to primary key columns, your workarounds are limited to

* Use shorter data type limits
* Create tablespaces with larger blocksizes to store these indexes
* Add a surrogate key and define a unique function-based index on the current primary key columns

The first point may seem a no-go - after all the data types have to be large enough to store the biggest value - but the point is to avoid using large limits "just in case". If one of the primary key columns is a varchar2(4000), but the maximum stored string length is 100 characters, consider shrinking the column to a much smaller upper limit.

More to Explore

Design

New to good database design? Check out Chris Saxon's full fundamentals class.