How to calculate the number of columns in a create index statement giving ORA-01793: Maximum Number Of Index Columns Is 32 (Doc ID 1562496.1)

Last updated on FEBRUARY 27, 2019

Applies to:

Goal

While creating index with function columns in Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 , you may get an error like:

ORA-01793: maximum number of index columns is 32

The error can be reproduce with example below:-

CREATE TABLE TBL_1
(
COL01 NUMBER,COL02 NUMBER,COL03 NUMBER,COL04 NUMBER,COL05 NUMBER,COL06 NUMBER,COL07 NUMBER,COL08 NUMBER,COL09 NUMBER,COL10 NUMBER,
COL11 NUMBER,COL12 NUMBER,COL13 NUMBER,COL14 NUMBER,COL15 NUMBER,COL16 NUMBER,COL17 NUMBER,COL18 NUMBER,COL19 NUMBER,COL20 NUMBER,
COL21 NUMBER,COL22 NUMBER,COL23 NUMBER,COL24 NUMBER,COL25 NUMBER,COL26 NUMBER,COL27 NUMBER,COL28 NUMBER,COL29 NUMBER,COL30 NUMBER,
COL31 NUMBER,COL32 NUMBER,COL33 NUMBER,COL34 NUMBER,COL35 NUMBER,COL36 NUMBER,COL37 NUMBER,COL38 NUMBER,COL39 NUMBER,COL40 NUMBER,
COL41 NUMBER,COL42 NUMBER,COL43 NUMBER,COL44 NUMBER,COL45 NUMBER,COL46 NUMBER,COL47 NUMBER,COL48 NUMBER,COL49 NUMBER,COL50 NUMBER
);

CREATE INDEX IDX_1 ON TBL_1
(
CASE WHEN (COL01 IS NOT NULL AND COL02=1) THEN COL01 ELSE NULL END,
CASE WHEN (COL01 IS NOT NULL AND COL02=1) THEN COL02 ELSE NULL END,
CASE WHEN (COL01 IS NOT NULL AND COL02=1) THEN COL03 ELSE NULL END,
CASE WHEN (COL01 IS NOT NULL AND COL02=1) THEN COL04 ELSE NULL END,
CASE WHEN (COL01 IS NOT NULL AND COL02=1) THEN COL05 ELSE NULL END,
CASE WHEN (COL01 IS NOT NULL AND COL02=1) THEN COL06 ELSE NULL END,
CASE WHEN (COL01 IS NOT NULL AND COL02=1) THEN COL07 ELSE NULL END,
CASE WHEN (COL01 IS NOT NULL AND COL02=1) THEN COL08 ELSE NULL END,
CASE WHEN (COL01 IS NOT NULL AND COL02=1) THEN COL09 ELSE NULL END,
CASE WHEN (COL01 IS NOT NULL AND COL02=1) THEN COL10 ELSE NULL END,
CASE WHEN (COL01 IS NOT NULL AND COL02=1) THEN COL11 ELSE NULL END,
CASE WHEN (COL01 IS NOT NULL AND COL02=1) THEN COL12 ELSE NULL END,
CASE WHEN (COL01 IS NOT NULL AND COL02=1) THEN COL13 ELSE NULL END

);

-- Hit error "ORA-01793: maximum number of index columns is 32" 

Note that in each case we are only referencing to columns, and the "THEN" clause has a different result.

So how are we calculating the number of referenced columns ?

Solution

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.