Re: View vs Underlying Query Performance Question

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: 8 Sep 2006 15:42:56 -0700
Message-ID: <1157755376.068981.55290@e3g2000cwe.googlegroups.com>

Soup wrote:
> SELECT
> X.ENTITY_ID entity,
> PROD.PRODNO Catalog,
> PRODLBM.GPC_PROD_ID UPN,
> BUS.FIN_BUSINESS_ID BUSINESS,
> PRODLBM.FISCAL_YEAR_ID FY,
> ETYCST.TRANSFER_PRICE TP,
> ETYCST.MANUAL_FLAG OVER_RIDE,
> ETYCST.NOTES NOTES,
> ETYCST.CHANGE_USERID CHANGE_USERID,
> ETYCST.CHANGE_TIMESTAMP CHANGE_TIMESTAMP,
> PRODLBM.LBM_PRICE LBM,
> ETYCST.MARKUP_PERCENT MU,
> PROD.PROD_DESC PROD_DESC,
> PROD.UOM_ID UOM,
> PROD.BASE_UOM_ID BASE_UOM,
> PROD.UOM_QUANTITY QTY,
> PRODLBM.FIN_PROD_ID GFS_CODE,
> ETYCST.ORIGIN_ENTITY_ID ORIGIN,
> TMP.BATCHNUM,
> TMP.USERID,
> rownum id
> FROM GPC_PRODUCT PROD
> JOIN GPC_PRODUCT_LBM PRODLBM ON (PROD.GPC_PROD_ID =
> PRODLBM.GPC_PROD_ID)
> JOIN GPC_BUSINESS BUS ON (PRODLBM.FIN_PROD_ID = BUS.FIN_PROD_ID AND
> PRODLBM.FISCAL_YEAR_ID = BUS.FISCAL_YEAR_ID)
> JOIN GPC_ENTITY_COST ETYCST ON (PRODLBM.GPC_PROD_ID =
> ETYCST.GPC_PROD_ID AND PRODLBM.FISCAL_YEAR_ID = ETYCST.FISCAL_YEAR_ID
> AND PRODLBM.END_FM = ETYCST.LBM_END_FM)
> JOIN GPC_ENTITY_XREF X ON (ETYCST.ENTITY_ID = X.COST_ENTITY_ID AND
> ETYCST.FISCAL_YEAR_ID = X.FISCAL_YEAR_ID)
> JOIN GPC_BATCH_TEMP TMP ON (((PROD.PRODNO = NVL(TMP.CATALOG, ' ')) OR
> (PROD.GPC_PROD_ID = NVL(TMP.UPN, ' ')) OR (BUS.FIN_BUSINESS_ID =
> NVL(TMP.BUSINESS, ' '))) AND PRODLBM.FISCAL_YEAR_ID = TMP.FISCALYEAR
> AND X.ENTITY_ID = TMP.ENTITYID)
> WHERE ETYCST.END_FM = 12;
Just curious, does the following SQL statement execute without error? If so, does it provide a better execution plan, or faster access time?

SELECT
  X.ENTITY_ID entity,
  PROD.PRODNO Catalog,
  PRODLBM.GPC_PROD_ID UPN,
  BUS.FIN_BUSINESS_ID BUSINESS,
  PRODLBM.FISCAL_YEAR_ID FY,

  ETYCST.TRANSFER_PRICE TP,
  ETYCST.MANUAL_FLAG OVER_RIDE,
  ETYCST.NOTES NOTES,
  ETYCST.CHANGE_USERID CHANGE_USERID,
  ETYCST.CHANGE_TIMESTAMP CHANGE_TIMESTAMP,

  PRODLBM.LBM_PRICE LBM,
  ETYCST.MARKUP_PERCENT MU,

  PROD.PROD_DESC PROD_DESC,
  PROD.UOM_ID UOM,
  PROD.BASE_UOM_ID BASE_UOM,
  PROD.UOM_QUANTITY QTY,

  PRODLBM.FIN_PROD_ID GFS_CODE,
  ETYCST.ORIGIN_ENTITY_ID ORIGIN,
  TMP.BATCHNUM,
  TMP.USERID,
  ROWNUM id
FROM

  GPC_PRODUCT PROD,
  GPC_PRODUCT_LBM PRODLBM,
  GPC_BUSINESS BUS,
  GPC_ENTITY_COST ETYCST,
  GPC_ENTITY_XREF X,
  GPC_BATCH_TEMP TMP

WHERE
  PROD.GPC_PROD_ID=PRODLBM.GPC_PROD_ID

  AND PRODLBM.FIN_PROD_ID=BUS.FIN_PROD_ID
  AND PRODLBM.FISCAL_YEAR_ID=BUS.FISCAL_YEAR_ID
  AND PRODLBM.GPC_PROD_ID=ETYCST.GPC_PROD_ID
  AND PRODLBM.FISCAL_YEAR_ID=ETYCST.FISCAL_YEAR_ID
  AND PRODLBM.END_FM=ETYCST.LBM_END_FM
  AND PRODLBM.FIN_PROD_ID=BUS.FIN_PROD_ID
  AND PRODLBM.FISCAL_YEAR_ID=BUS.FISCAL_YEAR_ID
  AND PRODLBM.GPC_PROD_ID=ETYCST.GPC_PROD_ID
  AND PRODLBM.FISCAL_YEAR_ID=ETYCST.FISCAL_YEAR_ID

  AND ETYCST.ENTITY_ID=X.COST_ENTITY_ID
  AND ETYCST.FISCAL_YEAR_ID=X.FISCAL_YEAR_ID   AND PRODLBM.FISCAL_YEAR_ID = TMP.FISCALYEAR   AND (
    (PROD.PRODNO=NVL(TMP.CATALOG, ' '))
    OR (PROD.GPC_PROD_ID=NVL(TMP.UPN, ' '))     OR (BUS.FIN_BUSINESS_ID=NVL(TMP.BUSINESS, ' ')))   AND X.ENTITY_ID=TMP.ENTITYID
  AND ETYCST.END_FM=12; If it does work, try it as your view definition. Is it any faster than your original SQL statement? Try it as an inline view, does it execute faster than as a static view?

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Fri Sep 08 2006 - 17:42:56 CDT