Can you solve this blocksize puzzle?

Posted by

Here’s a little puzzle to get your mind working Smile

It is generally well known that there is a limit to the length of an index key in the Oracle database, the length being related to the block size for your database, or the block size of the tablespace where you are trying to create that index.

Just like many long terms DBA’s are familiar the numbers the represent the old extent limits (505 for 8k, 249 for 4k and 121 for 2k), the numbers of index key limits are often easy to recall as well, but here’s a couple of quick demos to prove I’m not just pulling numbers out of thin air. In both cases, I’m trying to create a composite index on 2×3500 byte columns which cannot be done in the block sizes I’m attempting:

Blocksize = 8k


SQL> select username, default_tablespace
  2  from   all_users
  3  where  username = sys_context('USERENV','CURRENT_SCHEMA');

USERNAME             DEFAULT_TABLESPACE
-------------------- ------------------------------
SCOTT                TS8K

SQL> select block_size
  2  from   dba_tablespaces
  3  where  tablespace_name = 'TS8K';

BLOCK_SIZE
----------
      8192

SQL> create table t (x varchar2(3500),y varchar2(3500));

Table created.

SQL> create index ix on t ( x,y);
create index ix on t ( x,y)
                   *
ERROR at line 1:
ORA-01450: maximum key length (6398) exceeded

Blocksize = 4k


SQL> select username, default_tablespace
  2  from   all_users
  3  where  username = sys_context('USERENV','CURRENT_SCHEMA');

USERNAME             DEFAULT_TABLESPACE
-------------------- ------------------------------
SCOTT                TS4K

SQL> select block_size
  2  from   dba_tablespaces
  3  where  tablespace_name = 'TS4K';

BLOCK_SIZE
----------
      4096

SQL> create table t (x varchar2(3500),y varchar2(3500));

Table created.

SQL> create index ix on t ( x,y);
create index ix on t ( x,y)
                   *
ERROR at line 1:
ORA-01450: maximum key length (3118) exceeded

Thus we are capped at approximately 75% of the block’s size for the index key length.

So here’s the puzzle for you. Under what scenario can you be trying to create an index in 8k blocksize tablespace yet still get the error saying your index key exceeds the 4k limit?


SQL> select username, default_tablespace
  2  from   all_users
  3  where  username = sys_context('USERENV','CURRENT_SCHEMA');

USERNAME             DEFAULT_TABLESPACE
-------------------- ------------------------------
SCOTT                TS8K

SQL> select block_size
  2  from   dba_tablespaces
  3  where  tablespace_name = 'TS8K';

BLOCK_SIZE
----------
      8192

SQL> create table t (x varchar2(3500),y varchar2(3500));

Table created.

SQL> create index ix on t ( x,y);
create index ix on t ( x,y)
                   *
ERROR at line 1:
ORA-01450: maximum key length (3118) exceeded  < ===== ??????

Answer posted soon, but if you solve it, feel free to post a comment!

Update:

And here’s the interesting reason why a customer was hitting this issue

If you have a database that is defined with a 4k default blocksize, but you have a 8k blocksize tablespace for particular requirements, then you’ll observe the following:

  • “create index MY_INDEX tablespace 8K”, ie, explicitly nominating the tablespace works as expected
  • “create index MY_SCHEMA.MY_INDEX”, ie, explicitly nominating a schema who has a default tablespace of the 8K tablespace will also work as expected
  • “alter session set current_schema = MY_SCHEMA”, then followed by “create index MY_INDEX”, ie, using the schema session switch and not nominating a tablespace or schema, will erroneously check the key length against the default blocksize.

Bottom line – its always good practice to nominate a tablespace 🙂

8 comments

  1. Why not use syntax create index ix on t ( x,y) tablespace to be sure that correct tablespace is used?
    Maybe “ALTER SESSION SET current_schema=scott” was used and now it conflicts with owner default tablespace?

    1. We found this issue via a database encoding issue, but its not specifcally related to that, which is why I used the example above which doesn’t matter whatever characterset you use.

  2. Hi Connor,

    Just an exotic idea:
    Maybe there exists an INSTEAD OF CREATE trigger , that adds a non-default TABLESPACE
    to the CREATE statement, thus attempting to create the index in a tablespace with 4K block size ?

    Cheers & Best Regards,
    Iudith Mentzel

      1. My understanding – As per oracle doc:
        DBA_INDEXES: https://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_1069.htm#i1578369
        PCT_THRESHOLD NUMBER Threshold percentage of block space allowed per index entry
        May be pct_threshold was set to some think like 38% of the block size.

        However , as create index doesn’t include any extra clauses. So that’s not the answer.

        from other dba:
        (8k-overhead) is usable space. (rowid+x+y) > (usable space/2)

Got some thoughts? Leave a comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.