Home » RDBMS Server » Performance Tuning » merging 2 big tables (Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production, linux)
| merging 2 big tables [message #511625] |
Tue, 14 June 2011 05:34  |
orsoghu
Messages: 46
Registered: May 2011
Location: Venice, Italy
|
Member |
|
|
Hi there, sorry for what will be a long post.
I have 2 tables with the same DDL. Each one has a little less than 5M rows. What I need to do is very simple.
For each row of the master table do:
- IF master's key exists in slave table, update a few fields of master table
- IF master's key doesn't exists in slave table, update other fields
- IF slave's key doesn't exists in master table, insert row
What I'm doing for point 1 and 3 is using a merge instruction. This instruction lasts like 1 hour and a half and and I would like to improve that a little bit, if possibile.
Point 2 will be an other update that I still have to write. (Isn't it possible to do it with the same merge instruction, is it?)
So here's everything:
Tables DDL:
Master table
-- Create table
create table ANG_UNI_A0_DEF_MOB
(
PRG_REC NUMBER not null,
ANNO NUMBER not null,
FASE NUMBER not null,
COD_ENTE VARCHAR2(3 CHAR) not null,
TIPO_ENTE VARCHAR2(1 CHAR) not null,
PRG_FILE NUMBER not null,
PRG_INVIO NUMBER,
DATA_RIF VARCHAR2(8 CHAR),
DATA_IN_VAL DATE,
DATA_FI_VAL DATE,
FLG_ERR NUMBER,
FLG_ARCHIVIO VARCHAR2(1 CHAR),
COD_REGIONE_RES VARCHAR2(3 CHAR),
COD_AZIENDA_RES VARCHAR2(3 CHAR),
COD_AZIENDA_ASS VARCHAR2(3 CHAR),
DATA_ISCRIZIONE_RES VARCHAR2(8 CHAR),
DATA_ISCRIZIONE_ASS VARCHAR2(8 CHAR),
COD_AZIENDA VARCHAR2(3 CHAR),
CODICE_FISCALE VARCHAR2(16 CHAR),
CODICE_SSN VARCHAR2(9 CHAR),
COGNOME VARCHAR2(30 CHAR),
NOME VARCHAR2(20 CHAR),
SESSO VARCHAR2(1 CHAR),
DATA_NASCITA VARCHAR2(8 CHAR),
COD_COMUNE_NASCITA VARCHAR2(6 CHAR),
COD_COMUNE_RES VARCHAR2(6 CHAR),
INDIRIZZO_RES VARCHAR2(35 CHAR),
NUMERO_RES VARCHAR2(6 CHAR),
LOCALITA_RES VARCHAR2(35 CHAR),
CAP_RES VARCHAR2(5 CHAR),
COD_COMUNE_DOM VARCHAR2(6 CHAR),
INDIRIZZO_DOM VARCHAR2(35 CHAR),
NUMERO_DOM VARCHAR2(6 CHAR),
LOCALITA_DOM VARCHAR2(35 CHAR),
CAP_DOM VARCHAR2(5 CHAR),
COD_CITTADINANZA VARCHAR2(3 CHAR),
COD_MEDICO VARCHAR2(6 CHAR),
DATA_SCELTA VARCHAR2(8 CHAR),
DATA_ISCRIZIONE VARCHAR2(8 CHAR),
DATA_SCADENZA VARCHAR2(8 CHAR),
STATO_SOGGETTO VARCHAR2(1 CHAR),
CODICE_FISCALE_MEDICO VARCHAR2(16 CHAR),
FLG_VARIAZIONE VARCHAR2(1 CHAR),
COD_COMUNE_NASCITA_RIC VARCHAR2(6 CHAR),
ID_UNICO NUMBER,
FLG_ID_UNICO NUMBER,
NOME_AT VARCHAR2(50 CHAR),
COGNOME_AT VARCHAR2(50 CHAR),
PRG_REC_TS NUMBER,
FLG_CHK_CODICE_FISCALE NUMBER,
FLG_CHK_CODICE_SSN NUMBER,
FLG_CHK_COGNOME_E_NOME NUMBER,
CODICE_UNICO NUMBER,
FLG_CHIAVE_LINK_CODICE_UNICO NUMBER,
FLG_CHK_DATI_ANAG_CONGRUENTI NUMBER,
CODICE_UNICO_BIN RAW(16),
REFRESH_DATE DATE default sysdate
)
tablespace TSD_ASRUSR_1
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
-- Create/Recreate indexes
create index IDX_ANG_CODICE_FISCALE on ANG_UNI_A0_DEF_MOB (CODICE_FISCALE, DATA_IN_VAL, DATA_FI_VAL)
tablespace TSI_ASRUSR_1
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
create index IDX_ANG_CODICE_UNICO on ANG_UNI_A0_DEF_MOB (CODICE_UNICO_BIN, COD_ENTE, NVL(COD_REGIONE_RES,'NULL'))
tablespace TSI_ASRUSR_1
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
create index IDX_ANG_DATI_ANAG on ANG_UNI_A0_DEF_MOB (NOME, COGNOME, DATA_NASCITA, DATA_IN_VAL, DATA_FI_VAL)
tablespace TSI_ASRUSR_1
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
Slave table
-- Create table
create table ANG_UNI_A0_DEF_MOB_APPO
/* ( Same as above
)
-- no indexes, I insert stuff here with either data pump of sqlldr direct=yes */
Merge script:
merge into ang_uni_a0_def_mob a
using (select *
from ang_uni_a0_def_mob_appo b
where b.flg_chk_codice_fiscale = 100
and b.cod_Regione_res != '999') b
on (a.codice_unico_bin = b.codice_unico_bin and a.cod_ente = b.cod_ente and NVL(a.cod_regione_res, 'NULL') = NVL(b.cod_regione_res, 'NULL'))
when matched then
update
set a.data_rif = b.data_rif,
a.cod_Azienda_res = b.cod_Azienda_res,
a.cod_Azienda_ass = b.cod_azienda_Ass,
a.data_iscrizione_Res = b.data_iscrizione_res,
a.data_iscrizione_ass = b.datA_iscrizione_ass,
a.cod_comune_Res = b.cod_comune_Res,
a.cap_res = b.cap_Res,
a.cod_comune_dom = b.cod_comune_dom,
a.cap_dom = b.cap_dom,
a.cod_cittadinanza = b.cod_cittadinanza,
a.cod_medico = b.cod_medico,
a.data_scelta = b.data_scelta,
a.data_iscrizione = b.data_iscrizione,
a.data_scadenza = b.data_scadenza,
a.stato_soggetto = b.stato_soggetto,
a.codice_fiscale_medico = b.codice_fiscale_medico,
a.flg_variazione = 'V',
a.refresh_Date = SYSDATE,
a.data_fi_Val = TO_DATE(NVL(b.data_scadenza, a.data_fi_val),'YYYYMMDD')
when not matched then
INSERT
(a.prg_rec,
a.anno,
a.fase,
a.cod_ente,
a.tipo_ente,
a.prg_file,
a.prg_invio,
a.data_rif,
a.data_in_val,
a.data_fi_val,
a.flg_err,
a.flg_archivio,
a.cod_regione_res,
a.cod_azienda_res,
a.cod_azienda_ass,
a.data_iscrizione_res,
a.data_iscrizione_ass,
a.cod_azienda,
a.codice_fiscale,
a.codice_ssn,
a.cognome,
a.nome,
a.sesso,
a.data_nascita,
a.cod_comune_nascita,
a.cod_comune_res,
a.indirizzo_res,
a.numero_res,
a.localita_res,
a.cap_res,
a.cod_comune_dom,
a.indirizzo_dom,
a.numero_dom,
a.localita_dom,
a.cap_dom,
a.cod_cittadinanza,
a.cod_medico,
a.data_scelta,
a.data_iscrizione,
a.data_scadenza,
a.stato_soggetto,
a.codice_fiscale_medico,
a.flg_variazione,
a.cod_comune_nascita_ric,
a.id_unico,
a.flg_id_unico,
a.nome_at,
a.cognome_at,
a.prg_rec_ts,
a.flg_chk_codice_fiscale,
a.flg_chk_codice_ssn,
a.flg_chk_cognome_e_nome,
a.codice_unico,
a.flg_chiave_link_codice_unico,
a.flg_chk_dati_anag_congruenti,
a.codice_unico_bin,
a.refresh_date)
values
(b.prg_rec,
b.anno,
b.fase,
b.cod_ente,
b.tipo_ente,
b.prg_file,
b.prg_invio,
b.data_rif,
TO_DATE(NVL(LEAST(b.data_iscrizione_res, b.data_iscrizione_ass, b.data_iscrizione),b.data_rif),'YYYYMMDD'),
TO_DATE(NVL(b.data_scadenza,'99991231'),'yyyymmdd'),
b.flg_err,
b.flg_archivio,
b.cod_regione_res,
b.cod_azienda_res,
b.cod_azienda_ass,
b.data_iscrizione_res,
b.data_iscrizione_ass,
b.cod_azienda,
b.codice_fiscale,
b.codice_ssn,
b.cognome,
b.nome,
b.sesso,
b.data_nascita,
b.cod_comune_nascita,
b.cod_comune_res,
b.indirizzo_res,
b.numero_res,
b.localita_res,
b.cap_res,
b.cod_comune_dom,
b.indirizzo_dom,
b.numero_dom,
b.localita_dom,
b.cap_dom,
b.cod_cittadinanza,
b.cod_medico,
b.data_scelta,
b.data_iscrizione,
b.data_scadenza,
b.stato_soggetto,
b.codice_fiscale_medico,
'I',
b.cod_comune_nascita_ric,
b.id_unico,
b.flg_id_unico,
b.nome_at,
b.cognome_at,
b.prg_rec_ts,
b.flg_chk_codice_fiscale,
b.flg_chk_codice_ssn,
b.flg_chk_cognome_e_nome,
b.codice_unico,
b.flg_chiave_link_codice_unico,
b.flg_chk_dati_anag_congruenti,
b.codice_unico_bin,
sysdate);
Explain plan
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 934458761
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------
| 0 | MERGE STATEMENT | | 5013K| 8912M| | 461K (1)| 01:32:23 |
| 1 | MERGE | ANG_UNI_A0_DEF_MOB | | | | | |
| 2 | VIEW | | | | | | |
|* 3 | HASH JOIN RIGHT OUTER| | 5013K| 7244M| 1514M| 461K (1)| 01:32:23 |
| 4 | TABLE ACCESS FULL | ANG_UNI_A0_DEF_MOB | 4931K| 1458M| | 50549 (2)| 00:10:07 |
|* 5 | TABLE ACCESS FULL | ANG_UNI_A0_DEF_MOB_APPO | 5013K| 5761M| | 47562 (2)| 00:09:31 |
-----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access(NVL("A"."COD_REGIONE_RES"(+),'NULL')=NVL("B"."COD_REGIONE_RES",'NULL') AND
"A"."COD_ENTE"(+)="B"."COD_ENTE" AND "A"."CODICE_UNICO_BIN"(+)="B"."CODICE_UNICO_BIN")
5 - filter("B"."FLG_CHK_CODICE_FISCALE"=100 AND "B"."COD_REGIONE_RES"<>'999')
Note
-----
- dynamic sampling used for this statement
Trace file
attached
thanks for your help
|
|
|
|
|
|
| Re: merging 2 big tables [message #511635 is a reply to message #511632] |
Tue, 14 June 2011 05:58  |
orsoghu
Messages: 46
Registered: May 2011
Location: Venice, Italy
|
Member |
|
|
Quote:Also create indexes on the slave table as well.
Why? I must always scan everything from there + I would have additional time during inserts in the slave table and index rebuild
|
|
|
|
| Re: merging 2 big tables [message #511637 is a reply to message #511635] |
Tue, 14 June 2011 06:08 |
Yasir Hashmi
Messages: 304
Registered: April 2006
|
Senior Member |
|
|
Quote:Why?
Because this query:
select *
from ang_uni_a0_def_mob_appo b
where b.flg_chk_codice_fiscale = 100
and b.cod_Regione_res != '999'
does a full scan.
How many rows are there with the condition where b.flg_chk_codice_fiscale = 100?
Quote:
I would have additional time during inserts in the slave table
Ok
Quote:
and index rebuild
Why would you need to rebuild the index?
Post TKPROF output as well.
|
|
|
|
| Re: merging 2 big tables [message #511644 is a reply to message #511637] |
Tue, 14 June 2011 06:22 |
orsoghu
Messages: 46
Registered: May 2011
Location: Venice, Italy
|
Member |
|
|
Quote:TKprof with sys=no,waits=yes option.
If i run the query again with the options you suggested, would you still have a useful result even if master table has already been updated by the previous run?
Quote:How many rows are there with the condition where b.flg_chk_codice_fiscale = 100?
99.76%
Quote:Why would you need to rebuild the index?
If I use sqlldr direct=yes I would need it. I'm not sure with impdb.
Anyway since I must get pretty much all rows of slave table, I was hoping to obtain a full scan on slave and index on master, but the explain plan goes for a double full table scan.
P.S.: I can't go with /*+ parallel */ because our server has 4 processors and they're used by a lot of different applications, so if I take more than 1 cpu I would probably get 30-40 people very very angry with me 
|
|
|
|
Goto Forum:
Current Time: Thu Apr 25 02:25:22 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
