Datatype numérico e carácter, será que existem outros?

Tipo de dado

Algumas pessoas vão ler o título deste artigo e pensar que ele foi feito para leigos em tecnologia da informação, mas quem trabalha com banco de dados sabe que muitas aplicações que existem no mercado utilizam somente os Datatypes numérico e carácter, esta prática afeta negativamente o desempenho da aplicação pois o Otimizador não tem como deduzir, por exemplo, que a aplicação esta usando um campo numérico para armazenar datas.

Neste artigo vamos simular uma situação onde um campo numérico é carregado com datas e verificar porque o Otimizador fica vendido nesta situação e cria um plano de execução com baixo desempenho.

Para esta simulação prática vamos passar pelas seguintes etapas:

1) Criar uma tabela onde o campo DT_CAD é do tipo numérico
2) Executar uma consulta na tabela criada utilizando o campo DT_CAD na cláusula WHERE
3) Recriar a tabela da etapa 1 alterando o campo DT_CAD para o tipo DATA
4) Executar a mesma consulta novamente com campo DT_CAD na cláusula WHERE

 

1) Criar uma tabela onde o campo DT_CAD é numérico

Vamos criar os objetos necessários para realizar a nossa simulação.

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
SQL > SELECT * FROM V$VERSION where rownum < 2;

BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production              0

SQL >
SQL > CREATE TABLE dbtw061 (matr number, cod_cid number, dt_cad number, descr varchar(100));

Tabela criada.

SQL >
SQL > INSERT INTO dbtw061
  2     SELECT rownum,
  3            MOD(rownum,1000),
  4            TO_NUMBER(TO_CHAR(sysdate-TRUNC(DBMS_RANDOM.VALUE(0,10000)), 'YYYYMMDD')),
  5            DBMS_RANDOM.STRING('A', 100)
  6       FROM dual
  7    CONNECT BY level <=1000000;

1000000 linhas criadas.

SQL >
SQL >
SQL > COMMIT;

Commit concluído.

SQL >
SQL >
SQL > CREATE INDEX dbtw061_matr_idx ON dbtw061(matr);

Índice criado.

SQL > CREATE INDEX dbtw061_dt_cad_idx on dbtw061(dt_cad);

Índice criado.

SQL >
SQL >
SQL > EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname=> USER, tabname=> 'DBTW061', cascade=> true, method_opt=> 'FOR ALL COLUMNS SIZE AUTO',force=> true,degree=> 16);

Procedimento PL/SQL concluído com sucesso.

SQL >

 

2) Executar uma consulta com o campo DT_CAD na cláusula WHERE

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
SQL > SELECT /* dbtw-061.2 */ *
  2    FROM dbtw061
  3   WHERE dt_cad BETWEEN 19991231 and 20000101;

      MATR    COD_CID     DT_CAD DESCR
---------- ---------- ---------- ----------------------------------------------------------------------------------------------------
      2239        239   19991231 GsraCoSnqAbFoxmqzKmWplZFWMcvSKqsGcRyuhVJIwFoZFXnQfduEiqdEUWVyJgxdsEifIAHHrKPqwtnsGqXthWLwnTsrCGmpJXF
      8676        676   19991231 UwRSVzGqpeALcbxsvfEUQBeWyjBBQveZdNsghrtywdzvzlGxflymDdHSfTUlulPSssrJSqqrIEPQnkUdUAoTYmsJhMSbcJRoWrES
      8127        127   20000101 kUPMpafQiRacIGnOdzcUGnMkwGnqdxQmrDPKDrDtcmsgbjSQHvMxIMVgbDDTYvDaVpnLyrIGmVAhXfAECAnmKlTSGSebmHTxcgrZ
      .
      .
      .
    984189        189   20000101 CkRBQEPmoBhWKhbxAkKOBjTdQJiBgptyRRRDpQEDtpLyaaeIvZUeWyOTtzDwuJkFSyaAygBuaVMbJxmLiDgCoIRKRUAkapJMolyZ

206 linhas selecionadas.

SQL >
SQL >
SQL > column sql_id new_value m_sql_id
SQL > column child_number new_value m_child_no
SQL >
SQL > SELECT sql_id, child_number
  2    FROM v$sql
  3   WHERE sql_text LIKE '%dbtw-061.2%'
  4     AND sql_text NOT LIKE '%v$sql%';

SQL_ID        CHILD_NUMBER
------------- ------------
1ngp47mwuk766            0

SQL >
SQL >
SQL > SELECT *
  2    FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'ALLSTATS LAST'));
antigo   2:   FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'ALLSTATS LAST'))
novo   2:   FROM TABLE (dbms_xplan.display_cursor ('1ngp47mwuk766',         0,'ALLSTATS LAST'))

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  1ngp47mwuk766, child number 0
-------------------------------------
SELECT /* dbtw-061.2 */ *   FROM dbtw061  WHERE dt_cad BETWEEN 19991231
and 20000101

