Re: Is is possible to tune this SQL statement?

Not only is oratech_at_mindspring correct about views, the rest of Matt's SQL coding advice is questionable. Let the database be the database! Doing separate queries for each table is WAY too much work. A properly written join, against properly designed tables, whether it's in a view or a query, will perform just fine. It will often outperform multiple queries; on a client-server WAN, it will outperform by a huge margin.

Matt's advice on performance tools is good. tkprof, while not the easist to use, has the advantage of cost -- it comes with Oracle.

In article <7riolt$fju$1_at_nnrp1.deja.com>,   oratech_at_mindspring.com wrote:
> 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.
>

--
Joseph Thvedt
jthvedt_at_my-deja.com


Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't.