Alexandre, seguem algumas obs gerais , vamos ver se te ajudo :
1) a regra de ouro para manipulações de dados, principalmente em
grandes volumes, cargas, etc, é SIMPLES : tente AO MÁXIMO fazer tudo
num único SQL, usando os recursos todos do SQL, apenas SE realmente
não conseguir aí sim tente um cursor PL/SQL, se ainda assim não
conseguir aí sim ir pra C ou java. Isso visando principalmente à
performance, já que (como já falado em msgs anteriores, blablabla) há
a questão de context switch, o CBO é *** COMPLETAMENTE *** incapaz de
otimizar SQLs separados (como o SELECT num cirsor e os SQLs dentro
dele), etc, etc, etc... No seu caso vc quer fazer um UPSERT (UPdate se
existe, inSERT se não), já existe ** SIM ** no 9i um comando SQL
nativo pra isso, é , como outros colegas já indicaram, o MERGE,
consulte o manual "SQL Reference" do 9i que ele EXISTE SIM no 9i.
Obviamente, o MERGE trabalha com tabelas, então não sei se ficou claro
mas pra isso vc teria que usar o EXTERNAL TABLE, aí o teu
arquivo-texto funcionaria como se fosse uma tabela, podendo ser lida
via SELECT, participar de um MERGE, de um JOIN, normal....
O problema pra isso na 9i é que lá vc não tem o recurso de DML Error
Logging 9que este sim só veio no 10g), então se o merge 9i em qualquer
linha falhar (veio um número errado no arquivo-texto, uma data, enfim,
qquer problema causador de exception) no 9i o merge falha
integralmente e vc não tem um log das falhas, só a mensagem de erro...
Outra coisa é que o MERGE *** exige *** constraint de PK/UK presente,
e ** proíbe ** UPDATEs na chave...
Então a coisa é , SE isso é contornável/aceitável OK, vá de MERGE num
SQL só que deve ser mais performático, senão continue nas opções abaixo.
2) se vc não puder usar o MERGE por causa das limitações dele no 9i,
voltando um pouco a recomendação de usar SQLs para processar, outra
opção poderia ser (** sempre ** tendo o arquivo-texto como external
table) , E SE os INSERTs forem maioria esmagadora, fazer :
a. inserir quem tá na tabela-external / arq.texto e não existe na
tab-destino com :
INSERT /*+ APPEND */ INTO tabelasdestino (SELECT ... FROM
tabeladestino a, externalapontandoproarqtexto b
WHERE a.chavedatabdestino =
b.campodatabelaextorigem (+)
AND b.campodatabelaextorigem is null);
b. botar numa GTT para localizar rapidamente quem será UPDATE :
INSERT /*+ APPEND */ INTO GTT (SELECT ... FROM tabeladestino a,
externalapontandoproarqtexto b
WHERE a.chavedatabdestino =
b.campodatabelaextorigem (+)
AND b.campodatabelaextorigem is null);
c. cursor (*** OBVIAMENTE *** com BULK COLLECT, FORALL, etc, etc)
lendo as linhas da GTT e fazendo os UPDATEs, com COMMIT só no final, etc.
3) já falamos algumas vezes, mas vale o reforço : ao usar o hint de
NOLOGGING num UPDATE sabe o que vc obtém ?? Pontas dos dedos mais
gastas do que o necessário, pois vc acabou de digitar algo IMPOSSÌVEL,
o banco só pode mesmo ignorar o que vc indicou... A questão é que o
NOLOGGING funciona introduzindo dados em blocos vazios, situados além
do limite dos blocos usados (daí o APPEND do hint), e *** OBVIAMENTE
*** um UPDATE vai mexer numa linha dum bloco que JÁ EXISTE, um bloco
potencialmente "cheio", já com outras linhas, NÂO TEM COMO fazer
append-mode com UPDATE por causa disso...
4) pro INSERT ... VALUES (valor1, valor2...) que vc usa vc não obterá
*** COISA ALGUMA ***, nada, niente, nill, null com o HINT de APPEND,
nem com as tabelas em modo de nolog : como já dito anteriormente, o
APPEND / NOLOG funciona se vc está inserindo múltiplas linhas, que
podem portanto ser dirigidas para blocos virgens/vazios acima da lista
de blocos usados - EVIDENTEMENTE um INSERT... VALUES está inserindo UM
REGISTRO APENAS, ele NÃO VAI movimentar múltiplos blocos (por
definição um registro SEMPRE está alocado prum bloco só, falando de
tabelas heap comuns), então não tem como se fazer APPEND-mode.... O
fato é, tanto pro teu UPDATE (por definição) quanto pro teu INSERT
(pois ele operam em uma linha só, ao invés de fazer INSERT ... (SELECT
multiplaslinhas) vale aí o bordão de um vilão da minha infância ;
ZEEEROOOO!!! Blz ?
5) finalmente, se por qquer motivo não der pra fazer nada do acima, ao
menos altere a sua rotina para NÂO ficar lendo e processamento uma
linha por vez do arquivo-texto (a external table seria ótima pra isso)
e processe com BULK COLLECT e FORALL o cursor resultante.
[]s
Chiappa
--- Em oracle_br@yahoogrupos.com.br, Alexandre Brum
<alexandre_b...@...> escreveu
>
> Bom dia Wilson
>
> O Oracle que estou usando é 9i. Acredito que o comando MERGE está
disponível a partir da 10g. Estranhamente esta procedure gastou apenas
7 min para rodar no meu micro, mas no servidor está gastando quase 2h.
>
> Configuração do meu micro:
>
> Intel Core(TM)2 Duo
> E4600 2.40 GHz
> 1.20 GHz, 1,98 GB de Ram
>
>
> Configuração do Servidor:
>
>
> HP DL 580
> 4 Processadores Intel Xeon 2.8 GHz
> 4 HD´s 146 GB
> 4 GB de RAM
> Conexão com um Storage e 100 GB de Volume.
>
> Obrigado.
>
>
> Fique com Deus.
> Um grande abraço.
>
> Att.
> Alexandre Brum
>
> --- Em qui, 19/2/09, Wilson Teixeira <wilsonteixeira2...@...> escreveu:
>
> De: Wilson Teixeira <wilsonteixeira2...@...>
> Assunto: Res: [oracle_br] Tuning de Aplicação
> Para: oracle_br@yahoogrupos.com.br
> Data: Quinta-feira, 19 de Fevereiro de 2009, 16:13
>
>
>
>
>
>
> Alexandre,
>
> você pode tentar realizar esta operacao utilizando o comando MERGE.
>
> ____________ _________ _________ __
> De: Alexandre Brum <alexandre_brum@ yahoo.com. br>
> Para: oracle...@yahoogrup os.com.br
> Enviadas: Quinta-feira, 19 de Fevereiro de 2009 11:45:51
> Assunto: [oracle_br] Tuning de Aplicação
>
> Prezados (as)
> Tenho uma procedure que atualiza dados de uma tabela "A" para a
tabela "B". A tabela "A" não possui nenhum índice.
> Foi carregada através do loader. A procedure procura primeiro
inserir o registro da tabela "A" na tabela "B", caso o
> registro exista na tabela "B", então ocorre uma excecption e através
do WHEN DUP_VAL_ON_INDEX a procecure executa um
> Update na tabela "B" com os dados da tabela "A".
> Esse processo está demorando em média 2h. Estou procurando uma
forma de reduzir esse tempo. Se alguém tiver uma sugestão,
> agradeço antecipadamente. Alguns dados importantes sobre esse cenário:
> Tabela "A": 1.343.656 de registros;
> Não possui índices;
>
> Tabela "B": 1.953.678 de registros;
> Índices:
> PK_PRAZO_ACC - Coluna ID_PRAZO_ACC
> UK_PRAZO_ACC - Coluna ID_LINHA_ORDEM_ COMPRA
>
> Na cláusula WHERE do Update o índice UK_PRAZO_ACC
está sendo utilizado;
> No comando Insert está sendo utilizado:
> /*+ APPEND */
> NOLOGGING
>
> No comando Update está sendo utilizado:
> NOLOGGING
>
> Microsoft Windows Server 2003
> Service Pack for Microsoft Windows Server 2003 2
> Oracle9i Database Enterprise/Standard Edition for Windows 9.2.0. 1
> Patch Upgrade for Windows Server 2003 (32-bit) - requires 9.2.01
installation OUI220180
> Patch Set # 9203WIN2K3
> Patch # p3095277_9204_ WINNT
>
> Abaixo segue a procedure na íntegra:
>
> CREATE OR REPLACE PROCEDURE ECARGA.AI_PRAZO_ ACC IS
> --
> CURSOR C_TAB IS
> SELECT /*+ FIRST_ROWS(100000) */
> Prazo_Entrega
> ,MAX(Dt_Criacao) AS Dt_Criacao
> ,Dt_Atualizacao
> ,NR_Contrato
> ,NR_Ordem_Compra
> ,ID_Ordem_Compra
> ,ID_Linha_Ordem_ compra
> ,ID_Organizacao
> ,NR_LINHA_ORDEM_ COMPRA
> FROM A_PRAZO_ACC
> GROUP BY
> Prazo_Entrega
> ,Dt_Atualizacao
> ,NR_Contrato
> ,NR_Ordem_Compra
> ,ID_Ordem_Compra
> ,ID_Linha_Ordem_ compra
> ,ID_Organizacao
> ,NR_LINHA_ORDEM_ COMPRA
> ;
> --
> V_CONT NUMBER := 0; -- CONTADOR GERAL DE REGISTROS
> --
> TYPE FILE_TYPE IS RECORD ( ID BINARY_INTEGER) ;
> --
> ARQ UTL_FILE.FILE_ TYPE; -- ARQUIVO QUE LOGA OS ERROS
> --
> V_LOCAL_ERRO VARCHAR2(6);
> --
> V_DADOS_ERRO VARCHAR2(4000) ;
> --
> procedure log_erro(P_LOCAL_ ERRO varchar2, P_DADOS_ERRO varchar2) is
> --
> ARQ1 UTL_FILE.FILE_ TYPE; -- ARQUIVO QUE LOGA OS ERROS
> --
> begin
> --
> IF NOT UTL_FILE.IS_ OPEN(ARQ1) THEN
> --
> ARQ1 := UTL_FILE.FOPEN( 'C:\DADOS\ ARQUIVOS_ GERADOS_DO_
ORACLE\CARGA\ ERRO_PROC' , 'AI_PRAZO_ACC. TXT','W', 5000);
> --
> END IF;
> --
> UTL_FILE.PUT_ LINE(ARQ1, P_LOCAL_ERRO| |' '||P_DADOS_ERRO| |'
'||SQLERRM);
> --
> end;
> --
> BEGIN
> --
> FOR R_TAB IN C_TAB LOOP
> --
> V_DADOS_ERRO := TO_CHAR(SYSDATE, 'DD/MM/YYYY HH24:MI:SS')
||'ID_Linha_ Ordem_compra= ' || R_TAB.ID_Linha_ Ordem_compra;
> --
> BEGIN
> --
> V_LOCAL_ERRO := 'INSERT';
> --
> INSERT /*+ APPEND */ INTO PRAZO_ACC NOLOGGING
> (ID_PRAZO_ACC
> ,Prazo_Entrega
> ,Dt_Criacao
> ,Dt_Atualizacao
> ,NR_Contrato
> ,NR_Ordem_Compra
> ,ID_Ordem_Compra
> ,ID_Linha_Ordem_ compra
> ,ID_Organizacao
> ,NR_linha_Ordem_ Compra
> )
> VALUES
> (SEQ_PRAZO_ACC. NEXTVAL
> ,R_TAB.Prazo_ Entrega
> ,R_TAB.Dt_Criacao
> ,R_TAB.Dt_Atualizac ao
> ,R_TAB.NR_Contrato
> ,R_TAB.NR_Ordem_ Compra
> ,R_TAB.ID_Ordem_ Compra
> ,R_TAB.ID_Linha_ Ordem_compra
> ,R_TAB.ID_Organizac ao
> ,R_TAB.NR_linha_ Ordem_Compra
> );
> --
> V_CONT := V_CONT + 1;
> --
> IF V_CONT = 100000 THEN
> --
> V_CONT := 0;
> --
> COMMIT;
> --
> END IF;
> --
> EXCEPTION
> --
> WHEN DUP_VAL_ON_INDEX THEN
> --
> BEGIN
> --
> V_LOCAL_ERRO := 'UPDATE';
> --
> UPDATE PRAZO_ACC NOLOGGING
> SET Prazo_Entrega = R_TAB.Prazo_ Entrega
> , Dt_Criacao = R_TAB.Dt_Criacao
> , Dt_Atualizacao = R_TAB.Dt_Atualizaca o
> , NR_Contrato = R_TAB.NR_Contrato
> , NR_Ordem_Compra = R_TAB.NR_Ordem_ Compra
> , ID_Ordem_Compra = R_TAB.ID_Ordem_ Compra
> , ID_Linha_Ordem_ compra = R_TAB.ID_Linha_
Ordem_compra
> , ID_Organizacao = R_TAB.ID_Organizaca o
> , NR_linha_Ordem_ Compra = R_TAB.NR_linha_
Ordem_Compra
> WHERE ID_Linha_Ordem_ compra = R_TAB.ID_Linha_
Ordem_compra;
> --
> if sql%notfound then
> log_erro(V_LOCAL_ ERRO, V_DADOS_ERRO| |' '||SQLERRM);
> end if;
> --
> V_CONT := V_CONT + 1;
> --
> IF V_CONT = 100000 THEN
> --
> V_CONT := 0;
> --
> COMMIT;
> --
> END IF;
> --
> EXCEPTION
> --
> WHEN OTHERS THEN
> --
> IF NOT UTL_FILE.IS_ OPEN(ARQ) THEN
> --
> ARQ := UTL_FILE.FOPEN( 'C:\DADOS\ ARQUIVOS_
GERADOS_DO_ ORACLE\CARGA\ ERRO_PROC' , 'AI_PRAZO_ACC. TXT','W', 5000);
> --
> END IF;
> --
> UTL_FILE.PUT_ LINE(ARQ, V_LOCAL_ERRO| |'
'||V_DADOS_ERRO| |' '||SQLERRM);
> --
> END;
> --
> WHEN OTHERS THEN
> --
> IF NOT UTL_FILE.IS_ OPEN(ARQ) THEN
> --
> ARQ := UTL_FILE.FOPEN( 'C:\DADOS\ ARQUIVOS_ GERADOS_DO_
ORACLE\CARGA\ ERRO_PROC' , 'AI_PRAZO_ACC. TXT','W', 5000);
> --
> END IF;
> --
> UTL_FILE.PUT_ LINE(ARQ, V_LOCAL_ERRO| |' '||V_DADOS_ERRO| |'
'||SQLERRM);
> --
> END;
> --
> END LOOP;
> --
> IF UTL_FILE.IS_ OPEN(ARQ) THEN
> --
> UTL_FILE.FCLOSE( ARQ);
> --
> END IF;
> --
> COMMIT;
> --
> EXCEPTION
> --
> WHEN UTL_FILE.INVALID_ PATH OR UTL_FILE.INVALID_ MODE OR
UTL_FILE.INVALID_ OPERATION OR UTL_FILE.INVALID_ FILEHANDLE OR
UTL_FILE.WRITE_ ERROR THEN
> --
> IF UTL_FILE.IS_ OPEN(ARQ) THEN
> --
> UTL_FILE.PUT_ LINE(ARQ, 'UTL_FILE' ||' '||V_DADOS_ERRO| |'
'||SQLERRM);
> --
> UTL_FILE.FCLOSE( ARQ);
> --
> ELSE
> --
> RAISE_APPLICATION_ ERROR(-20001, SQLERRM);
> --
> END IF;
> --
> COMMIT;
> --
> WHEN OTHERS THEN
> --
> IF NOT UTL_FILE.IS_ OPEN(ARQ) THEN
> --
> UTL_FILE.PUT_ LINE(ARQ, 'OTHERS'| |' '||V_DADOS_ERRO| |'
'||SQLERRM);
> --
> UTL_FILE.FCLOSE( ARQ);
> --
> ELSE
> --
> RAISE_APPLICATION_ ERROR(-20001, SQLERRM);
> --
> END IF;
> --
> COMMIT;
> --
> END;
> /
>
> Fique com Deus.
> Um grande abraço.
>
> Att.
> Alexandre Brum
>
> Veja quais são os assuntos do momento no Yahoo! +Buscados
> http://br.maisbusca dos.yahoo. com
>
> [As partes desta mensagem que não continham texto foram removidas]
>
> Veja quais são os assuntos do momento no Yahoo! +Buscados
> http://br.maisbusca dos.yahoo. com
>
> [As partes desta mensagem que não continham texto foram removidas]
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> Veja quais são os assuntos do momento no Yahoo! +Buscados
> http://br.maisbuscados.yahoo.com
>
> [As partes desta mensagem que não continham texto foram removidas]
>
