My Oracle Support Banner

Global Search View's Claims And Authorizations Tuning (Doc ID 2694489.1)

Last updated on JULY 28, 2020

Applies to:

Oracle Health Insurance Claims Adjudication - Version 3.19.3.0.0 and later
Information in this document applies to any platform.

Goal

Global search view is taking much time in Preprod

The DBA suggested some query tuning and indexes to improve the performance of the global search.

The SGA was also increased from 20GB to 36GB.

Please find below indexes suggested by our DBA and let us know if we can proceed with the below.


CREATE INDEX "OHI_CLAIMS_OWNER"."IDX_CLA_CLAIMS_DN26" ON "OHI_CLAIMS_OWNER"."CTR_CLAIMS" ("DYN_CHAR_026", "REVERSAL_INDICATOR");

CREATE INDEX "OHI_CLAIMS_OWNER"."IDX_CLA_CLAIMS_DN21" ON "OHI_CLAIMS_OWNER"."CLA_CLAIMS" ("DYN_CHAR_021");


CREATE INDEX "OHI_CLAIMS_OWNER"."IDX_CTR_CLAIMS_PR_ST" ON "OHI_CLAIMS_OWNER"."CTR_CLAIMS" ("PROCESS_TYPE", "REVERSAL_INDICATOR", "CODE")

CREATE INDEX "OHI_CLAIMS_OWNER"."IDX_CTR_CLAIMS_01" ON "OHI_CLAIMS_OWNER"."CTR_CLAIMS" ("SERVICED_ENTITY_CODE")
  
CREATE INDEX "OHI_CLAIMS_OWNER"."IDX_CLA_CLAIMS_PR_ST" ON "OHI_CLAIMS_OWNER"."CLA_CLAIMS" ("PROCESS_TYPE", "STATUS")
  
CREATE INDEX "OHI_CLAIMS_OWNER"."IDX_CLA_CLAIMS_DATE" ON "OHI_CLAIMS_OWNER"."CLA_CLAIMS" ("RECEIPT_DATE")


CREATE INDEX "OHI_CLAIMS_OWNER"."IDX_CTR_CLAIMS_DN_RV" ON "OHI_CLAIMS_OWNER"."CTR_CLAIMS" ("DYN_CHAR_011", "REVERSAL_INDICATOR")


CREATE INDEX "OHI_CLAIMS_OWNER"."IDX_CTR_CLAIMS_DATE" ON "OHI_CLAIMS_OWNER"."CTR_CLAIMS" ("RECEIPT_DATE", "REVERSAL_INDICATOR", "VERSION")


CREATE INDEX "OHI_CLAIMS_OWNER"."IDX_CTR_CLAIMS_DN19" ON "OHI_CLAIMS_OWNER"."CTR_CLAIMS" ("DYN_CHAR_019")


CREATE INDEX "OHI_CLAIMS_OWNER"."IDX_CLA_CLAIMS_DN19" ON "OHI_CLAIMS_OWNER"."CLA_CLAIMS" ("DYN_CHAR_019")


CREATE INDEX "OHI_CLAIMS_OWNER"."IDX_CTR_CLAIMS_001" ON "OHI_CLAIMS_OWNER"."CTR_CLAIMS" ("RECEIPT_DATE", "REVERSAL_INDICATOR", "DYN_CHAR_026")

CREATE INDEX "OHI_CLAIMS_OWNER"."IDX_CLA_CLAIMS_001" ON "OHI_CLAIMS_OWNER"."CLA_CLAIMS" ("RECEIPT_DATE", "DYN_CHAR_021")

create index OHI_CLAIMS_OWNER.idx_PROV_01 on OHI_CLAIMS_OWNER.REL_PROVIDERS (CODE);
 

Solution

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Goal
Solution


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.