Social Icons

8 de ago. de 2011

Qual tipo de dado devo usar: CHAR, VARCHAR ou VARCHAR2?


Olá pessoal,

     No artigo de hoje irei comentar sobre um assunto que representa uma dúvida muito comum para quem vai criar tabelas no Banco de Dados (BD) Oracle:  "Qual tipo de dado devo usar: CHAR, VARCHAR ou VARCHAR2?". Essa dúvida surge, normalmente, quando um Desenvolvedor precisa criar colunas para armazenar texto (caracteres alfanuméricos).  Para ter a resposta, precisamos entender primeiro as diferenças entre os tipos de dados CHARVARCHAR e VARCHAR2:

1- VARCHAR  
     Atualmente (até a versão 11G do Oracle Database), VARCHAR nada mais é do que um sinônimo para VARCHAR2 (desde o Oracle8), por isso a Oracle recomenda não utilizar este tipo de dado. Existe a possibilidade de VARCHAR ser utilizado em versões futuras para ter uma semântica ou característica diferente de VARCHAR2, mas mesmo, estando tudo igual atualmente, utilize VARCHAR2 ao invés de VARCHARpara evitar possíveis problemas futuros.
  
2- VARCHAR2
     VARCHAR2 armazena caracteres alfanuméricos de tamanho variável, entre 1 e 4000 bytes ou caracteres. O tamanho padrão desta coluna é especificado em bytes. Agora vem a pergunta, qual a diferença entre armazenar bytes ou caracteres? Bom... quando utilizamos caracteres multibyte, como por exemplo UTF-8 (para representar caracteres específicos em múltiplas linguagens), 1 único caractere pode ser armazenado em até 3 bytes. Nestes casos, uma palavra contendo caracteres especiais, como por exemplo, FÁBIO, poderá ter mais bytes do que caracteres (a letra Á, internamente será armazenada em 2 ou 3 bytes), por isso, recomenda-se, nestes casos, especificar o armazenamento da coluna em caracteres, ao invés de bytes.

3- CHAR
     A única diferença entre CHAR e VARCHAR2 é que CHAR armazena caracteres alfanuméricos de tamanho fixo, entre 1 e 2000 bytes ou caracteres. Se você armazenar o sobrenome PRADO em uma coluna CHAR(50), a coluna conterá a string PRADO + 45 espaços em branco que são adicionados automaticamente para preencher o tamanho total da coluna.      

     Agora que já sabemos as diferenças e particularidades de cada tipo de dados, vamos à pergunta que é o título deste artigo: Qual tipo de dado devo usar: CHAR, VARCHAR ou VARCHAR2?
     Resposta: Sabendo que VARCHAR é um sinônimo para VARCHAR2 e que a própria Oracle não recomenda utilizar este tipo de dados, esqueça que ele existe, utilize VARCHAR2 ao invés de VARCHAR. Agora que já sabemos que não devemos utilizar VARCHAR, sobraram apenas 2 tipos de dados para comparar: CHAR e VARCHAR2. Thomas Kyte, um dos maiores especialistas em Oracle do mundo, recomenda evitar CHAR em seu site ASK TOM (ver referências). Resumindo o que ele explica, em termos de armazenamento CHAR nada mais é do que um VARCHAR2 com espaços em branco adicionais. Neste caso, se você cria uma coluna com CHAR ou VARCHAR2 de tamanho 50 e armazena uma palavra que tenha somente 40 caracteres, utilizando VARCHAR2 você poderá economizar espaço em disco e você ainda terá consultas mais rápidas.
    
    Diante de todos estes dados e informações, podemos chegar à conclusão de que devemos evitar CHAR e VARCHAR, utilizando sempre  VARCHAR2 para armazenar valores alfanuméricos de até 4000 caracteres (limite de tamanho do Oracle Database 11G).

     Segue abaixo um conjunto de scripts para serem executados no SQL Developer (ou em sua ferramenta preferida), para permitir a comparação de performance de INSERTs e SELECTs entre uma tabela com 1 coluna VARCHAR2 e outra tabela idêntica com 1 coluna CHAR. O script do Passo 4 permite comparar a performance de inserções nas 2 tabelas. Os scripts dos Passos 6 e 7 permitem comparar a performance de consultas e os scripts dos Passos 8 e 9 permitem verificar a qtde. de bytes que foram armazenados em cada coluna:

