Partition the State History table for better performance

Opportunity Detect relies on the State History table to access customer data that is saved from previous runs. Opportunity Detect reads and updates these records frequently during execution of trigger systems. To optimize this access and to scale with multiple engines, you can partition the State History table.

See your database documentation for complete details on managing partitions. For Oracle, partitioning is an extra cost option, for Enterprise Edition only.

The number of partitions to use depends on your usage patterns. General testing has shown that one partition per engine is a good basic guideline.

Oracle example

For Oracle, use a hash partitioning clause on the audienceId field.

The following example creates 16 partitions.

CREATE TABLE State
(
  id                     NUMBER NOT NULL ,
  audienceId      NVARCHAR2(60) NOT NULL ,
  data                 BLOB NOT NULL ,
  audienceType  NVARCHAR2(36) NOT NULL ,
  firingTime        TIMESTAMP NOT NULL ,
  runId                NUMBER NOT NULL ,
  cycleTime        TIMESTAMP NOT NULL ,
  CONSTRAINT State_PK PRIMARY KEY  (id) 
)       
PARTITION BY HASH (audienceId)
PARTITIONS 16

DB2 example

DB2 does not provide the hash partition by default, so you must do the following to ensure that records are uniformly allocated among partitions on a round robin basis.

Change the ID generator to create a maxvalue range based on the number of partitions that you intend to use.
Recycle these generated IDs.
Use range partitioning with the ID column.

The following example creates five partitions.

CREATE TABLE "OPDETECT"."STATE"  (
   "ID" INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (  
      START WITH +1  
      INCREMENT BY +1  
      MINVALUE +1  
      MAXVALUE +5  
      CYCLE  
      CACHE 40  
      NO ORDER ) , 
      "AUDIENCEID" VARCHAR(60) NOT NULL , 
      "DATA" BLOB(10485760) NOT LOGGED NOT COMPACT NOT NULL , 
      "AUDIENCETYPE" VARCHAR(36) NOT NULL , 
      "FIRINGTIME" TIMESTAMP NOT NULL , 
      "RUNID" INTEGER NOT NULL , 
      "CYCLETIME" TIMESTAMP NOT NULL )   
   PARTITION BY RANGE (ID) 
      (
      PARTITION state_000 starting from (minvalue) exclusive ending at (1) 
         EXCLUSIVE ,
      PARTITION state_001 starting from (1) inclusive ending at (2) EXCLUSIVE ,
      PARTITION state_002 starting from (2) inclusive ending at (3) EXCLUSIVE ,
      PARTITION state_003 starting from (3) inclusive ending at (4) EXCLUSIVE ,
      PARTITION state_004 starting from (4) inclusive ending at (maxvalue) 
         EXCLUSIVE 
      );