Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: View vs Underlying Query Performance Question

Re: View vs Underlying Query Performance Question

From: Soup <kjmerc5_at_gmail.com>
Date: 8 Sep 2006 09:19:42 -0700
Message-ID: <1157732382.527380.295430@b28g2000cwb.googlegroups.com>


The Oracle version number is 9.2.0.3.0 and the userid is a string. Here is the explain plan for the view query.


| Id  | Operation                           |  Name

| Rows | Bytes | Cost |

|   0 | SELECT STATEMENT                    |

| 1830 | 541K| 60590 |
|* 1 | VIEW | GPC_TP_QUERY_VW
| 1830 | 541K| 60590 |
| 2 | COUNT |
| | | |
| 3 | CONCATENATION |
| | | |
|* 4 | HASH JOIN |
| 1 | 165 | 34 |
| 5 | TABLE ACCESS FULL | GPC_BUSINESS
| 1 | 14 | 1 |
|* 6 | HASH JOIN |
| 1 | 119 | 29 |
|* 7 | HASH JOIN |
| 1 | 151 | 33 |
| 8 | TABLE ACCESS FULL | GPC_PRODUCT_LBM
| 398 | 12736 | 4 |
|* 9 | HASH JOIN |
| 1 | 228 | 37 |
|* 10 | HASH JOIN |
| 8 | 448 | 5 |
| 11 | TABLE ACCESS FULL | GPC_BATCH_TEMP
| 4 | 168 | 2 |
| 12 | TABLE ACCESS FULL | GPC_ENTITY_XREF
| 1013 | 14182 | 2 |
|* 13 | TABLE ACCESS FULL | GPC_ENTITY_COST
| 1 | 63 | 3 |
| 14 | TABLE ACCESS FULL | GPC_PRODUCT
| 1 | 63 | 3 |
| 15 | NESTED LOOPS |
| 1 | 165 | 34 |
| 16 | NESTED LOOPS |
| 1 | 228 | 37 |
| 17 | NESTED LOOPS |
| 1 | 151 | 33 |
| 18 | NESTED LOOPS |
| 1013 | 14182 | |
| 19 | NESTED LOOPS |
| 1 | 119 | 29 |
| 20 | TABLE ACCESS FULL | GPC_BATCH_TEMP
| 4 | 168 | 2 |
| 21 | TABLE ACCESS BY INDEX ROWID| GPC_PRODUCT
| 1 | 63 | 3 |
|* 22 | INDEX UNIQUE SCAN | GPC_PRODUCT_PK
| 1 | | 2 |
| 23 | TABLE ACCESS BY INDEX ROWID | GPC_ENTITY_XREF
| 1013 | 14182 | 2 |
|* 24 | INDEX RANGE SCAN | GPC_COST_ENTITY_PRD_EI_IDX | | | | | 25 | TABLE ACCESS BY INDEX ROWID | GPC_PRODUCT_LBM
| 398 | 12736 | 4 |
|* 26 | INDEX RANGE SCAN | GPC_PRODUCT_LBM_PK
| 1 | | 2 |
|* 27 | TABLE ACCESS BY INDEX ROWID | GPC_ENTITY_COST
| 1 | 63 | 3 |
|* 28 | INDEX UNIQUE SCAN | GPC_ENTITY_COST_PK
| 1 | | 2 |
|* 29 | TABLE ACCESS BY INDEX ROWID | GPC_BUSINESS
| 1 | 14 | 1 |
|* 30 | INDEX UNIQUE SCAN | GPC_BUSINESS_PK
| 1 | | |
| 31 | NESTED LOOPS |
| 1 | 228 | 37 |
| 32 | NESTED LOOPS |
| 1 | 165 | 34 |
| 33 | NESTED LOOPS |
| 1 | 151 | 33 |
| 34 | NESTED LOOPS |
| 1 | 119 | 29 |
|* 35 | HASH JOIN |
| 8 | 448 | 5 |
| 36 | TABLE ACCESS FULL | GPC_BATCH_TEMP
| 4 | 168 | 2 |
| 37 | TABLE ACCESS FULL | GPC_ENTITY_XREF
| 1013 | 14182 | 2 |
|* 38 | TABLE ACCESS BY INDEX ROWID | GPC_PRODUCT
| 1 | 63 | 3 |
|* 39 | INDEX RANGE SCAN | GPC_PRODUCT_PRODNO_IDX
| 1 | | 2 |
| 40 | TABLE ACCESS BY INDEX ROWID | GPC_PRODUCT_LBM
| 398 | 12736 | 4 |
|* 41 | INDEX RANGE SCAN | GPC_PRODUCT_LBM_PK
| 1 | | 2 |
|* 42 | TABLE ACCESS BY INDEX ROWID | GPC_BUSINESS
| 1 | 14 | 1 |
|* 43 | INDEX UNIQUE SCAN | GPC_BUSINESS_PK
| 1 | | |
|* 44 | TABLE ACCESS BY INDEX ROWID | GPC_ENTITY_COST
| 1 | 63 | 3 |
|* 45 | INDEX UNIQUE SCAN | GPC_ENTITY_COST_PK
| 1 | | 2 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):


   1 - filter("GPC_TP_QUERY_VW"."USERID"='campbk2')

   4 - access("BUS"."FIN_BUSINESS_ID"=NVL("TMP"."BUSINESS",' ') AND

              "PRODLBM"."FIN_PROD_ID"="BUS"."FIN_PROD_ID" AND "PRODLBM"."FISCAL_YEAR_ID"="BUS"."FISCAL_YEAR_ID")    6 - access("PROD"."GPC_PROD_ID"="PRODLBM"."GPC_PROD_ID")

   7 - access("PRODLBM"."FISCAL_YEAR_ID"=TO_NUMBER("TMP"."FISCALYEAR") 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")
   9 - access("ETYCST"."ENTITY_ID"="X"."COST_ENTITY_ID" AND

              "ETYCST"."FISCAL_YEAR_ID"="X"."FISCAL_YEAR_ID")

  10 - access("X"."ENTITY_ID"="TMP"."ENTITYID")

  13 - filter("ETYCST"."END_FM"=12)

  22 - access("PROD"."GPC_PROD_ID"=NVL("TMP"."UPN",' '))

  24 - access("X"."ENTITY_ID"="TMP"."ENTITYID")

  26 - access("PROD"."GPC_PROD_ID"="PRODLBM"."GPC_PROD_ID" AND

              "PRODLBM"."FISCAL_YEAR_ID"=TO_NUMBER("TMP"."FISCALYEAR"))   27 - filter("PRODLBM"."END_FM"="ETYCST"."LBM_END_FM")

  28 - access("PRODLBM"."GPC_PROD_ID"="ETYCST"."GPC_PROD_ID" AND

              "ETYCST"."ENTITY_ID"="X"."COST_ENTITY_ID" AND "PRODLBM"."FISCAL_YEAR_ID"="ETYCST"."FISCAL_YEAR_ID"               AND "ETYCST"."END_FM"=12)        filter("ETYCST"."FISCAL_YEAR_ID"="X"."FISCAL_YEAR_ID")

  29 - filter(LNNVL("BUS"."FIN_BUSINESS_ID"=NVL("TMP"."BUSINESS",' ')))

  30 - access("PRODLBM"."FIN_PROD_ID"="BUS"."FIN_PROD_ID" AND

              "PRODLBM"."FISCAL_YEAR_ID"="BUS"."FISCAL_YEAR_ID")   35 - access("X"."ENTITY_ID"="TMP"."ENTITYID")

  38 - filter(LNNVL("PROD"."GPC_PROD_ID"=NVL("TMP"."UPN",' ')))

  39 - access("PROD"."PRODNO"=NVL("TMP"."CATALOG",' '))

  41 - access("PROD"."GPC_PROD_ID"="PRODLBM"."GPC_PROD_ID" AND

              "PRODLBM"."FISCAL_YEAR_ID"=TO_NUMBER("TMP"."FISCALYEAR"))   42 - filter(LNNVL("BUS"."FIN_BUSINESS_ID"=NVL("TMP"."BUSINESS",' ')))

  43 - access("PRODLBM"."FIN_PROD_ID"="BUS"."FIN_PROD_ID" AND

              "PRODLBM"."FISCAL_YEAR_ID"="BUS"."FISCAL_YEAR_ID")   44 - filter("PRODLBM"."END_FM"="ETYCST"."LBM_END_FM")

  45 - access("PRODLBM"."GPC_PROD_ID"="ETYCST"."GPC_PROD_ID" AND

              "ETYCST"."ENTITY_ID"="X"."COST_ENTITY_ID" AND "PRODLBM"."FISCAL_YEAR_ID"="ETYCST"."FISCAL_YEAR_ID"               AND "ETYCST"."END_FM"=12)        filter("ETYCST"."FISCAL_YEAR_ID"="X"."FISCAL_YEAR_ID")

Note: cpu costing is off

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?
> --
> Daniel Morgan
> University of Washington
> Puget Sound Oracle Users Group
Received on Fri Sep 08 2006 - 11:19:42 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US