here the trace :
SELECT STATEMENT, GOAL = CHOOSE 2 72 1368
FOR UPDATE
FILTER
PARTITION RANGE SINGLE
TABLE ACCESS BY LOCAL INDEX ROWID FICOM HREL_FUSION 2 72 1368
INDEX RANGE SCAN FICOM IDX_HREL_FUSION_P_COD_REL 1 72
TABLE ACCESS BY INDEX ROWID FICOM PRIMEDI_ENR2_TEMP_FUSION 2 1 32
INDEX RANGE SCAN FICOM IDX_PRIMCOD_ENR2_TEMP_FUSION 1 1
TIA
> ____________________________________________________
> Philippe Nguyen
> CETELEM - Administration, Architecture Décisionnelle
> Direction Customer Relationship Management
> E-Mail : [EMAIL PROTECTED]
> Tel : (+33) 1 46 39 92 16 - Fax : (+33) 1 46 39 59 88
>
-----Message d'origine-----
De : [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Envoyé : 05 September 2003 00:09
À : Multiple recipients of list ORACLE-L
Objet : RE: RE: URGENT : sql*loader performance problem on partionned
tab
Did you explain plan? I suspect FTS taking place in case of NOT EXISTS.
It must be using Range scan for the non partitioned table.
Can you confirm / post the explain plan.
GovindanK
> 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
> ***********************************************************
>
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
