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 (45) times.
Ensure fields index exist when Joining Who Moved My Cheese?