Passo 1: Criando as tabelas de origem de dados:
     CREATE TABLE CLIENTE_CHAR (ID_CLIENTE NUMBER, NM_CLIENTE CHAR(50), NM_ENDERECO CHAR(100));
     CREATE TABLE CLIENTE_VARCHAR2 (ID_CLIENTE NUMBER, NM_CLIENTE VARCHAR2(50), NM_ENDERECO VARCHAR2(100));

Passo 2: Populando as tabelas de origem de dados:
SET SERVEROUTPUT ON
DECLARE  
  V_COUNT         NUMBER; 
BEGIN 
  FOR V_COUNT IN 1..99999
  LOOP 
          -- executa insert c/ loop em cursor
          INSERT  INTO CLIENTE_CHAR (ID_CLIENTE, NM_CLIENTE, NM_ENDERECO)
          VALUES  (V_COUNT, 'NOME ' || TO_CHAR(V_COUNT), 'ENDERECO ' || TO_CHAR(V_COUNT));
         
          INSERT  INTO CLIENTE_VARCHAR2 (ID_CLIENTE, NM_CLIENTE, NM_ENDERECO)
          VALUES  (V_COUNT, 'NOME ' || TO_CHAR(V_COUNT), 'ENDERECO ' || TO_CHAR(V_COUNT));
  END LOOP; 
 
  COMMIT;
END;

Passo 3: Criando as tabelas teste para comparar performance:
CREATE TABLE CLIENTE_VARCHAR2_B AS SELECT * FROM CLIENTE_VARCHAR2 WHERE 1=2;
CREATE TABLE CLIENTE_CHAR_B AS SELECT * FROM CLIENTE_CHAR WHERE 1=2;

Passo 4: Calculando o tempo de INSERT nas tabelas teste a partir da leitura das tabelas de origens de dados:
SET SERVEROUTPUT ON
DECLARE
  L_START         NUMBER;  
BEGIN
  L_START := DBMS_UTILITY.GET_TIME;     
  -- executa insert c/ loop em cursor
  INSERT  INTO CLIENTE_CHAR_B
  SELECT * FROM CLIENTE_CHAR;
  DBMS_OUTPUT.PUT_LINE('Tempo total de INSERT na tabela CLIENTE_CHAR2 : ' || ROUND((DBMS_UTILITY.GET_TIME - L_START)/100,2) || 's');   
 
  L_START := DBMS_UTILITY.GET_TIME; 
  INSERT  INTO CLIENTE_VARCHAR2_B
  SELECT * FROM CLIENTE_VARCHAR2; 
  DBMS_OUTPUT.PUT_LINE('Tempo total de INSERT na tabela CLIENTE_VARCHAR2 : ' || ROUND((DBMS_UTILITY.GET_TIME - L_START)/100,2) || 's');   
END;

Passo 5: Coletando estatísticas das tabelas teste para gerar plano de execução otimizado:
     ANALYZE TABLE CLIENTE_VARCHAR2_B COMPUTE STATISTICS;
     ANALYZE TABLE CLIENTE_CHAR_B COMPUTE STATISTICS;

Passo 6: Verificando tempo de consulta na tabela teste que tem colunas CHAR:
     EXPLAIN PLAN FOR
          SELECT * FROM  CLIENTE_CHAR_B WHERE NM_CLIENTE = 'NOME 1'; --
     SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

Passo 7: Verificando tempo de consulta na tabela teste que tem colunas VARCHAR2:
     EXPLAIN PLAN FOR
          SELECT * FROM  CLIENTE_VARCHAR2_B WHERE NM_CLIENTE = 'NOME 1'; --
     select * from table(DBMS_XPLAN.DISPLAY);

