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

Breadcrumb

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

Question and Answer

Connor McDonald

Thanks for the question, aravind.

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

Answered by: Connor McDonald - Last updated: January 13, 2020 - 3:27 am UTC

Category: SQL - Version: oracle

Viewed 100+ times

Whilst you are here, check out some content from the AskTom team: Choose your names wisely

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 we 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.


and you rated our response

  (1 rating)

Reviews

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

January 13, 2020 - 3:57 am UTC

Reviewer: Aravind from India

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.