Menu de navegação

[klarcParticipante]

Preciso atualizar 134 bilhoes de linhas onde o campo flg_ori está vazio e realizar commit a cada 10mil linhas. Segue detalhes do erro:

declare
cursor C1 is select rowid as IDLINHA from ecfid_bk where flg_ori is null;
type v_typ is table of C1%rowtype;
v_arr v_typ;
begin

open C1;
loop
fetch C1 bulk collect into v_arr limit 1000;
forall i in 1..v_arr.count update ecfid_bk set flg_ori=’ ‘ where rowid = v_arr.idlinha(i);
commit;
exit when C1%notfound;
end loop;
close C1;
end;
/

Error at line 1
ORA-06550: line 10, column 83:
PLS-00302: component ‘IDLINHA’ must be declared
ORA-06550: line 10, column 83:
PLS-00302: component ‘IDLINHA’ must be declared
ORA-06550: line 10, column 77:
PL/SQL: ORA-00904: “V_ARR”.”IDLINHA”: invalid identifier
ORA-06550: line 10, column 31:
PL/SQL: SQL Statement ignored

[fsitjaParticipante]

Se você realmente quer modificar 134 Bilhões de linhas, não faça com update. Forall mais update vai levar uma eternidade para concluir.

Crie uma nova tabela já com os valores corretos, por meio de um “create table as select” (vulgo CTAS), de preferência usando direct-path insert (usando o hint /+APPEND/) e nologging para fazer o processo andar mais rápido. Depois renomeie a nova tabela com o nome da tabela antiga, recrie índices e constraints e drop na velha.

Só para esclarecer o erro que você obteve, está trocado o local do índice do array. O correto seria algo como:


v_arr(i).idlinha;


Mesmo assim, se não estou enganado, ele vai dar um exception “PLS-00436: implementation restriction: cannot reference fields of BULK In-BIND table of records”. Para contornar teria que usar MULTISET e CAST no cursor, usando object types em vez de type no declare, e fazer um select INTO comum em vez de bulk collect.

De qualquer forma, não se preocupe, essa solução com FORALL não é escalável para fazer um update dessa magnitude. Crie uma uma tabela nova que é o melhor caminho.

[VitorLeandroParticipante]

134 Bilhoes de linhas?? Você trabalha no GOOGLE?
(heheh) Brincadeira!

[fsitjaParticipante]

[quote=”VitorLeandro”:3j7xuhxd]134 Bilhoes de linhas?? Você trabalha no GOOGLE?
(heheh) Brincadeira![/quote]

Que nada… o Google trabalha para ele! 😆

[burgaParticipante]

[quote=”fsitja”:17rwloy2][quote=”VitorLeandro”:17rwloy2]134 Bilhoes de linhas?? Você trabalha no GOOGLE?
(heheh) Brincadeira![/quote]

Que nada… o Google trabalha para ele! 😆 [/quote]

CHUCK NORRIS MODE ON; 8) 8)

@topic (pra não dizer que floodei) 😆
o erro que está dando é o que o Fsitja já disse:
v_arr(i).idlinha;

[hudsonaParticipante]

[quote=”VitorLeandro”:2r1g4neb]134 Bilhoes de linhas?? Você trabalha no GOOGLE?
(heheh) Brincadeira![/quote]

Curiosidade:

Qual o tamanho dessa tabela ??

rsrs

[klarcParticipante]

[quote=”fsitja”:2bgztt3m]Se você realmente quer modificar 134 Bilhões de linhas, não faça com update. Forall mais update vai levar uma eternidade para concluir.

Crie uma nova tabela já com os valores corretos, por meio de um “create table as select” (vulgo CTAS), de preferência usando direct-path insert (usando o hint /+APPEND/) e nologging para fazer o processo andar mais rápido. Depois renomeie a nova tabela com o nome da tabela antiga, recrie índices e constraints e drop na velha.

Só para esclarecer o erro que você obteve, está trocado o local do índice do array. O correto seria algo como:


v_arr(i).idlinha;


Mesmo assim, se não estou enganado, ele vai dar um exception “PLS-00435: DML statement without BULK In-BIND cannot be used inside FORALL”. Para contornar teria que usar MULTISET e CAST no cursor, usando object types em vez de type no declare, e fazer um select INTO comum em vez de bulk collect.

De qualquer forma, não se preocupe, essa solução com FORALL não é escalável para fazer um update dessa magnitude. Crie uma uma tabela nova que é o melhor caminho.[/quote]