Passo 8: Verificando qtde de bytes armazenados na coluna nm_cliente da tabela teste com CHAR:
     SELECT DUMP(NM_CLIENTE) FROM CLIENTE_CHAR_B;

Passo 9: Verificando qtde. de bytes armazenados na coluna nm_cliente da tabela teste com VARCHAR2:
     SELECT DUMP(NM_CLIENTE) FROM CLIENTE_VARCHAR2_B;

   
Obs.: Após efetuar os testes de comparação, execute os comandos abaixo para apagar as tabelas criadas nos passos anteriores:
     DROP TABLE CLIENTE_VARCHAR2 PURGE;
     DROP TABLE CLIENTE_VARCHAR2_B PURGE;
     DROP TABLE CLIENTE_CHAR PURGE;
     DROP TABLE CLIENTE_CHAR_B PURGE;
     
 
COMENTÁRIOS FINAIS:
     Em testes que eu fiz, o tempo de consulta na tabela que tem a coluna VARCHAR2 foi 4.6 vezes mais rápido e o tempo de inserção na tabela que tem a coluna VARCHAR2 foi 3.5 vezes mais rápido.
           
CONCLUSÃO:
     Evite CHAR ou VARCHAR. Não utilize CHAR nem para criar colunas que armazenarão apenas 1 caractere de tamanho fixo. Atualmente não há vantagens em utilizar este tipo de dado. Utilize sempre VARCHAR2. Este sim poderá oferecer vantagens de performance e armazenamento quando a definição do tamanho da coluna for maior que o valor armazenado.

