Re: View vs Underlying Query Performance Question

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;
>
>

,but personally I don't really mind.
,that top posting is evil
Others may tell you though

I think your problem is this: (...) rownum id (...) When you select the rownum in the view, you prevent Oracle from pushing any predicate into the view. (Rownum is only a pseudo column and numbers your result set from 1 to n -- this numbering would change O. pushed another predicate into the view, hence it doesn't.)

best,
Martin Received on Fri Sep 08 2006 - 13:17:33 CDT