Can you solve this blocksize puzzle?

Here’s a little puzzle to get your mind working

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 🙂