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

Home -> Community -> Usenet -> c.d.o.server -> Re: How to boost the performance of a view?

Re: How to boost the performance of a view?

From: Jonathan Gennick <gennick_at_worldnet.att.net>
Date: Sat, 11 Apr 1998 01:46:08 GMT
Message-ID: <6gmi1p$9kk@bgtnsc03.worldnet.att.net>


On Fri, 10 Apr 1998 16:20:26 -0500, Sam <sambavan_at_tenet.edu> wrote:

>Does the view get created (ie formed) every time we make a call to it? I
>believe its not the way. Thanx in advance.

A view is just a stored select statement. When you issue a select against the view, oracle merges the two statements together. There are different ways Oracle can approach the merge, but in your case I would guess that Oracle is simply merging the where clauses.

>I have a buch of user info. tables. inorder to retrive the informations
>at one shot, I created a view. The primary key for the main table is
>USER_ID and it is the Foreign key to other child tables. The table as a
>whole has about 20000 records.
>When I use the cursor to retrive the information using the USER_ID as
>where condition, it almost takes about 25-30 secs to retrive the
>information for me.
>
>Could any body tell me, how I could shorte this fetch time?

Are all the tables joined on USER_ID? Have you checked to be sure that all the tables are indexed on that field? Another thing to do is to use EXPLAIN PLAN, and have Oracle tell you what it's execution strategy really is. If you look in your Server SQL Reference manual, I believe you will find a reasonably good explanation of how to do that. Received on Fri Apr 10 1998 - 20:46:08 CDT

Original text of this message

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