CHROMIUM

Welcome to CHROMIUM's very own blog!

Select Composite key colums from same table in VIEWs

While creating views keep it mind that columns of composite key should be select from a same table otherwise views will perform very slow. Recently while doing WFS GAC optimization we found that the root cause of slow problem was a view in which a composite key columns was selected from different tables. After updating views by selecting all composite columns from same table the views performance increased and a process which was using the view it execution time reduced from 11 seconds to within a seconds. Please see below example of creating view for further understanding.

 

Before change

===================

CREATE OR REPLACE FORCE VIEW DEALER_REQUEST_VIEW

(

COMPANY_CODE,

CREDIT_REQ_NO,

DEALER_CODE,

ASSET_GROUP,

ASSET_CATEGORY,

ASSET_CONDITION,

MAKE,

DLR_AGENCY_TYPE,

EFFECTIVE_DATE,

APPROVED_LIMIT,

CREDIT_VAR_PC,

DCR_STATUS,

CR_LIMIT,

DIST_CODE,

LIMIT_TYPE,

EXPIRY_DATE,

REVIEW_DATE

)

AS

SELECT                   /*+ index(DCR_REQ_Limits, IDX_DCRREQLMT_CRRENO )*/

DCR_REQ_Limits.Company_Code,

Dealer_Credit_Request.Credit_Req_No,

Dealer_Credit_Request.Dealer_Code,

DCR_REQ_Limits.Asset_Group,

DCR_REQ_Limits.Asset_Category,

DCR_REQ_Limits.Asset_Condition,

DCR_REQ_Limits.Make,

Dealer_Credit_Request.Dlr_Agency_Type,

Dealer_Credit_Request.Effective_date,

DCR_REQ_Limits.Approved_Limit,

DCR_REQ_Limits.Credit_Var_PC,

DCR_REQ_Limits.Dcr_Status,

DCR_REQ_Limits.CR_Limit,

Dealer_Credit_Request.Dist_Code,

Dealer_Credit_Request.Limit_type,

Dealer_Credit_Request.Expiry_date,

Review_Date

FROM    DCR_REQ_Limits

INNER JOIN

Dealer_Credit_Request

ON DCR_REQ_Limits.Company_Code = Dealer_Credit_Request.Company_Code

AND DCR_REQ_Limits.Credit_Req_No =

Dealer_Credit_Request.Credit_Req_No;

 

After Change

==========

CREATE OR REPLACE FORCE VIEW DEALER_REQUEST_VIEW

(

COMPANY_CODE,

CREDIT_REQ_NO,

DEALER_CODE,

ASSET_GROUP,

ASSET_CATEGORY,

ASSET_CONDITION,

MAKE,

DLR_AGENCY_TYPE,

EFFECTIVE_DATE,

APPROVED_LIMIT,

CREDIT_VAR_PC,

DCR_STATUS,

CR_LIMIT,

DIST_CODE,

LIMIT_TYPE,

EXPIRY_DATE,

REVIEW_DATE

)

AS

SELECT                   /*+ index(DCR_REQ_Limits, IDX_DCRREQLMT_CRRENO )*/

Dealer_Credit_Request.Company_Code,

Dealer_Credit_Request.Credit_Req_No,

Dealer_Credit_Request.Dealer_Code,

DCR_REQ_Limits.Asset_Group,

DCR_REQ_Limits.Asset_Category,

DCR_REQ_Limits.Asset_Condition,

DCR_REQ_Limits.Make,

Dealer_Credit_Request.Dlr_Agency_Type,

Dealer_Credit_Request.Effective_date,

DCR_REQ_Limits.Approved_Limit,

DCR_REQ_Limits.Credit_Var_PC,

DCR_REQ_Limits.Dcr_Status,

DCR_REQ_Limits.CR_Limit,

Dealer_Credit_Request.Dist_Code,

Dealer_Credit_Request.Limit_type,

Dealer_Credit_Request.Expiry_date,

Review_Date

FROM    DCR_REQ_Limits

INNER JOIN

Dealer_Credit_Request

ON DCR_REQ_Limits.Company_Code = Dealer_Credit_Request.Company_Code

AND DCR_REQ_Limits.Credit_Req_No =

Dealer_Credit_Request.Credit_Req_No;

 

This post was viewed (44) times.

Leave a Reply

Your email address will not be published. Required fields are marked *