Re: Help, I am desperate: Sql problems or a bug

Hi,

I had a query on a report that joined two tables. The first one had 27000 lines,
the second *no rows*, and even so the query took about 5 minutes to execute.
It seemed that the optimizer was going through the first table, so I put the
optimizer hint /*+ use_nl(my_table) */ where my_table it the table that has to have less rows (in my case, no rows).

So, I can try:

select /*+ use_nl(ose.bundt) */ nvl(round(sum(fp.beloeb),0),0)*(-1) from ose.bundt bu, ose.fpost
fp
where fp.rgkod = '0001'
...

assuming ose.bundt to be the table with less rows.

I hope it help you, and I would be glad if you could tell me if that worked or not

Luis Cabral (Brasil)
cabral_at_merconet.com.br

Casper Thrane <ct_at_benau.dk> escreveu no artigo <348D93D5.D5BA648A_at_benau.dk>...
> Hi!
>
> I have worked a little with my sql problem and changed a bit in the
> script. I can see that it works fine, if I only have to update 10
> rows(the full recordset is 1600 rows). But after we have upgraded to
> 7.3.3 from 7.3.2 on digital Unix, it is 20-30 times slower than before.
> Have we forgot to change a specific parameter, or is this a bug in
> 7.3.3.
>
> We have created indexes on the tables.
>
> the script looks like this:
>
> update repport set
> Use =
> (select nvl(round(sum(fp.beloeb),0),0)*(-1) from ose.bundt bu, ose.fpost
> fp
> where fp.rgkod = '0001'
> and fp.kntid = repport.actid
> and fp.bidat >= 19970101
> and fp.bidat <= 19971231
> and bu.nedtid = 0
> and bu.rgkod = '0001'
> and bu.bdtnr = fp.bdtnr)
>
> Please help me.
> --
> Casper Thrane
> System developer
> Benau A/S
>
Received on Wed Dec 10 1997 - 00:00:00 CST