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 13:21:41 -0700
Message-ID: <1157746901.675207.151670@b28g2000cwb.googlegroups.com> 





Thanks for your response Martin.



I removed the rownum and recreated the view as gpc_tp_query_vw2 and ran
the following query.



select * from gpc_tp_query_vw2 where userid = 'campbk2';



The query is still slow. Any other thoughts?



Here is the explain plan...





| Id  | Operation                          |  Name




 | Rows  | Bytes |TempSpc| Cost  |





|   0 | SELECT STATEMENT                   |


 | 24027 |  5349K|       | 88688 |

|   1 |  CONCATENATION                     |


 |       |       |       |       |

|   2 |   NESTED LOOPS                     |


 | 61849 |  7187K|       | 43533 |

|*  3 |    HASH JOIN                       |


 |     1 |    56 |       |     4 |

|   4 |     TABLE ACCESS FULL              | GPC_ENTITY_XREF


 |     2 |    28 |       |     2 |

|*  5 |     HASH JOIN                      |


 | 23417 |  5213K|    14M| 48091 |

|*  6 |      HASH JOIN                     |


 | 19728 |  3178K|       | 46853 |

|   7 |       TABLE ACCESS FULL            | GPC_BUSINESS


 |  2420 | 33880 |       |     4 |

|*  8 |       HASH JOIN                    |


 | 20036 |  2954K|    38M| 46844 |

|*  9 |        TABLE ACCESS BY INDEX ROWID | GPC_BATCH_TEMP


 |     1 |    42 |       |     2 |

|* 10 |         INDEX RANGE SCAN           | GPC_BATCH_TEMP_USERID_IDX


 |     1 |       |       |     1 |

|  11 |        TABLE ACCESS FULL           | GPC_PRODUCT_LBM


 |   963K|    29M|       |   828 |

|  12 |      TABLE ACCESS FULL             | GPC_PRODUCT


 |   321K|    19M|       |   383 |

|* 13 |    TABLE ACCESS BY INDEX ROWID     | GPC_ENTITY_COST


 |    42M|  2577M|       | 43119 |

|* 14 |     INDEX UNIQUE SCAN              | GPC_ENTITY_COST_PK


 |       |       |       |       |

|  15 |   NESTED LOOPS                     |


 | 19728 |  3178K|       | 46853 |

|  16 |    NESTED LOOPS                    |


 | 61849 |  7187K|       | 43533 |

|  17 |     NESTED LOOPS                   |


 | 20036 |  2954K|       | 46844 |

|  18 |      NESTED LOOPS                  |


 |     1 |    56 |       |     4 |

|  19 |       NESTED LOOPS                 |


 | 23417 |  5213K|       | 48091 |

|* 20 |        TABLE ACCESS BY INDEX ROWID | GPC_BATCH_TEMP


 |     1 |    42 |       |     2 |

|* 21 |         INDEX RANGE SCAN           | GPC_BATCH_TEMP_USERID_IDX


 |     1 |       |       |     1 |

|  22 |        TABLE ACCESS BY INDEX ROWID | GPC_PRODUCT


 |   321K|    19M|       |   383 |

|* 23 |         INDEX UNIQUE SCAN          | GPC_PRODUCT_PK


 |     1 |       |       |       |

|  24 |       TABLE ACCESS BY INDEX ROWID  | GPC_ENTITY_XREF


 |     2 |    28 |       |     2 |

|* 25 |        INDEX RANGE SCAN            | GPC_COST_ENTITY_PRD_EI_IDX


 |     2 |       |       |     1 |

|  26 |      TABLE ACCESS BY INDEX ROWID   | GPC_PRODUCT_LBM


 |   963K|    29M|       |   828 |

|* 27 |       INDEX RANGE SCAN             | GPC_PRODUCT_LBM_PK


 |     1 |       |       |       |

|* 28 |     TABLE ACCESS BY INDEX ROWID    | GPC_ENTITY_COST


 |    42M|  2577M|       | 43119 |

|* 29 |      INDEX UNIQUE SCAN             | GPC_ENTITY_COST_PK


 |       |       |       |       |

|* 30 |    TABLE ACCESS BY INDEX ROWID     | GPC_BUSINESS


 |  2420 | 33880 |       |     4 |

|* 31 |     INDEX UNIQUE SCAN              | GPC_BUSINESS_PK


 |     1 |       |       |       |

