Otimizando o INSERT com FORALL - Oracle

E aí galera,
bom vamos dar um exemplo de como carregar muitos registros de uma
forma mais rápida, sem ficar sobrecarregando a área de UNDO do Oracle.
Eu preciso colocar vários clientes em atraso em uma tabela de log.



DECLARE
  /*---------------------------------------------------------------------------------*\   
  | DECLARE: ATUALIZA CLIENTES EM COBRANÇA                     |   
  | DATA CRIAÇÃO:  26/06/2005                                                             |   
  | OBJETIVO: INCLUI A CARGA DO DIA NO ESTOQUE.                |   
  | ANALISTA: GLAUBER                                                                       |   
  | Projeto: Contatos Cobrança                                                                   |   
  |==================================================|   
  | ALTERAÇÃO:                                                                                     |    
  | DATA ALTERAÇÃO:                                                                         |   
  | ANALISTA:                                                                                         |   
  \*---------------------------------------------------------------------------------*/
  
  v_QTD_UPDATE NUMBER(9) := 0;

  CURSOR c_CLIENTES_DIA IS
    SELECT DIA.ID_CONT_COBRANCA,       DIA.CPF,
           DIA.NOME,                                             DIA.STATUSREGISTRO,
           DIA.DATAULTCOMANDO,                   DIA.DATAENTCENTRAL,
           DIA.DATAATRASO,                               DIA.VALOR,
           DIA.UF,                                                    DIA.SEGMENTO,
           DIA.CDPRODLIM,                                 DIA.SUBSPID,
           DIA.COLLECTIONSCOR                      DIA.INDPARCATRASO,
           DIA.QTPARCATRASO,                         DIA.CODCAMPANHA,
           DIA.PRIORIDADE,                               DIA.VLRVAR,
           DIA.TIMEZONE,                                   DIA.CEP,
           DIA.INDQUEBRAPROMESSA,                    DIA.INDCOMPOSICAO,          
      FROM TB_CLIENTES_COBRANCA DIA
     WHERE DIA.DATAATRASO > (SYSDATE-30);
      
  --AQUI EU CRIO UMA VARIÁVEL DO TIPO ARRAY, PARA FICAR IGUAL AO CURSOR
  TYPE ARRAY IS TABLE OF c_CLIENTES_DIA%ROWTYPE INDEX BY PLS_INTEGER;
  S_ARRAY ARRAY;


BEGIN
  dbms_output.put_line('|------------------------------------------------------------|');
  dbms_output.put_line('|Início do processo:' || to_char(SYSDATE, 'dd/mm/yyy hh:mm:ss'));
  dbms_output.put_line('|------------------------------------------------------------|');

  OPEN c_CLIENTES_DIA;

  LOOP
    FETCH c_CLIENTES_DIA BULK COLLECT
      INTO S_ARRAY LIMIT 5000; --AQUI EU LIMITO UM VALOR DE 5000 CLIENTES POR EXECUÇÃO
   
   --AQUI EU FALO PRA ELE SAIR DO LOOP QUANDO NÃO RETORNAR MAIS DADOS
   EXIT WHEN S_ARRAY.COUNT = 0;

    --LEMBRANDO QUE A TABELA DESTINO DEVERÁ TER MESMOS CAMPOS QUE ESTÃO NO CURSOR.
    FORALL i IN 1 .. S_ARRAY.COUNT
      INSERT INTO TB_CONTATOS_COBRANCA_LOG VALUES S_ARRAY (i);

    -- AQUI EU CONTO A QUANTIDADE DE REGISTROS INSERIDOS
    v_QTD_INSERT := v_QTD_INSERT + SQL%ROWCOUNT;
    -- AQUI EU LIMPO A VARIÁVEL PARA SER USADA NOVAMENTE E NÃO FICAR LIXO.
    S_ARRAY.DELETE;

    COMMIT;

  END LOOP;
  CLOSE c_CLIENTES_DIA;

  COMMIT;
  dbms_output.put_line('|Quantidade de registros retornados: ' ||  v_QTD_INSERT);

  dbms_output.put_line('|------------------------------------------------------------|');
  dbms_output.put_line('|Fim do processo:' || to_char(SYSDATE, 'dd/mm/yyyy hh:mm:ss'));
  dbms_output.put_line('|------------------------------------------------------------|');

EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line('|------------------------------------------------------------|');
    dbms_output.put_line('| MENSAGEM ORACLE : ' || SQLERRM);
    dbms_output.put_line('|------------------------------------------------------------|');
    dbms_output.put_line('|Fim do processo:' || to_char(SYSDATE, 'dd/mm/yyyy hh:mm:ss'));
    dbms_output.put_line('|------------------------------------------------------------|');
