Indexing optimization for Oracle High Volume Database

This topic provides a workaround to reduce Enq contention on indexes.

Problem

When Oracle is used as an external runtime database, under high loads, there are issues caused when the primary index is not hash partitioned.

Workaround

Create partitioned indexes.

The following examples are examples of creating a hash partitioned index:

Example 1

ALTER TABLE "CGATEADMIN"."OAUTH20_TOKEN_EXTRA_ATTRIBUTE" DISABLE PRIMARY KEY ; CREATE UNIQUE INDEX "CGATEADMIN"."OAUTH_EXTRA_ATTRI_PK" ON 
"CGATEADMIN"."OAUTH20_TOKEN_EXTRA_ATTRIBUTE" ("STATE_ID", "ATTR_NAME") PCTFREE 30 INITRANS 200 MAXTRANS 255 STORAGE( BUFFER_POOL DEFAULT 
FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "EN_IDX" GLOBAL PARTITION BY HASH ("STATE_ID","ATTR_NAME") PARTITIONS 32 ALTER TABLE 
"CGATEADMIN"."OAUTH20_TOKEN_EXTRA_ATTRIBUTE" ENABLE PRIMARY KEY USING INDEX "CGATEADMIN"."OAUTH_EXTRA_ATTRI_PK";

Example 2

CREATE INDEX "CGATEADMIN"."OAUTH20CACHE_ST" ON "CGATEADMIN"."OAUTH20_TOKEN_CACHE" ("STATE_ID")
PCTFREE 30 INITRANS 100 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "EN_IDX" ;
NEW HASH Partitioned INDEX Definition:
CREATE INDEX "CGATEADMIN"."OAUTH20CACHE_ST" ON "CGATEADMIN"."OAUTH20_TOKEN_CACHE" ("STATE_ID")
PCTFREE 30 INITRANS 200 MAXTRANS 255
TABLESPACE "EN_IDX" GLOBAL PARTITION BY HASH ("STATE_ID") partitions 32 ;

Note: This is required for indexes which are UUID or timestamps, where there is a slight change in data.