Re: tuning select statements

"Sabine.Buechel" wrote:

> 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

Have you run this query in SQL*Plus? How does it perform there? Have created statistics using DBMS_STATS for the optimizer to use? Have you run Explain Plan?
Are there indexes matching the join condition ? (though I'm not sure with so few records they would help rather than hurt). How about TKProf?

Daniel Morgan Received on Thu Jun 20 2002 - 11:22:57 CDT