END;

Blz galera, esse é um exemplo fácil para você criar um insert em lote, vlw

qualquer dúvida só postar nos comentários.

Utilizando Hint's para performance das query's.

Olá galera, Vamos ver alguns Hint's que podem nos ajudar em nossas consultas. Muitas pessoas não gostam de utilizar, outras utilizam sempre, mas sempre é bom analisar sua query pra saber se irá otimizar seu tempo de resposta ou piora-lo. Pois é preciso conhecer e analisar, o que será melhor para o Oracle.

Temos vários hint's, "sugestões" algumas já obsoletas, mas vários que podem nos ajudar... eles são declarados como comentários.

Como por exemplo o first_rows, já me ajudou, mas é preciso fazer consultas analisando sua query, também com explan para verificar como o Oracle se comporta, esse hint ele força o uso de índice, para otimizador um caminho que retorna a 1º linha ou a quantidade de linhas que escolher mais rapidamente.

Exemplo:
select /*+ first_rows (500) */ coluna_1, coluna_2 from tabela;

O hint /*+ full_rows (tabela) */ força um scan completo na tabela, custo do acesso do índice e dos registros pode ser maior do que simplesmente ler a tabela inteira. Este hint também pode causar resultados inesperados, como varrer a tabela interia em ordem diferente da ordem de acesso, se for uma tabela com centenas de dados, não é aconselhável.

Exemplo:
select /*+ full_rows (tabela_user) */ coluna_1, coluna_2 from tabela_user where rownum <= 10;

A partir do Oracle 11g, temos uma view com mais de 250 Hint's v$sql_hint, se fosse de todo mau, não existiria mais.

Existem vários outros HINT's, mas entenda, sempre é bom se aprofundar no conhecimento, pois não têm uma receita de bolo, e sim uma melhor prática para cada caso. Vlw galera, qualquer dúvida é só compartilhar.

Referências: https://community.oracle.com/welcome

Como deletar registros duplicados no Oracle.

É muito comum termos esse tipo de problema em nossas tabelas, então segue uma uma query bem simples para apagar estes registros:

Select para localizar duplicados

  
    select campo,campo1,count(*) 
     from tabela having count(*) > 1 
    group by campo,campo1 

Como deletar duplicados

  
      delete from tab p1 
   where rowid < (select max(rowid) 
                    from tab1 p2 
                   where p1.primary_key = p2.primary_key); 

Qualquer dúvida é só comentar
ou enviar um e-mail. Abraços.

Aumentando o tamanho da TABLESPACE

E aí galera, tudo bem ? Bom vamos lá, muitos profissionais se deparam com problemas quando o tamanho da TABLESPACE esgota, aí na hora vira um furdunço, e não dá pra fazer mais nada, então para um profissional não ter problemas como este:

"ORA-01653: unable to extend table SUPER.CLI_PROCESSSOS by 128 in tablespace LEGAL_DATA" "ORA-06512: at line 4"

 É necessário que ele faça um monitoramento delas, pelo Enterprise Manager ou através das santas query's como essa:

  
 SELECT D.TABLESPACE_NAME "Nome",DF.FILE_NAME "Local do Arq", D.STATUS "Status",          
        TO_CHAR((A.BYTES / 1024 / 1024),'99,999,990.900')      "Tamanho (M)",
        TO_CHAR(((A.BYTES-DECODE(F.BYTES,NULL,0,F.BYTES))/1024/1024),'99,999,990.900') "Usado (M)"
  FROM SYS.DBA_TABLESPACES D
 INNER JOIN SYS.DBA_DATA_FILES DF  ON DF.TABLESPACE_NAME  = D.TABLESPACE_NAME
 INNER JOIN SYS.SM$TS_AVAIL A      ON A.TABLESPACE_NAME   = D.TABLESPACE_NAME
  FULL OUTER JOIN SYS.SM$TS_FREE F ON F.TABLESPACE_NAME   = D.TABLESPACE_NAME
 ORDER BY D.TABLESPACE_NAME; 

Com o retorno, você pode remanejar espaço entre elas, como por exemplo, tirar de uma e colocar em outra com o comando: Diminuindo a TABLESPACE SATIDAT1.DBF.

  
alter database datafile 'C:\ORACLEXE\ORADATA\XE\SSJR\SATIDAT1.DBF' resize 100M

Aumentando o tamanho da TABLESPACE SSJRDAT1.DBF.

  
alter database datafile 'C:\ORACLEXE\ORADATA\XE\SSJR\SSJRDAT1.DBF' resize 300M

Qualquer dúvida ou sugestão comentem, ou mandem e-mail, Abraços.