tuning select statements

From: Sabine.Buechel <Sabine.Buechel_at_innosystec.de>
Date: Thu, 20 Jun 2002 17:34:34 +0200
Message-ID: <aessih$3p3$06$1@news.t-online.com>

Hallo All,

have performance problems in FORMS.
Do someone have a solution, or tips 4 me?

Description of the Problem:

There exist 2 tables: may say 'A' and 'B' with a relational Table 'AB'.
'A' have little data (say 50 rows). But 'B' have a lot of data (over 50.000
rows!) for may say the halfe of rows of 'A'.

In the Forms Block I have to show all the Query Counts of one special A-row. So I
decided to calculate the hits over a formula.

I have few Indexes 4 the critical columns of 'B'.

Heres what the Formula 'XY'(on field Properties in Block) looks like:

ProgramUnit 'XY'

---

declare
v number;
begin
select count(*) into v
from AB ab, B b

where  ab.pk_b = b.pk_b                ---> over 50.000 hits
and    ab.pk_a    =  <something>    ----> same
and    b.num = 1;                            --->little...~5

return v;
end;

Performance is very bad.
Does someone has any idea?

Thanks in advance Received on Thu Jun 20 2002 - 10:34:34 CDT