|* 32 |   HASH JOIN                        |


 | 23417 |  5213K|    14M| 48091 |

|* 33 |    HASH JOIN                       |


 | 19728 |  3178K|       | 46853 |

|  34 |     TABLE ACCESS FULL              | GPC_BUSINESS


 |  2420 | 33880 |       |     4 |

|* 35 |     HASH JOIN                      |


 | 20036 |  2954K|    38M| 46844 |

|* 36 |      HASH JOIN                     |


 | 61849 |  7187K|       | 43533 |

|  37 |       TABLE ACCESS BY INDEX ROWID  | GPC_ENTITY_XREF


 |     2 |    28 |       |     2 |

|  38 |        NESTED LOOPS                |


 |     1 |    56 |       |     4 |

|* 39 |         TABLE ACCESS BY INDEX ROWID| GPC_BATCH_TEMP


 |     1 |    42 |       |     2 |

|* 40 |          INDEX RANGE SCAN          | GPC_BATCH_TEMP_USERID_IDX


 |     1 |       |       |     1 |

|* 41 |         INDEX RANGE SCAN           | GPC_COST_ENTITY_PRD_EI_IDX


 |     2 |       |       |     1 |

|* 42 |       TABLE ACCESS FULL            | GPC_ENTITY_COST


 |    42M|  2577M|       | 43119 |

|  43 |      TABLE ACCESS FULL             | GPC_PRODUCT_LBM


 |   963K|    29M|       |   828 |

|  44 |    TABLE ACCESS FULL               | GPC_PRODUCT


 |   321K|    19M|       |   383 |

----------------------------------------------------------------------------------------------------------







Predicate Information (identified by operation id):








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



   5 - access("PROD"."GPC_PROD_ID"="PRODLBM"."GPC_PROD_ID")



   6 - access("BUS"."FIN_BUSINESS_ID"="TMP"."BUSINESS" AND
"PRODLBM"."FIN_PROD_ID"="BUS"."FIN_PROD_ID"

              AND "PRODLBM"."FISCAL_YEAR_ID"="BUS"."FISCAL_YEAR_ID")


   8 - access("PRODLBM"."FISCAL_YEAR_ID"=TO_NUMBER("TMP"."FISCALYEAR"))



   9 - filter("TMP"."BUSINESS" IS NOT NULL)



  10 - access("TMP"."USERID"='campbk2')



  13 - filter("PRODLBM"."END_FM"="ETYCST"."LBM_END_FM")



  14 - 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")



  20 - filter("TMP"."UPN" IS NOT NULL)



  21 - access("TMP"."USERID"='campbk2')



  23 - access("PROD"."GPC_PROD_ID"="TMP"."UPN")



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



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



              "PRODLBM"."FISCAL_YEAR_ID"=TO_NUMBER("TMP"."FISCALYEAR"))


  28 - filter("PRODLBM"."END_FM"="ETYCST"."LBM_END_FM")



  29 - 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")



  30 - filter(LNNVL("BUS"."FIN_BUSINESS_ID"="TMP"."BUSINESS") OR
LNNVL("TMP"."BUSINESS" IS NOT NULL))



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



              "PRODLBM"."FISCAL_YEAR_ID"="BUS"."FISCAL_YEAR_ID")


  32 - access("PROD"."PRODNO"="TMP"."CATALOG" AND
"PROD"."GPC_PROD_ID"="PRODLBM"."GPC_PROD_ID")


       filter(LNNVL("PROD"."GPC_PROD_ID"="TMP"."UPN") OR
LNNVL("TMP"."UPN" IS NOT NULL))


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



              "PRODLBM"."FISCAL_YEAR_ID"="BUS"."FISCAL_YEAR_ID")


       filter(LNNVL("BUS"."FIN_BUSINESS_ID"="TMP"."BUSINESS") OR
LNNVL("TMP"."BUSINESS" IS NOT NULL))



  35 - 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")


  36 - access("ETYCST"."ENTITY_ID"="X"."COST_ENTITY_ID" AND



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


  39 - filter("TMP"."CATALOG" IS NOT NULL)



  40 - access("TMP"."USERID"='campbk2')



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



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





Note: cpu costing is off





Martin T. wrote:

> 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 - 15:21:41 CDT












Original text of this message













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