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;
Martin T. wrote:
> DA Morgan wrote:
> > Soup wrote:
> > > I have a query that runs fast (second or less). I put this query into a
> > > view minus one condition (userid = '<the users userid>'). When I query
> > > the view and add the condition, the query (see 1 below) is slow
> > > (minutes). I tried running the view without the condition (see 2 below)
> > > and found that it ran in about the same amount of time and the explain
> > > plans were the same. The only difference was the results.
> > >
> > > 1) SELECT * FROM myview WHERE userid = '<the users userid>'
> > > 2) SELECT * FROM myview
> > >
> > > Is this normal behavior? Doesn't Oracle include my condition before
> > > running the view's query? I assumed that since a view is a stored query
> > > that it would include my condition before processing the request. That
> > > isn't what is happening here. Also, I have used this approach before
> > > and haven't had an issue.
> > >
> > > How should Oracle handle this situation? Will it always run the stored
> > > query first before narrowing the results by the conditions added
> > > outside of the view? If it should be including my condition what could
> > > prevent it from happening?
> > >
> > > Thanks!
> >
> > Oracle version number?
> > It would have been very helpful if you had included the EXPLAIN PLANs
> > created with DBMS_XPLAN.
> > Is USERID a string or a number?
> > --
>
> What I really would like to see is the underlying _query_ ... well the
> Oracle version number is always nice also ;)
>
> cheers,
> Martin
Received on Fri Sep 08 2006 - 11:22:17 CDT
