Re: Oracle Query-problem help wanted.

Earlier Metin Zeybek wrote:
> Hello friends !!!
>

Hi!

>
> I got a ORACLE SQL-problem that I am not able to resolve.
> The query is working but it takes about 30-40 minutes on 1200 rows.
>
> I hope somebody can help me .
>
> The tables and the query are listed below
>
> Any comments are welcome.
>

Indexes would help if you could list those or add them if you don't have any.

[Table defs snipped for brevity]

>
>
> The Query :
>
>
> DELETE from mal_perioder mp
> WHERE (mp.mal_kode, mp.fom_aar, mp.fom_uke) in
> (SELECT t.best_kode, to_number(substr(to_char(t.fom_aar_nr),1,4)), fp.fom_uke
> FROM temp_bel_sted_hdoe t, regnskaps_perioder fp
> WHERE mp.mal_type = 'A'
> and mp.mal_gruppe = 'BESTILL U&P-VK'
> and to_number(substr(to_char(t.fom_aar_nr),1,4)) = fp.aar
> and to_number(substr(to_char(t.fom_aar_nr),5,6)) = fp.nr
> and t.regn_reg_kode = fp.regn_reg_kode);
>

From what I can see, you might want to try something like this:

DELETE
from mal_perioder mp
WHERE mp.mal_type = 'A'
AND mp.mal_gruppe = 'BESTILL U&P-VK'
AND EXISTS

        (SELECT 'x'
         FROM  temp_bel_sted_hdoe t, regnskaps_perioder fp
         WHERE fp.aar = mp.fom_arr
         AND   fp.fom_uke = mp.fom_uke
         AND   t.best_kode = mp.mal_kode
         AND   t.regn_reg_kode = fp.regn_reg_kode
         AND   to_number(substr(to_char(t.fom_aar_nr),1,4)) = fp.aar
         AND   to_number(substr(to_char(t.fom_aar_nr),5,6)) = fp.nr)

It might help, but I could probably make a better guess if I knew what indexes existed.

> Thank you in advance.
>

You're welcome, hope this helps.

Andy

-- 
 /---------------------------------------------------------------------------\
 | Andrew Dowswell     | "...They bring me numbness in their bright needles, |
 | Parke-Davis (CPO)   |  they bring me sleep. Now I have lost myself..."    |
 | dowswea_at_aa.wl.com   |                                                     |
 | (313) 996-1337      |             Sylvia Plath "Tulips"                   |
 \---------------------------------------------------------------------------/
 Disclaimer: The opinions of Parke-Davis are not necessarily my own, they
             should not be construed as mine and no flames should be directed 
             at me for something Parke-Davis has said or done.