ALERT: Some images may not load properly within the Knowledge Base Article. If you see a broken image, please right-click and select 'Open image in a new tab'. We apologize for this inconvenience.

Custom Object Index missing in Data Warehouse

book

Article ID: 223019

calendar_today

Updated On:

Products

Clarity PPM On Premise Clarity PPM SaaS

Issue/Introduction

The custom object created in the Clarity schema has an index and the object is included in the Data Warehouse, but the table created in the Data Warehouse has no index. This causes slowness for reporting using custom object tables in the Data Warehouse. 

STEPS TO REPRODUCE: 

  1. Log in to Clarity classic UI (UX) with administrator rights
  2. Navigate to Administration --> Studio --> Objects
  3. Create a custom object (z_custom_testing) and Include it in the Data Warehouse by checking the flag on the Object Properties page 
  4. Create a few attributes and include those also in the Data Warehouse
  5. Log in to the Clarity database and check the table definition (ODF_CA_Z_CUSTOM_TESTING) and there will be the 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 Data Warehouse job full load and ensure it completes successfully 
  7. Log in to the Data Warehouse database and check the table definition (DWH_ODF_Z_CUSTOM_TESTING). There will be no index and the 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 an optimized database, indexes are required and the expectation is to have an index 

Actual Results: No indexes are created in the custom objects tables in the Data Warehouse 

Cause

This is defect DE62392 

Environment

Release : 15.8.1, 15.9.x

Component : CLARITY DATA WAREHOUSE

Resolution

Fixed in 16.0.1 and patched in 16.0.0.1. With the fix, all existing custom objects will have an index 

Workaround: The workaround is applicable to lower version of clarity where its not fixed. From 16.0 patch 1 there is no need for this workaround and application will create index automatically

  • Create a 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 a custom index, ensure to drop prior to any upgrade including patches. If a blank out of box Data Warehouse schema is imported, recreate the custom index again.