ORA-25193
Tried to rebuild some indexes with COMPRESS option, it threw ORA-25193.
Let's see some cases.
Primary Key
SQL> alter index hr.emp_emp_id_pk rebuild compress;
alter index hr.emp_emp_id_pk rebuild compress
*
ERROR at line 1:
ORA-25193: cannot use COMPRESS option for a single column key
Note that, primary keys are typically unique.
Unique Key
An unique key index has the same problem.
SQL> alter index hr.emp_email_uk rebuild compress;
alter index hr.emp_email_uk rebuild compress
*
ERROR at line 1:
ORA-25193: cannot use COMPRESS option for a single column key
The error message indicates that it's a single column problem. How about non-unique key?
Non-unique Key
We try another non-unique, single-column one.
SQL> alter index hr.emp_manager_ix rebuild compress;
Index altered.
OK, the non-unique key passed. So ORA-25193 is basically related to uniqueness of a key, not necessarily single column.
This is because specifying only COMPRESS to ALTER INDEX implies a prefix (basic) compression on column 1 which can only apply on non-unique key(s).
ORA-25193 means that prefix compress has no effects on unique keys(s), so it prevents you from applying it on such keys of indexes. It's irrelevant to single column.
Solution
Whenever prefix compress cannot be used, we can use advanced compression.
SQL> alter index hr.emp_emp_id_pk rebuild compress advanced;
Index altered.
SQL> alter index hr.emp_email_uk 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.