Plan hash value: 2108755988

---------------------------------------------------------------------------------------
| Id  | Operation         | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |         |      1 |        |    206 |00:00:00.06 |   16755 |
|*  1 |  TABLE ACCESS FULL| DBTW061 |      1 |  32997 |    206 |00:00:00.06 |   16755 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(("DT_CAD"<=20000101 AND "DT_CAD">=19991231))


19 linhas selecionadas.

SQL >
SQL >
SQL > -------------------------------------------------------------------
SQL > -- SELETIVIDADE = (vval2-vval1)/((maxval-minval)+(2 * Density))
SQL > -------------------------------------------------------------------
SQL >
SQL > SELECT MIN(dt_cad) min,
  2         MAX(dt_cad) max
  3    FROM dbtw061;

       MIN        MAX
---------- ----------
  19920308   20190724

SQL >
SQL > SELECT column_name,
  2         density
  3    FROM user_tab_cols
  4   WHERE table_name='DBTW061'
  5     AND column_name='DT_CAD';

COLUMN_NAME        DENSITY
--------------- ----------
DT_CAD          ,000097694

SQL >
SQL > SELECT (20000101-19991231)/(20190724-19920308)+(2*0.000097694) SELETIVIDADE
  2    FROM dual;

SELETIVIDADE
-------------
   ,032996702

SQL >
SQL > -------------------------------------------------------------------
SQL > -- CARDINALIDADE = SELECTIVIDADE * NUM LINHAS DA TABELA
SQL > -------------------------------------------------------------------
SQL >
SQL > SELECT ROUND(0.032996702*1000000) CARDINALIDADE
  2    FROM dual;

CARDINALIDADE
-------------
        32997

SQL >

 

Observe no plano de execução que o Otimizador estimou que seriam selecionadas 32997 linhas na tabela e por isso escolheu fazer uma operação TABLE ACCESS FULL, mas na verdade foram selecionadas apenas 206 linhas, devido a esse erro de estimativa o Otimizador deixou de usar o índice que seria a melhor opção para selecionar 206 linhas na tabela.
Após o plano de execução podemos observar os cálculos realizados pelo Otimizador para estimar o numero de linhas que seriam lidos na tabela, o algoritmo do Otimizador acaba fornecendo um resultado errado pois faz uma subtração numérica dos valores máximo e minimo na tabela, bem como a subtração do intervalo fornecido na cláusula WHERE, porem o resultado da subtração de campos do tipo numérico são diferentes dos resultados de subtração de campos do tipo DATA, e no nosso caso apesar do tipo do campo ser numérico o conteúdo desse campo é data.

 

3) Recriar a tabela alterando o campo DT_CAD

 

Agora vamos recriar a tabela alterando o campo DT_CAD para o tipo DATA e verificar se o Otimizador é capaz de criar um plano de execução melhor.

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
SQL > DROP TABLE dbtw061;

Tabela eliminada.

SQL >
SQL > CREATE TABLE dbtw061 (matr number, cod_cid number, dt_cad date, descr varchar(100));

Tabela criada.

SQL >
SQL > INSERT INTO dbtw061
  2     SELECT rownum,
  3            MOD(rownum,1000),
  4            TRUNC(sysdate-TRUNC(DBMS_RANDOM.VALUE(0,9999))),
  5            DBMS_RANDOM.STRING('A', 100)
  6       FROM dual
  7    CONNECT BY level <=1000000;

1000000 linhas criadas.

SQL >
SQL >
SQL > COMMIT;

Commit concluído.

SQL >
SQL >
SQL > CREATE INDEX dbtw061_matr_idx ON dbtw061(matr);

Índice criado.

SQL > CREATE INDEX dbtw061_dt_cad_idx on dbtw061(dt_cad);

Índice criado.

SQL >
SQL >
SQL > EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname=> USER, tabname=> 'DBTW061', cascade=> true, method_opt=> 'FOR ALL COLUMNS SIZE AUTO',force=> true,degree=> 16);

Procedimento PL/SQL concluído com sucesso.

SQL >

 

4) Executar a mesma consulta novamente

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
SQL > SELECT /* dbtw-061.3 */ *
  2    FROM dbtw061
  3   WHERE dt_cad BETWEEN to_date('19991231','yyyymmdd') and to_date('20000101','yyyymmdd');

      MATR    COD_CID DT_CAD   DESCR
