Oracle Query-problem help wanted.

From: Metin Zeybek <hdamz_at_hda.hydro.com>
Date: Wed, 20 Apr 1994 13:40:57
Message-ID: <hdamz.10.000DAF26_at_hda.hydro.com>


Hello friends !!!

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.

Thank you in advance.

Metin, Oslo  

SQL> desc mal_perioder;

 Name                            Null?    Type

------------------------------- -------- ----
MAL_TYPE NOT NULL VARCHAR2(1) MAL_GRUPPE NOT NULL VARCHAR2(16) MAL_KODE NOT NULL VARCHAR2(16) FOM_AAR NOT NULL NUMBER(4) FOM_UKE NOT NULL NUMBER(2) TOM_AAR NUMBER(4) TOM_UKE NUMBER(2) STATUS VARCHAR2(1) SQL> desc temp_bel_sted_hdoe ; Name Null? Type
------------------------------- -------- ----
REGN_REG_KODE NOT NULL VARCHAR2(16) OVERF_DATO NOT NULL DATE DELREGN_KODE NOT NULL VARCHAR2(16) DELREGN_NAVN NOT NULL VARCHAR2(50) OPPDRAG_KODE NOT NULL VARCHAR2(16) OPPDRAG_NAVN NOT NULL VARCHAR2(50) FOM_AAR_NR NOT NULL NUMBER(6) TOM_AAR_NR NOT NULL NUMBER(6) BEST_KODE VARCHAR2(16) BEST_NAVN VARCHAR2(50) SPERRET_IND VARCHAR2(2) SQL> desc regnskaps_perioder; Name Null? Type
------------------------------- -------- ----
ID NOT NULL NUMBER(8) REGN_REG_KODE NOT NULL VARCHAR2(16) AAR NOT NULL NUMBER(4) NR NOT NULL NUMBER(3) FOM_UKE NUMBER(2) TOM_UKE NUMBER(2) NORMAL_TID NUMBER(5,1)

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);
Received on Wed Apr 20 1994 - 13:40:57 CEST

Original text of this message