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: Is is possible to tune this SQL statement?

Re: Is is possible to tune this SQL statement?

From: <oratech_at_mindspring.com>
Date: Mon, 13 Sep 1999 11:54:41 GMT
Message-ID: <7riolt$fju$1@nnrp1.deja.com>


The statement that "views are generally slow" is not only absurd, it's inaccurate. There is nothing about a "view" that affects performance. The only thing that affects the performance is the SQL statement defining the view.

In article <37da8d58.17544548_at_news.columbia.edu>,   k_a_h_l_i_l_at_yahoo.com wrote:
> Views generally are slow. Design your DB so that you can do an
indexed
> search on a single table for all of the possible queries. This might
> mean that your SQL is divided into several scripts, which query
> different tables depending on whatever your cariable condition
> statement depends on. Once you've found the record in your indexed
> search, you can either (1) right then and there fetch the related
info
> in related tables for that one record, or (2) aggregate that record
> data-- without its related info-- in a temporary space and fetch the
> related info for all the records your search returned at the end.
> Option (2) here depends a lot alos on whether you can reasonable
> assume that the space required to store your temporary aggregated
data
> is not going to go past a certain limit.
>
> You probably would be helped by using a performance tool that can get
> the exact processing times that the different implementations get.
> This is useful since performance depends on a lot of other factors
> other than data size and indexes, like table buffers, querying
> patterns, memory allocation, roll space, etc. Often nested fetches
> (option 1 above) or the aggregate fetch scheme (option 2 above)
> outperform views by a huge margin.
>
> -Matt
>
> On Sun, 12 Sep 1999 10:50:48 -0700, Jimmy
<c6635500_at_comp.polyu.edu.hk>
> wrote:
>
> >Hello all,
> >
> > I create a view AAA joining 5 tables and the resulting rows is
over
> >50000. I am doing query on AAA with the following SQL statements:
> >
> > select distinct(field) from AAA where condition...
> >
> > (View AAA has 50 columns, only the column with varchar2 data
type is
> >retrieved)
> > The condition part is different each time. Is there any way to
> >improve the above query time? If the fields to retrieve is only on
two
> >or three columns (each time one field is retrieved but the condition
> >part is again different each time), can I built index on these
columns
> >(i.e. can I built index on view column)? Or is there other ways can
> >improve the query time?
> >
> >
> >Thanks,
> >Jimmy
> >
> >
> >
>
>

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Mon Sep 13 1999 - 06:54:41 CDT

Original text of this message

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