19 comments:

  1. Olá Fábio !

    É raro eu utilizar o CHAR, quando utilizado geralmente é CHAR(1).

    Então CHAR(1) VS VARCHAR2(1)? Quem é melhor?

    Difícil de dizer, realizei o seu procedimento, e no passo 4 em uma ocasião CHAR(1) ganhou, e nas outras empatava, no passo 6 e 7 empata sempre...

    Passo 4 em CHAR(1) VS VARCHAR2(1):
    Tempo total de INSERT na tabela CLIENTE_CHAR2 : ,06s
    Tempo total de INSERT na tabela CLIENTE_VARCHAR2 : ,06s

    Creio que fica visível a diferença de desempenho ao aumentar o tamanho, CHAR(2000) VS VARCHAR2(2000).

    CHAR(2000) VS VARCHAR2(2000):
    Tempo total de INSERT na tabela CLIENTE_CHAR2 : 54,17s
    Tempo total de INSERT na tabela CLIENTE_VARCHAR2 : ,35s

    Obviamente, os passo 6 e 7 em CHAR(2000) VS VARCHAR2(2000), o VARCHAR2(2000) ganha em disparado...

    Bom, pelo que conclui, quanto maior o tamanho, mais recomendado o uso de VARCHAR2...

    Espero ter complementado o seu artigo...

    Att,

    Sakamoto

    MyTraceLog - Registro de um DBA
    http://mytracelog.blospot.com

    ResponderExcluir
  2. Olá Sakamoto, obrigado pela colaboração!

    Se vc for gravar dados de tamanho fixo, tanto faz usar char ou varchar2. No caso de CHAR(1) x VARCHAR2(1) ambos vão gravar sempre 1 caractere e neste caso o tempo de alteração ou consulta deverá ser sempre o mesmo (com pequenas variações de tempo para mais ou menos entre um processo ou outro, que pode ser ocasionado por outros processos concorrentes no BD).

    Se vc for gravar dados de tamanho variável, VARCHAR2 será sempre melhor! A recomendação é usar sempre VARCHAR2 pq no mínimo ele vai ser melhor qdo vc tiver que gravar dados de tamanho variável. Nas minhas referëncias veja o site do Tom Kyte. Ele nao recomenda usar CHAR para 1 caractere (CHAR(1). Se vc usar o SQL Developer 3.0 para criar tabelas verá que ao definir colunas esta ferramenta separa em um nível acima os principais tipos de dado, entre os quais nao está incluso CHAR.

    ResponderExcluir
  3. Olá!
    Certa vez ouvi um programador postgre-sql dizendo que em alguns casos o uso do char era válido.

    A teoria dele dizia que os dados CHAR como tinham tamanho fixo ficariam armazenados no mesmo cilindro do hd. Como a busca ocorria no mesmo cilindro a movimentação das cabeças de leitura seria menor e a resposta de leitura mais rápida.

    Será que em algum caso essa técnica é válida?

    Abs.
    Paulo Gervásio.

    ResponderExcluir
  4. Paulo,

    Acredito que isso não aconteça no Oracle, pois testes de performance não demonstram ganho em usar CHAR. Eu já fui desenvolvedor e me lembro bem que há uns 7 anos atrás eu também preferia CHAR no SQL SERVER 2000, pq em rotinas de carga, ele apresentava desempenho muito superior. Nas versões atuais do Oracle isso não acontece.

    []s

    ResponderExcluir
  5. Opa Fabio tudo bem?

    Mas me diz uma coisa caso essa coluna sofra muitas alterações.
    Por exemplo, era de 10 caracteres passou a ser 20 depois 30 e etc.

    Os dados não ficaram mais espalhados no disco, e assim perdendo performace. Isso no caso de VARCHAR2.

    ResponderExcluir
    Respostas
    1. Cesar,
      A partir do 10G a Oracle recomenda utilizar um esquema de gerenciamento de segmentos chamado Automatic Segment Storage Management (ASSM), que funciona muito bem para a maior parte do sistemas e que por padrão mantém sempre 10% (PCTFREE =10%) do tamanho total dos blocos de uma tabela, livres, para posteriores UPDATEs. Se os updates de uma tabela, em geral acrescentam mais que 10% de dados, recomendo alterar PCTFREE para evitar linhas encadeadas na situação que vc mencionou, OK?

      []s

      Excluir
    2. Entendi Fabio.

      Muito bom o post, ja encaminhei para o pessoal aqui ler.

      Abraços

      Excluir
  6. Fábio, qual campo usar para quando preciso ter mais de 4000 caracteres? Quero armazenar alguns contratos e eles possuem bem mais que 4000 caracteres. Utilizo o Oracle 10g.

    ResponderExcluir
  7. Carlos, até o Oracle 11G vc deve usar o tipo de dado CLOB. No 12c o limite do VARCHAR2 aumentou de 4000 p/ 32767, portanto, no 12c, talvez o VARCHAR2 ainda atenda a sua necessidade.

    []s

    ResponderExcluir
  8. Boa tarde Fábio, estou montando uma procedure com insert em 4 tabelas originando de um EDI, sendo 104 campos do tipo varchar2, banco 11g, e estou achando estranho a performance, o banco e de testes, só eu utilizo, e algumas vezes e rápido, outras vezes demora a inserção linha a linha, só não encontrei motivo aparente, o mesmo arquivo, em momentos diferentes apresenta uma diferença absurda de performance, teria alguma dica de onde procurar? Obrigado.

    ResponderExcluir
    Respostas
    1. Emerson, enquanto a carga está ocorrendo, faça uma consulta na V$SESSION filtrando por sessões ativas, localize a sua sessão e veja o valor da coluna EVENT. Ela te indicará o que está ocorrendo no processamento naquele momento.

      []s

      Excluir
  9. Olá....
    Fábio...
    Uma ajuda com tipos de dados...
    Instalei o Oracle 11g (Windows 7) e utilizo o SQL Developer...
    Quando é executado o CREATE TABLE (ex)
    CREATE TABLE TB_CLIENTES
    (
    ID_CLIENTE INTEGER CONSTRAINT PK_CLIENTE PRIMARY KEY,
    NOME VARCHAR2(50) NOT NULL );

    Executa e cria a tabela sem problemas... mas,
    como resultado final o ID_CLIENTE fica com o tipo Number(38,0) !
    É normal ! Ou está faltando algo.

    PS: Iniciante em Oracle.
    Grato
    Antonio

    ResponderExcluir
    Respostas
    1. Antonio, no Oracle não existe um tipo de dado nativo INTEGER para ser utilizado em SQL. O INTEGER é na verdade um apelido para o NUMBER(38,0) e ele só existe no Oracle para atender exigências de padronização ANSI/ISO, OK?

      Excluir
  10. Olá Fábio, ótimo artigo, como sempre.
    Tenho uma questão, Sybase ASE, campo char(6) que é PK em uma tabela e FK em outras 243 tabelas. O valor não é fixo.
    Preciso aumentar este campo para (8), estou pensando em alterar para varchar. O que você recomenda, manter char(8) ou alterar para varchar(8)?
    Tenho certo receio na troca para varchar, o campo ficar com espaços para preencher os 6 que existem atualmente e começar a dar problema nas consultas envolvendo os relacionamentos.

    Att,
    Fernando Cambiaghi

    ResponderExcluir
    Respostas
    1. Olá Fernando, tudo bem? Não conheço o Sybase e portanto não posso neste caso te dar alguma recomendação. O que eu posso te dizer é que aquilo que se aplica no Oracle não necessariamente se aplica no Sybase, ok?

      Excluir
  11. Olá, Fábio, bom dia!
    Estou com uma dificuldade em converter tabelas de uma versão velha do Oracle e outros bancos para o Oracle 11g. Campos acentuados estão sendo ignorados na conversão, pois ocupam mais bytes que está indicado na tabela. Um exemplo: Em uma tabela de senha com 10 caracteres da versão velha, cuja senha é "manutenção" não pode ser gravado no Oracle 11g na mesma tabela. No Oracle 11 eu criei o campo da tabela como N_SENHA VARCHAR2(10). Infelizmente não posso mudar para mais bytes porque o sistema trabalha com vários bancos de dados, de acordo com cada cliente. Como gravar o conteúdo que já existe em tabelas de versões antigas do Oracle e até de outras fontes (MySQL, SQL Server, Paradox) como sendo "caracter" e não "byte", mantendo o mesmo tamanho dos campos das tabelas ?
    Só para reforçar: Meu sistema já funciona, está em vários clientes e cada tabela tem uma estrutura padrão que não pode ser diferente no tamanho dos campos de acordo com o banco. O que pode ser diferente é o tipo de dado. Se puder me responder ou enviar um e-mail eu agradeço: valdeci.lunguinho@gmail.com

    ResponderExcluir
    Respostas
    1. Valdeci, desculpa a demora, mas só vi hoje essa sua pergunta. Seu problema não me parece difícil de resolver, você tem que apenas especificar nas colunas de dados do tipo VARCHAR se o tamanho deve considerar a qtde. de bytes ou caracteres, como no exemplo abaixo:
      COLUNA1 VARCHAR2(100 CHAR)

      []s

      Excluir
  12. Boa noite, Fabio!
    Tenho um problema com uma aplicação rodando com o banco de dados Oracle 12, em versão de desenvolvimento, e ao carregar os dados necessários para o teste da aplicação, algumas tabelas que tem campos VARCHAR2(255) não estão suportando as strings UTF8, pelo fato de excederem os bytes do campo. Ouvi dizer que o ORACLE pode ser configurado para que os campos VARCHAR2(xxx BYTE) sejam por default VARCHAR2(xxx CHAR). Como devo configurar o banco para que isso aconteça?

    ResponderExcluir
    Respostas
    1. Boa tarde, basta executar um ALTER TABLE nas tabelas desejadas redefinindo o tipo de dado das colunas com o tipo que vc mesmo especifiou: VARCHAR2(xxx CHAR).

      Excluir

 

LINKS ÚTEIS

Total de visualizações de página

Seguidores

Meu One Drive (antigo Sky Drive)