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