Skip to Main Content
  • Questions
  • How to create partition in a functional index in oracle?

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, aravind.

Asked: January 12, 2020 - 1:58 pm UTC

Last updated: January 13, 2020 - 3:27 am UTC

Version: oracle

Viewed 1000+ times

You Asked

im trying to create a partition on a functional index in oracle , but it is failing with the below error .

Error : ORA-30555: global index partitioning key is an expression

Syntax: CREATE INDEX TMP_ARA_I9 ON TMP_ARA ( UPPER(JOB_TITLE) ASC, UPPER(COMPANY_NAME) ASC ) global PARTITION BY HASH ( UPPER(JOB_TITLE) , UPPER(COMPANY_NAME) ) PARTITIONS 4;

and Connor said...

As the error suggest, you cannot have an expression the definition

SQL> create table t as select * from dba_objects where object_id is not null;

Table created.

SQL> create index ix on t ( object_id ) global partition by hash (object_id ) partitions 2;

Index created.

SQL>
SQL> drop index ix;

Index dropped.

SQL> create index ix on t ( object_id*2 ) global partition by hash (object_id*2 ) partitions 2;
create index ix on t ( object_id*2 ) global partition by hash (object_id*2 ) partitions 2
                                                                           *
ERROR at line 1:
ORA-30555: global index partitioning key is an expression




But you can work around that but by using a virtual column...

SQL> alter table t add fbi_col generated always as (object_id*2);

Table altered.

SQL> create index ix on t ( fbi_col ) global partition by hash (fbi_col ) partitions 2;

Index created.


Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thanks for the quick response , I will check and get back to you

Aravind, January 13, 2020 - 3:57 am UTC

Thanks for the quick response , I will check and get back to you

More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.

VLDB

Documentation set on VLDB and Partitioning.