Re: Is is possible to tune this SQL statement?

[Quoted] Views generally are slow. Design your DB so that you can do an indexed [Quoted] 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 [Quoted] 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 [Quoted] is not going to go past a certain limit.

You probably would be helped by using a performance tool that can get [Quoted] 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
>
>
>
Received on Sat Sep 11 1999 - 19:22:38 CEST