Custom Object Index missing in datawarehouse

book

Article ID: 223019

calendar_today

Updated On:

Products

Clarity PPM On Premise Clarity PPM SaaS

Issue/Introduction

SUMMARY: The custom object created in clarity schema has index and one the object is included in dataware house , the table created in Dataware house has no index. This caused slowness for reporting using customer object tables in dataware house 

STEPS TO REPRODUCE: 

  1. Login to clarity classic ux using administrator rights
  2. Navigate to Administration --> Studio --> Objects
  3. Create a customer object (z_custom_testing) and Include in Data Warehouse by checking the flag at Object Properties Page 
  4. Create few attributes and include those also in the dataware house 
  5. Login to clarity database and check the table definition(ODF_CA_Z_CUSTOM_TESTING) and there will be below index  

  CREATE UNIQUE INDEX "Clarity"."ODF_CA_Z_CUSTOM_TESTING_PK" ON "Clarity"."ODF_CA_Z_CUSTOM_TESTING" ("ID") 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  TABLESPACE "Clarity" ;
  CREATE UNIQUE INDEX "Clarity"."ODF_CA_Z_CUSTOM_TESTING_U1" ON "Clarity"."ODF_CA_Z_CUSTOM_TESTING" ("CODE") 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  TABLESPACE "Clarity" ;
  CREATE UNIQUE INDEX "Clarity"."ODF_CA_Z_CUSTOM_TESTING_U2" ON "Clarity"."ODF_CA_Z_CUSTOM_TESTING" ("ID", "PARTITION_CODE") 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  TABLESPACE "Clarity" ;
  CREATE INDEX "Clarity"."ODF_CA_Z_CUSTOM_TESTING_N1" ON "Clarity"."ODF_CA_Z_CUSTOM_TESTING" (NLS_UPPER("NAME")) 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  TABLESPACE "Clarity" ;

    6. Run the Load Dataware house full load and ensure it complete successfully 

   7. Login to dataware house database and check the table definition(DWH_ODF_Z_CUSTOM_TESTING) and there will no index and below DDL can be found

CREATE TABLE "DWH"."DWH_ODF_Z_CUSTOM_TESTING" 
   ( "CLARITY_UPDATED_DATE" DATE, 
 "DW_UPDATED_DATE" DATE, 
 "ID" VARCHAR2(90), 
 "NAME" VARCHAR2(240), 
 "CLARITY_CREATED_DATE" DATE, 
 "CLARITY_Z_CUSTOM_TESTING_KEY" NUMBER(30,0), 
 "Z_CUSTOM_TESTING_KEY" NUMBER(30,0), 
 "Z_CUSTOM_ATTRIBUTE1" VARCHAR2(60)
   ) SEGMENT CREATION DEFERRED 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  TABLESPACE "DWH" ;

Expected Results: In order to have optimized database index are required and expectation is to have index 

Actual Results: No Index are created at the custom objects tables in data warehouse 

Cause

This is logged as defect DE62392 and in review with engineering 

Environment

Release : 15.8.1, 15.9.x

Component : CLARITY DATA WAREHOUSE

Resolution

Workaround: Till the defect is fixed please create custom index on object key 

CREATE UNIQUE INDEX "DWH_ODF_Z_Z_CUSTOM_TESTING_Z_CUSTOM_TESTING_KEY_IDX" ON "DWH_ODF_Z_CUSTOM_TESTING" ("Z_CUSTOM_TESTING_KEY") ;

Note: Since this is custom index, please ensure to drop prior to any upgrade including patches. And for any reason if blank out of box Data warehouse schema is imported, please recreate the custom index again.