Tu está me dizendo que é mais rápido criar uma nova tabela e depois recriar os indices???

Rapaz, isso é ambiente de produção. Tabela de 200GB.

Enfim, já tinha testado dessa forma que você colocou v_arr(i).idlinha, não funciona. Preciso do código pronto e funcionando… 😉

Existe outra forma usando BULK e commit a cada 10000 mil linhas???

Desde já agradeço o empenho de Todos.

[fsitjaParticipante]

Bom, se você quer fazer um update mesmo (ou milhões deles), quem sou eu para impedir, certo… dá para tentar, sem ter ideia de quanto tempo vai levar.

Não entendi sua regra para preencher a coluna nova… É o mesmo valor para todas as linhas?

Qual versão do Oracle você está rodando? Para versões antes do 11g há a tal restrição de implementação que mencionei acima, por isso você não vai conseguir usar forall. Mas de toda forma dá para contornar.

Misturei um pouco as coisas, para update nem precisa usar gambiarra com multiset e cast mas vai precisar criar object type de qualquer forma (os create type não permitem usar %rowtype ou %type… 👿 ).

Passe a lógica de preencher a coluna que dá para fazer algo mais preciso.
—————————————————————————————————————————————————

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.4.0
Connected as FSITJA

SQL>
SQL> set serveroutput on
SQL> create table t1 (id_t1 number(10) primary key, tipo varchar2(20));

Table created
SQL> insert into t1 (id_t1, tipo) values (1, 'X');

1 row inserted
SQL> insert into t1 (id_t1, tipo) values (2, 'X');

1 row inserted
SQL> insert into t1 (id_t1, tipo) values (3, 'X');

1 row inserted
SQL> insert into t1 (id_t1, tipo) values (4, 'X');

1 row inserted
SQL> insert into t1 (id_t1, tipo) values (5, 'X');

1 row inserted
SQL> commit;

Commit complete
SQL> create or replace type typ_t1 as object (id_t1 number(10),
2 tipo varchar2(20))
3 /

Type created
SQL> create or replace type typ_tab_t1 is table of typ_t1
2 /

Type created
SQL> declare
2 tab_t1 typ_tab_t1;
3 cursor cur_t1 is
4 select typ_t1(id_t1, tipo) from t1;
5 begin
6 open cur_t1;
7 loop
8 fetch cur_t1 bulk collect
9 into tab_t1 limit 2;
10 update t1 set t1.tipo = 'Y'
11 where t1.id_t1 in (select t.id_t1 from table(tab_t1) t);
12 dbms_output.put_line('Update em ' || sql%rowcount || ' linhas.');
13 commit;
14 exit when cur_t1%notfound;
15 end loop;
16 dbms_output.put_line('Total de linhas atualizadas: ' || cur_t1%rowcount);
17 close cur_t1;
18 end;
19 /

Update em 2 linhas.
Update em 2 linhas.
Update em 1 linhas.
Total de linhas atualizadas: 5

PL/SQL procedure successfully completed
SQL> drop type typ_tab_t1;

Type dropped
SQL> drop type typ_t1;

Type dropped
SQL> select *
2 from t1;

ID_T1 TIPO
----------- --------------------
1 Y
2 Y
3 Y
4 Y
5 Y

SQL>


Provavelmente no seu caso teria que colocar um filtro no where do cursor pegando apenas as linhas com flg_ori is null caso tenha que continuar por motivo de erro ou qualquer interrupção no processamento.

[klarcParticipante]

Senhores Segue resolução.

*Mostra também os intervalos entre os commits.
*Nesse exemplo o commit é efetuado a cada 500 mil linhas atualizadas(limit 500000;).
*Como a tabela é grande fiz teste apenas para 10 milhões de linhas (rownum < 10000000;)


set timing on
set autotrace on
set serveroutput on
declare
cursor C1 is select rowid as IDLINHA from .

