"Jan Gelbrich" <j_gelbrich_at_westfalen-blatt.de> wrote in message news:<asigsm$rmp15$1_at_ID-152732.news.dfncis.de>...
> select statement
> filter
> nested loops (outer)
> nested loops
> table access (by index rowid) person
> index (range scan) i_uname
> table access (by index rowid) adresse
> index (range scan) i_adr_pers
> table access (by index rowid) strasse
> index (unique scan) pk_strasse
> filter
> index (range scan) i_abostamm_co2
>
> it runs fine in SQL*Plus, just as it should, but when used for a LOV in
> Forms,
> LOV comes up only as all data are completely received by the client
> (different from SQL*Plus).
> If you enter "Gelbrich" as a name, the LOV comes up fast since the name is
> rare,
> but if You enter "Smith", You receive 1000 names ... that is the real
> problem.
>
> I personally do not beleive that partitioning is a considerable way to solve
> the problem,
> but I wanted to hear Your opinions ...
Could you get statistics for the query (autotrace or 10046)? In case
of "Smith" I expect ~ 5000 buffer gets if you select 1000 records in
the worst case as you describe. That shouldn't be a problem (since
caching works well for index scans and NLs there would be only few
physical IOs). If you have significanly more logical IO, then you have
to figure out which range scan is not very selective. In 9.2 this can
be done without any analysis since statistics is captured per each
rowsource. You'll immediately see the node where extra IO is coming
from.
Received on Tue Dec 03 2002 - 18:56:08 CST
