Here the informations :
table HREL_FUSION :
63 millions rows
3 indexes on columns : nodos_or, nodos_or, numcli <------- too much indexes ??
table primedi_enr2_temp_fusion : 133 000 rows
I also took snaps with stastpack, can it help you ?
this table is recreated and re-analyze each day, but the hanging part of the scritp in located here :
******************************************************
SELECT NULL
FROM hrel_fusion
WHERE cod_rel = :b1
AND dat_rel = :b2
AND NOT EXISTS (SELECT NULL
FROM primedi_enr2_temp_fusion
WHERE primedi_enr2_temp_fusion.nodos_or =
hrel_fusion.nodos_or
AND primedi_enr2_temp_fusion.code_logis =
hrel_fusion.cod_rel
AND primedi_enr2_temp_fusion.date_logis =
hrel_fusion.dat_rel)
FOR UPDATE
**********************************************************
The mostly amazing thing is that the same script work in 12 min on non-partitionned table.
Here a subset of the script :
***********************************************************
cat <<EOD >$maj_histo_rel
set serveroutput on;
DECLARE
vt_code_logis char(4);
vt_date_logis number(4);
n number;
cursor curs_code_ctlm is
select code_logis,date_logis from primedi_temp_fusion
where exists (select null from $TABLE_RETOURS
where $TABLE_RETOURS.code_logis=primedi_temp_fusion.code_logis
and $TABLE_RETOURS.date_logis=primedi_temp_fusion.date_logis
and $TABLE_RETOURS.code_ficom=primedi_temp_fusion.code_ficom
)
;
cursor curs_histo_ctlm1 (vt_code_logis char, vt_date_logis number) is
select null from HREL_FUSION
where cod_rel=vt_code_logis
and dat_rel=vt_date_logis
and not exists (select null from primedi_enr2_temp_fusion
where primedi_enr2_temp_fusion.nodos_or=HREL_FUSION.nodos_or
and primedi_enr2_temp_fusion.code_logis=HREL_FUSION.cod_rel
and primedi_enr2_temp_fusion.date_logis=HREL_FUSION.dat_rel)
for update
;
BEGIN
DBMS_OUTPUT.ENABLE(500000);
for curs in curs_code_ctlm
loop
n:=0;
for enr_histo in curs_histo_ctlm1 (curs.code_logis,curs.date_logis) loop
delete from HREL_FUSION
where current of curs_histo_ctlm1;
n:=n+1;
--if (mod(n,5000) = 0) then
--commit;
--end if;
end loop;
--commit;
DBMS_OUTPUT.PUT_LINE('Supression de ' || n || ' ligne(s) ' ||curs.code_logis || curs.date_logis || ' dans l historique HREL_FUSION');
-- update $TABLE_RETOURS
-- set DATE_MAJ=to_number(to_char(sysdate,'yyyymmdd'))
-- where exists (select null from primedi_temp_fusion
-- where curs.code_logis=$TABLE_RETOURS.code_logis
-- and curs.date_logis=$TABLE_RETOURS.date_logis
-- )
--;
end loop;
COMMIT;
END;
/
exit;
EOD
***********************************************************
