ORA-25194
When we tried to compress a composite unique key, we saw ORA-25194. Let's see the case.
SQL> create unique index hr.emp_emp_id_email_ix on hr.employees (employee_id, email);
Index created.
SQL> alter index hr.emp_emp_id_email_ix rebuild compress 2;
alter index hr.emp_emp_id_email_ix rebuild compress 2
*
ERROR at line 1:
ORA-25194: invalid COMPRESS prefix length value
COMPRESS 2 means that you want to apply prefix compression on the first and the second column of the index. In fact, the first 2 columns is already unique and has no duplicate.
ORA-25194 means that the key you want to apply prefix compression in the statement is an unique one, which cannot be done on such keys.
Solutions
To solve ORA-25194, we have some ways to do it.
Shorter Prefix
We can choose a shorter prefix number to make it succeed.
SQL> alter index hr.emp_emp_id_email_ix rebuild compress;
Index altered.
Without specifying any number in the statement means that you want to apply prefix compression only on the first column of the index.
Although we know the first column EMPLOYEE_ID has an unique constraint in another index and prefix compression has no use on it, we did it.
Advanced Compression
Whenever prefix compress cannot be used, we can use advanced compression.
SQL> alter index hr.emp_emp_id_email_ix rebuild compress advanced;
Index altered.
Please note that, the feature of basic compression is included in Enterprise Edition, whereas Oracle Advanced Compression is an Oracle Database option which needs purchase in addition to Enterprise Edition.
Non-unique Key
If uniqueness is not necessary, you can re-create it as a non-unique one, then do it again.
SQL> drop index hr.emp_emp_id_email_ix;
Index dropped.
SQL> create index hr.emp_emp_id_email_ix on hr.employees (employee_id, email);
Index created.
SQL> alter index hr.emp_emp_id_email_ix rebuild compress 2;
Index altered.
We solved it.