---------- ---------- -------- ----------------------------------------------------------------------------------------------------
    360801        801 31/12/99 ZkPmOyWkbcopuUumsNeUnzFnOgoIUxcBXNhkshyeQSLwAyrqbguIAQYnQDvjwylzqWanBsRIfqRoxwepNiQUOBpoRdUpgjdGolkC
    368200        200 31/12/99 LouQSxQRimDBTBWeLVFyOqUuhXAryLDptcYkEYaFJJQwWRoejpToXxftLvHRJTHcOcmXZwjVfWvlHIYRMTgWJjjFXfCNrhhYlCUw
    365402        402 31/12/99 uUDeJYLmLfPwsStjVsnfxHjHLasvPsSEfVTFCUBnwYXSRkaxuAqIRrsGuwZYQeqrAiBumuXSQLyOrKEGajDgjLKaRseTyrfxGYND
     .
     .
     .
     52714        714 01/01/00 tSSQcxTRqeFriPpPcJehJpKMYiVwopfTUOrszyotZlCVRmCoFGDodKxRBnMQnykLswjiNdIBvgCacNBdpYlRzckVCDJnxYERilDe

179 linhas selecionadas.

SQL >
SQL >
SQL > column sql_id new_value m_sql_id
SQL > column child_number new_value m_child_no
SQL >
SQL > SELECT sql_id, child_number
  2    FROM v$sql
  3   WHERE sql_text LIKE '%dbtw-061.3%'
  4     AND sql_text NOT LIKE '%v$sql%';

SQL_ID        CHILD_NUMBER
------------- ------------
fbx73g4dgzv04            0

SQL >
SQL >
SQL > SELECT *
  2    FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'ALLSTATS LAST'));
antigo   2:   FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'ALLSTATS LAST'))
novo   2:   FROM TABLE (dbms_xplan.display_cursor ('fbx73g4dgzv04',         0,'ALLSTATS LAST'))

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  fbx73g4dgzv04, child number 0
-------------------------------------
SELECT /* dbtw-061.3 */ *   FROM dbtw061  WHERE dt_cad BETWEEN
to_date('19991231','yyyymmdd') and to_date('20000101','yyyymmdd')

Plan hash value: 1720299405

--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name               | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                    |      1 |        |    179 |00:00:00.01 |     193 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| DBTW061            |      1 |    300 |    179 |00:00:00.01 |     193 |
|*  2 |   INDEX RANGE SCAN                  | DBTW061_DT_CAD_IDX |      1 |    300 |    179 |00:00:00.01 |      15 |
--------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("DT_CAD">=TO_DATE(' 1999-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "DT_CAD"<=TO_DATE('
              2000-01-01 00:00:00'
, 'syyyy-mm-dd hh24:mi:ss'))


21 linhas selecionadas.

SQL >
SQL > -------------------------------------------------------------------
SQL > -- SELETIVIDADE = (vval2-vval1)/((maxval-minval)+(2 * Density))
SQL > -------------------------------------------------------------------
SQL >
SQL > SELECT MIN(dt_cad) min,
  2         MAX(dt_cad) max
  3    FROM dbtw061;

MIN      MAX
-------- --------
09/03/92 24/07/19

SQL >
SQL > SELECT column_name,
  2         density
  3    FROM user_tab_cols
  4   WHERE table_name='DBTW061'
  5     AND column_name='DT_CAD';

COLUMN_NAME       DENSITY
-------------- ----------
DT_CAD          ,00009984

SQL >
SQL >
SQL > SELECT (to_date('20000101','yyyymmdd')-to_date('19991231','yyyymmdd'))/(to_date('20190723','yyyymmdd')-to_date('19920308','yyyymmdd'))+(2*0.00010001) SELETIVIDADE
  2    FROM dual;

SELETIVIDADE
------------
   ,00030004

SQL >
SQL > -------------------------------------------------------------------
SQL > -- CARDINALIDADE = SELETIVIDADE * NUM LINHAS DA TABELA
SQL > -------------------------------------------------------------------
SQL >
SQL > SELECT ROUND(0.00030004*1000000) CARDINALIDADE
  2    FROM dual;

CARDINALIDADE
-------------
          300

SQL >

 

Observe no plano de execução que o Otimizador desta vez estimou que seriam lidas 300 linhas da tabela, valor bem próximo da quantidade de linhas selecionadas ao final da consulta (179 linhas), e como o Otimizador estimou uma pequeno número de linhas ele decidiu fazer a leitura da tabela utilizando índice, com isso o desempenho da consulta foi muito superior ao da primeira consulta executada na etapa 2.
Logo abaixo do plano de execução temos os cálculos realizados pelo Otimizador para estimar a leitura de 300 linhas da tabela.

CONCLUSÃO

Os tipos de campos que definimos para os dados que vão popular as tabelas, além de servir para validar as informações inseridas no banco de dados, servem para aumentar a precisão dos algoritmos utilizados pelo Otimizador que por sua vez é capaz de gerar planos de execução com ótimo desempenho. Quando decidimos ignorar a utilização dos tipos de campos adequados aos dados da tabela estamos sabotando o desempenho da aplicação.

Referências

https://docs.oracle.com/database/121/

 
 
 
Promo-D75L

video-02
 
 
 
 
 
 
 
 
 
 
 
 

Deixe uma resposta

O seu endereço de email não será publicado. Campos obrigatórios são marcados com *