Re: Indexing a view

I agree with Rene. You should make this a materialized view. I had the same problem while trying to query 13 tables simultaneously. The only downside (in my opinion) is that if the data isn't static, you might find that you have to keep updating the materialized view. You can find more info on materialized views in the 8.1.7 documentation, if that's what you're running. I use that doc and found it to be very good.

Good luck.
Kevin

"Rene S. Nielsen" <renesn_at_image.dk> wrote in message news:<3d6372d2$0$64888$edfadb0f_at_dspool01.news.tele.dk>...
> This is a very big view
>
> To get any spped out of this, I would make it into a materialized view. So
> if you can do this. Then that would be a nice solution.
>
> Regards
> Rene
>
>
> "Riyaz" <skriyaz_at_yahoo.com> skrev i en meddelelse
> news:8b59c056.0208190722.2bef8857_at_posting.google.com...
> > skriyaz_at_yahoo.com (Riyaz) wrote in message
 news:<8b59c056.0208161030.5506e02f_at_posting.google.com>...
> > > Hi,
> > > Is there any way to index a VIEW.
> > >
> > > Thanks,
> > > Riyaz
> >
> > Hi Rene,
> > Thanks for your view.To be very clear this is how my view
> > goes.
> > It is just a select statment (14 tables) having outer joins to
> > retrieve all rows.which are linked by the "where criteria".The view is
> > getting very slow.
> > I just wanted to make it fast.Is there any other to rewrite this.
> >
> > This is how the view looks.
> >
> >
> >
> > create view view_tab as
> > select
> A.GROUP_ID,A.CASE_ID,A.INTTR_EMP_ID,A.RCVD_DT,A.CAU_ATRNY_EMP_ID,A.RGNL_ATRN
> Y_EMP_ID,A.XPDT_FL,A.RSPND_BY_DT,A.ACTN_CD,A.DCSN_CD,A.DCSN_DT,A.CMPLN_FILED
> _DT,A.FRML_ACTN_CASE_ID,
> > B.INTTG_DEPT_CD,B.EXAM_ID,B.CASE_DESC_TX,
> > C.TYPE_CD AS ALGTN_CODE,
> > D.DESC_TX AS ALGTN_DESC,
> > E.DESC_TX AS ACTN_DESC,
> > F.LAST_NM AS EMPLOYEE_LAST_NAME,F.LOC_ID AS EMP_LOC_ID,
> > G.BLOCK_NT,
> > H.OFFR_CASE_ID,H.RCVD_DT AS OFFER_RCVD_DT,H.CAU_DCSN_CD,
> > H.CAU_DCSN_DT,H.DCMTN_RCVD_DT,
> > I.CRD_ID,I.RSPDT_NM,
> > J.ST_CD,
> > K.DESC_TX AS STATUS_DESCRIPTION,
> > L.DESC_TX AS DEPT_DESC,
> > M.PRNT_EXAM_ID,M.CHILD_EXAM_ID
> > from
> > TABLE A,
> > TABLE B,
> > TABLE C,
> > TABLE D,
> > TABLE E,
> > TABLE F,
> > TABLE G,
> > TABLE H,
> > TABLE I,
> > TABLE J,
> > TABLE K,
> > TABLE L,
> > TABLE M
> > where a.group_id =b.group_id(+) and
> > A.CASE_ID =C.CASE_ID (+) and
> > C.TYPE_CD =D.TYPE_CD (+) and
> > A.ACTN_CD =E.ACTN_CD (+) and
> > A.INTTR_EMP_ID=F.EMP_ID (+) and
> > A.CASE_ID =G.CASE_ID (+) and
> > A.CASE_ID =H.CASE_ID (+) and
> > A.CASE_ID =I.CASE_ID (+) and
> > A.CASE_ID =J.CASE_ID (+) and
> > J.ST_CD =K.ST_CD (+) and
> > B.INTTG_DEPT_CD=L.DEPT_CD (+) and
> > B.EXAM_ID= M.PRNT_EXAM_ID (+)
Received on Sat Aug 24 2002 - 06:12:55 CEST