where flg_ori is null and rownum < 10000000;
TYPE NumList IS TABLE OF rowid;
v_arr NumList ;
--type v_typ is table of C1%rowtype;
--v_arr v_typ;
errors NUMBER;
dml_errors EXCEPTION;
v_datahora varchar2(300);
--PRAGMA exception_init(dml_errors, -24381);
begin
dbms_output.enable(2000000);
v_datahora:=to_char(sysdate,'dd/mm/yy hh24:mi:ss');
dbms_output.put_line('Inicio : '||v_datahora);
open C1;
loop
fetch C1 bulk collect into v_arr limit 500000;
forall i in v_arr.first .. v_arr.last
update . set flg_ori=' ' where rowid = v_arr(i);
commit;
v_datahora:=to_char(sysdate,'dd/mm/yy hh24:mi:ss');
dbms_output.put_line('500K : '||v_datahora);
exit when C1%notfound;
end loop;
close C1;
v_datahora:=to_char(sysdate,'dd/mm/yy hh24:mi:ss');
dbms_output.put_line('Fim : '||v_datahora);
EXCEPTION
WHEN dml_errors THEN
errors := SQL%BULK_EXCEPTIONS.COUNT;
dbms_output.put_line('Number of errors is ' || errors);
FOR i IN 1..errors LOOP
dbms_output.put_line('Error ' || i || ' occurred during '||
'iteration ' || SQL%BULK_EXCEPTIONS(i).ERROR_INDEX);
dbms_output.put_line('Oracle error is ' ||
SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
END LOOP;
end;
/


exemplo da saida:

Execution Plan
———————————————————-
0 UPDATE STATEMENT Optimizer Mode=CHOOSE
1 0 UPDATE TESTE.TABELA_BK
2 1 TABLE ACCESS FULL TESTE.TABELA_BK

Statistics
———————————————————-
392 recursive calls
1102779 db block gets
2797 consistent gets
2072 physical reads
241807224 redo size
0 Parallel operations downgraded 50 to 75 pct
0 Parallel operations downgraded 25 to 50 pct
0 Parallel operations downgraded 1 to 25 pct
0 PX remote messages recv’d
0 buffer is pinned count
999999 rows processed

Inicio : 18/03/10 18:07:19
500K : 18/03/10 18:08:19
500K : 18/03/10 18:09:09
500K : 18/03/10 18:09:40
500K : 18/03/10 18:10:45
500K : 18/03/10 18:11:34
500K : 18/03/10 18:12:15
500K : 18/03/10 18:13:04
500K : 18/03/10 18:13:35
500K : 18/03/10 18:14:07
500K : 18/03/10 18:14:49
500K : 18/03/10 18:15:20
500K : 18/03/10 18:16:01
500K : 18/03/10 18:16:39
500K : 18/03/10 18:17:32
500K : 18/03/10 18:18:07
500K : 18/03/10 18:18:39
500K : 18/03/10 18:19:14
500K : 18/03/10 18:19:45
500K : 18/03/10 18:20:18
500K : 18/03/10 18:20:45
Fim : 18/03/10 18:20:45
PL/SQL procedure successfully completed.
Elapsed: 00:13:30.80 Para 10 Milhoes de rows

Assim é muito mais rápido. Testem e vejam o quanto eficiente é usar BULK Collect.

Com isso evita-se alto uso de UNDO, pode-se, nesse caso, fazer aos poucos – como no exemplo, fiz para 10 milhões de linhas – para gerenciar a quantidade de archives gerados.

Agradeço a todos que participaram e que direto ou indiretamente ajudaram.

[fsitjaParticipante]

Imaginei que não daria para usar o workaround do rowid pois assumi que o novo valor da coluna no update seria em função dos atributos da própria linha sendo atualizada. Geralmente essa solução com rowid se usa para FORALL com Delete que só precisa identificar qual é a linha, sem olhar para o conteúdo dela.

Ficou legal, muito bom mesmo. Você chegou a verificar o consumo na PGA para ver se o lote de 10 milhões não está muito pesado, competindo com recursos em produção?

[vieriParticipante]

quer dizer que a tabela tem 134 Bilhões de linhas com 200Gb???
no minimo interessante.

SQL> select num_rows from dba_tables where table_name = ‘TB_HISTORICO_ESTOQUE’;

NUM_ROWS

1155397176

SQL> select sum(bytes/1024/1024/1024) as GB from dba_segments where segment_name = ‘TB_HISTORICO_ESTOQUE’;

GB

100.400757

Beleza utilizando paralelismo, em um update em banco de produção?

Que especie de sistema armazena essa quantidade de linhas,
e o consumo de backup, por acaso excluem ela no RMAN?

E a geração de archive no momento do update , já criou uma rotina pra te mandar sms de 10 em 10 minutos pra ver se estoura?

E os latch’s que serão provocados e o excesso de waits por redo,
pga allocation, switch dos logfiles.. etc..etc..?

Experimente fazer um CTAS e depois fazer um
insert de volta com hint duplo.
/By_pass_recursive_check Append/

Se tem uma estrutura dessas com certeza tem mais de 200Gb disponíveis tranquilamente para fazer o de-para com o pé nas costas…

[Autor]

Posts