{"id":2026,"date":"2019-08-11T22:55:19","date_gmt":"2019-08-12T01:55:19","guid":{"rendered":"http:\/\/dbtimewizard.com.br\/blog\/?p=2026"},"modified":"2019-11-11T18:29:57","modified_gmt":"2019-11-11T21:29:57","slug":"datatype-numerico-e-caracter-sera-que-existem-outros","status":"publish","type":"post","link":"http:\/\/dbtimewizard.com.br\/blog\/datatype-numerico-e-caracter-sera-que-existem-outros\/","title":{"rendered":"Datatype num\u00e9rico e car\u00e1cter, ser\u00e1 que existem outros?"},"content":{"rendered":"

Algumas pessoas v\u00e3o ler o t\u00edtulo deste artigo e pensar que ele foi feito para leigos em tecnologia da informa\u00e7\u00e3o, mas quem trabalha com banco de dados sabe que muitas aplica\u00e7\u00f5es que existem no mercado utilizam somente os Datatypes num\u00e9rico e car\u00e1cter, esta pr\u00e1tica afeta negativamente o desempenho da aplica\u00e7\u00e3o pois o Otimizador n\u00e3o tem como deduzir, por exemplo, que a aplica\u00e7\u00e3o esta usando um campo num\u00e9rico para armazenar datas. <\/span><\/p>\n

Neste artigo vamos simular uma situa\u00e7\u00e3o onde um campo num\u00e9rico \u00e9 carregado com datas e verificar porque o Otimizador fica vendido nesta situa\u00e7\u00e3o e cria um plano de execu\u00e7\u00e3o com baixo desempenho.<\/span><\/p>\n

Para esta simula\u00e7\u00e3o pr\u00e1tica vamos passar pelas seguintes etapas:<\/span><\/p>\n

1) Criar uma tabela onde o campo DT_CAD \u00e9 do tipo num\u00e9rico <\/span>
\n2) Executar uma consulta na tabela criada utilizando o campo DT_CAD na cl\u00e1usula WHERE<\/span>
\n3) Recriar a tabela da etapa 1 alterando o campo DT_CAD para o tipo DATA<\/span>
\n4) Executar a mesma consulta novamente com campo DT_CAD na cl\u00e1usula WHERE<\/span><\/p>\n

 <\/p>\n

1) Criar uma tabela onde o campo DT_CAD \u00e9 num\u00e9rico<\/span><\/h2>\n

Vamos criar os objetos necess\u00e1rios para realizar a nossa simula\u00e7\u00e3o.<\/span><\/p>\n

 <\/p>\n

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
<\/div><\/td>
SQL ><\/span> SELECT *<\/span> FROM V$VERSION<\/span> where rownum <<\/span> 2<\/span>;<\/span>
\n
\nBANNER                                                                               CON_ID
\n--------------------------------------------------------------------------------<\/span> ----------<\/span>
\nOracle Database 12c Enterprise Edition Release 12.<\/span>2.<\/span>0.<\/span>1.<\/span>0 -<\/span> 64bit Production              0<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> CREATE TABLE dbtw061 (<\/span>matr number,<\/span> cod_cid number,<\/span> dt_cad number,<\/span> descr varchar(<\/span>100<\/span>)<\/span>)<\/span>;<\/span>
\n
\nTabela criada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> INSERT INTO dbtw061
\n  2<\/span>     SELECT rownum,<\/span>
\n  3<\/span>            MOD(<\/span>rownum,<\/span>1000<\/span>)<\/span>,<\/span>
\n  4<\/span>            TO_NUMBER(<\/span>TO_CHAR(<\/span>sysdate-<\/span>TRUNC(<\/span>DBMS_RANDOM.<\/span>VALUE(<\/span>0<\/span>,<\/span>10000<\/span>)<\/span>)<\/span>,<\/span> 'YYYYMMDD'<\/span>)<\/span>)<\/span>,<\/span>
\n  5<\/span>            DBMS_RANDOM.<\/span>STRING(<\/span>'A'<\/span>,<\/span> 100<\/span>)<\/span>
\n  6<\/span>       FROM dual
\n  7<\/span>    CONNECT BY level <=<\/span>1000000<\/span>;<\/span>
\n
\n1000000<\/span> linhas criadas.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span>
\nSQL ><\/span> COMMIT;<\/span>
\n
\nCommit conclu\u00eddo<\/span>.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span>
\nSQL ><\/span> CREATE INDEX dbtw061_matr_idx ON dbtw061(<\/span>matr)<\/span>;<\/span>
\n
\n\u00cdndice criado.<\/span>
\n
\nSQL ><\/span> CREATE INDEX dbtw061_dt_cad_idx on dbtw061(<\/span>dt_cad)<\/span>;<\/span>
\n
\n\u00cdndice criado.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span>
\nSQL ><\/span> EXEC<\/span><\/a> DBMS_STATS.<\/span>GATHER_TABLE_STATS(<\/span>ownname=><\/span> USER,<\/span> tabname=><\/span> 'DBTW061'<\/span>,<\/span> cascade=><\/span> true<\/span>,<\/span> method_opt=><\/span> 'FOR ALL COLUMNS SIZE AUTO'<\/span>,<\/span>force=><\/span> true<\/span>,<\/span>degree=><\/span> 16<\/span>)<\/span>;<\/span>
\n
\nProcedimento PL\/<\/span>SQL conclu\u00eddo<\/span> com sucesso.<\/span>
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

2) Executar uma consulta com o campo DT_CAD na cl\u00e1usula WHERE<\/span><\/h2>\n

 <\/p>\n

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
<\/div><\/td>
SQL ><\/span> SELECT \/* dbtw-061.2 *\/<\/span> *<\/span>
\n  2<\/span>    FROM dbtw061
\n  3<\/span>   WHERE dt_cad BETWEEN 19991231<\/span> and 20000101<\/span>;<\/span>
\n
\n      MATR    COD_CID     DT_CAD DESCR
\n----------<\/span> ----------<\/span> ----------<\/span> ----------------------------------------------------------------------------------------------------<\/span>
\n      2239<\/span>        239<\/span>   19991231<\/span> GsraCoSnqAbFoxmqzKmWplZFWMcvSKqsGcRyuhVJIwFoZFXnQfduEiqdEUWVyJgxdsEifIAHHrKPqwtnsGqXthWLwnTsrCGmpJXF
\n      8676<\/span>        676<\/span>   19991231<\/span> UwRSVzGqpeALcbxsvfEUQBeWyjBBQveZdNsghrtywdzvzlGxflymDdHSfTUlulPSssrJSqqrIEPQnkUdUAoTYmsJhMSbcJRoWrES
\n      8127<\/span>        127<\/span>   20000101<\/span> kUPMpafQiRacIGnOdzcUGnMkwGnqdxQmrDPKDrDtcmsgbjSQHvMxIMVgbDDTYvDaVpnLyrIGmVAhXfAECAnmKlTSGSebmHTxcgrZ
\n      .<\/span>
\n      .<\/span>
\n      .<\/span>
\n    984189<\/span>        189<\/span>   20000101<\/span> CkRBQEPmoBhWKhbxAkKOBjTdQJiBgptyRRRDpQEDtpLyaaeIvZUeWyOTtzDwuJkFSyaAygBuaVMbJxmLiDgCoIRKRUAkapJMolyZ
\n
\n206<\/span> linhas selecionadas.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span>
\nSQL ><\/span> column sql_id new_value m_sql_id
\nSQL ><\/span> column child_number new_value m_child_no
\nSQL ><\/span>
\nSQL ><\/span> SELECT sql_id,<\/span> child_number
\n  2<\/span>    FROM v$sql<\/span>
\n  3<\/span>   WHERE sql_text LIKE '%dbtw-061.2%'<\/span>
\n  4<\/span>     AND sql_text NOT LIKE '%v$sql%'<\/span>;<\/span>
\n
\nSQL_ID        CHILD_NUMBER
\n-------------<\/span> ------------<\/span>
\n1ngp47mwuk766            0<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span>
\nSQL ><\/span> SELECT *<\/span>
\n  2<\/span>    FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'&m_sql_id'<\/span>,&<\/span>m_child_no,<\/span>'ALLSTATS LAST'<\/span>)<\/span>)<\/span>;<\/span>
\nantigo   2<\/span>:<\/span>   FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'&m_sql_id'<\/span>,&<\/span>m_child_no,<\/span>'ALLSTATS LAST'<\/span>)<\/span>)<\/span>
\nnovo   2<\/span>:<\/span>   FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'1ngp47mwuk766'<\/span>,<\/span>         0<\/span>,<\/span>'ALLSTATS LAST'<\/span>)<\/span>)<\/span>
\n
\nPLAN_TABLE_OUTPUT
\n------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<\/span>
\nSQL_ID  1ngp47mwuk766,<\/span> child number 0<\/span>
\n-------------------------------------<\/span>
\nSELECT \/* dbtw-061.2 *\/<\/span> *<\/span>   FROM dbtw061  WHERE dt_cad BETWEEN 19991231<\/span>
\nand 20000101<\/span>
\n
\nPlan hash<\/span><\/a> value:<\/span> 2108755988<\/span>
\n
\n---------------------------------------------------------------------------------------<\/span>
\n|<\/span> Id  |<\/span> Operation         |<\/span> Name    |<\/span> Starts |<\/span> E-<\/span>Rows |<\/span> A-<\/span>Rows |<\/span>   A-<\/span>Time<\/span><\/a>   |<\/span> Buffers |<\/span>
\n---------------------------------------------------------------------------------------<\/span>
\n|<\/span>   0<\/span> |<\/span> SELECT STATEMENT  |<\/span>         |<\/span>      1<\/span> |<\/span>        |<\/span>    206<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.06<\/span> |<\/span>   16755<\/span> |<\/span>
\n|*<\/span>  1<\/span> |<\/span>  TABLE ACCESS FULL|<\/span> DBTW061 |<\/span>      1<\/span> |<\/span>  32997<\/span> |<\/span>    206<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.06<\/span> |<\/span>   16755<\/span> |<\/span>
\n---------------------------------------------------------------------------------------<\/span>
\n
\nPredicate Information (<\/span>identified by operation id)<\/span>:<\/span>
\n---------------------------------------------------<\/span>
\n
\n   1<\/span> -<\/span> filter(<\/span>(<\/span>"DT_CAD"<\/span><=<\/span>20000101<\/span> AND "DT_CAD"<\/span>>=<\/span>19991231<\/span>)<\/span>)<\/span>
\n
\n
\n19<\/span> linhas selecionadas.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span>
\nSQL ><\/span> -------------------------------------------------------------------<\/span>
\nSQL ><\/span> --<\/span> SELETIVIDADE =<\/span> (<\/span>vval2-<\/span>vval1)<\/span>\/<\/span>(<\/span>(<\/span>maxval-<\/span>minval)<\/span>+<\/span>(<\/span>2<\/span> *<\/span> Density)<\/span>)<\/span>
\nSQL ><\/span> -------------------------------------------------------------------<\/span>
\nSQL ><\/span>
\nSQL ><\/span> SELECT MIN<\/span><\/a>(<\/span>dt_cad)<\/span> min<\/span><\/a>,<\/span>
\n  2<\/span>         MAX<\/span><\/a>(<\/span>dt_cad)<\/span> max<\/span><\/a>
\n  3<\/span>    FROM dbtw061;<\/span>
\n
\n       MIN<\/span><\/a>        MAX<\/span><\/a>
\n----------<\/span> ----------<\/span>
\n  19920308<\/span>   20190724<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> SELECT column_name,<\/span>
\n  2<\/span>         density
\n  3<\/span>    FROM user_tab_cols
\n  4<\/span>   WHERE table_name=<\/span>'DBTW061'<\/span>
\n  5<\/span>     AND column_name=<\/span>'DT_CAD'<\/span>;<\/span>
\n
\nCOLUMN_NAME        DENSITY
\n---------------<\/span> ----------<\/span>
\nDT_CAD          ,<\/span>000097694<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> SELECT (<\/span>20000101<\/span>-<\/span>19991231<\/span>)<\/span>\/<\/span>(<\/span>20190724<\/span>-<\/span>19920308<\/span>)<\/span>+<\/span>(<\/span>2<\/span>*<\/span>0.000097694<\/span>)<\/span> SELETIVIDADE
\n  2<\/span>    FROM dual;<\/span>
\n
\nSELETIVIDADE
\n-------------<\/span>
\n   ,<\/span>032996702<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> -------------------------------------------------------------------<\/span>
\nSQL ><\/span> --<\/span> CARDINALIDADE =<\/span> SELECTIVIDADE *<\/span> NUM LINHAS DA TABELA
\nSQL ><\/span> -------------------------------------------------------------------<\/span>
\nSQL ><\/span>
\nSQL ><\/span> SELECT ROUND<\/span><\/a>(<\/span>0.032996702<\/span>*<\/span>1000000<\/span>)<\/span> CARDINALIDADE
\n  2<\/span>    FROM dual;<\/span>
\n
\nCARDINALIDADE
\n-------------<\/span>
\n        32997<\/span>
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

Observe no plano de execu\u00e7\u00e3o que o Otimizador estimou que seriam selecionadas 32997 linhas na tabela e por isso escolheu fazer uma opera\u00e7\u00e3o TABLE ACCESS FULL, mas na verdade foram selecionadas apenas 206 linhas, devido a esse erro de estimativa o Otimizador deixou de usar o \u00edndice que seria a melhor op\u00e7\u00e3o para selecionar 206 linhas na tabela.<\/span>
\nAp\u00f3s o plano de execu\u00e7\u00e3o podemos observar os c\u00e1lculos 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\u00e7\u00e3o num\u00e9rica dos valores m\u00e1ximo e minimo na tabela, bem como a subtra\u00e7\u00e3o do intervalo fornecido na cl\u00e1usula WHERE, porem o resultado da subtra\u00e7\u00e3o de campos do tipo num\u00e9rico s\u00e3o diferentes dos resultados de subtra\u00e7\u00e3o de campos do tipo DATA, e no nosso caso apesar do tipo do campo ser num\u00e9rico o conte\u00fado desse campo \u00e9 data.<\/span><\/p>\n

 <\/p>\n

3) Recriar a tabela alterando o campo DT_CAD<\/span><\/h2>\n

 <\/p>\n

Agora vamos recriar a tabela alterando o campo DT_CAD para o tipo DATA e verificar se o Otimizador \u00e9 capaz de criar um plano de execu\u00e7\u00e3o melhor.<\/span><\/p>\n

 <\/p>\n

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
<\/div><\/td>
SQL ><\/span> DROP TABLE dbtw061;<\/span>
\n
\nTabela eliminada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> CREATE TABLE dbtw061 (<\/span>matr number,<\/span> cod_cid number,<\/span> dt_cad date<\/span><\/a>,<\/span> descr varchar(<\/span>100<\/span>)<\/span>)<\/span>;<\/span>
\n
\nTabela criada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> INSERT INTO dbtw061
\n  2<\/span>     SELECT rownum,<\/span>
\n  3<\/span>            MOD(<\/span>rownum,<\/span>1000<\/span>)<\/span>,<\/span>
\n  4<\/span>            TRUNC(<\/span>sysdate-<\/span>TRUNC(<\/span>DBMS_RANDOM.<\/span>VALUE(<\/span>0<\/span>,<\/span>9999<\/span>)<\/span>)<\/span>)<\/span>,<\/span>
\n  5<\/span>            DBMS_RANDOM.<\/span>STRING(<\/span>'A'<\/span>,<\/span> 100<\/span>)<\/span>
\n  6<\/span>       FROM dual
\n  7<\/span>    CONNECT BY level <=<\/span>1000000<\/span>;<\/span>
\n
\n1000000<\/span> linhas criadas.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span>
\nSQL ><\/span> COMMIT;<\/span>
\n
\nCommit conclu\u00eddo<\/span>.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span>
\nSQL ><\/span> CREATE INDEX dbtw061_matr_idx ON dbtw061(<\/span>matr)<\/span>;<\/span>
\n
\n\u00cdndice criado.<\/span>
\n
\nSQL ><\/span> CREATE INDEX dbtw061_dt_cad_idx on dbtw061(<\/span>dt_cad)<\/span>;<\/span>
\n
\n\u00cdndice criado.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span>
\nSQL ><\/span> EXEC<\/span><\/a> DBMS_STATS.<\/span>GATHER_TABLE_STATS(<\/span>ownname=><\/span> USER,<\/span> tabname=><\/span> 'DBTW061'<\/span>,<\/span> cascade=><\/span> true<\/span>,<\/span> method_opt=><\/span> 'FOR ALL COLUMNS SIZE AUTO'<\/span>,<\/span>force=><\/span> true<\/span>,<\/span>degree=><\/span> 16<\/span>)<\/span>;<\/span>
\n
\nProcedimento PL\/<\/span>SQL conclu\u00eddo<\/span> com sucesso.<\/span>
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

4) Executar a mesma consulta novamente<\/span><\/h2>\n

 <\/p>\n

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
<\/div><\/td>
SQL ><\/span> SELECT \/* dbtw-061.3 *\/<\/span> *<\/span>
\n  2<\/span>    FROM dbtw061
\n  3<\/span>   WHERE dt_cad BETWEEN to_date(<\/span>'19991231'<\/span>,<\/span>'yyyymmdd'<\/span>)<\/span> and to_date(<\/span>'20000101'<\/span>,<\/span>'yyyymmdd'<\/span>)<\/span>;<\/span>
\n
\n      MATR    COD_CID DT_CAD   DESCR
\n----------<\/span> ----------<\/span> --------<\/span> ----------------------------------------------------------------------------------------------------<\/span>
\n    360801<\/span>        801<\/span> 31<\/span>\/<\/span>12<\/span>\/<\/span>99<\/span> ZkPmOyWkbcopuUumsNeUnzFnOgoIUxcBXNhkshyeQSLwAyrqbguIAQYnQDvjwylzqWanBsRIfqRoxwepNiQUOBpoRdUpgjdGolkC
\n    368200<\/span>        200<\/span> 31<\/span>\/<\/span>12<\/span>\/<\/span>99<\/span> LouQSxQRimDBTBWeLVFyOqUuhXAryLDptcYkEYaFJJQwWRoejpToXxftLvHRJTHcOcmXZwjVfWvlHIYRMTgWJjjFXfCNrhhYlCUw
\n    365402<\/span>        402<\/span> 31<\/span>\/<\/span>12<\/span>\/<\/span>99<\/span> uUDeJYLmLfPwsStjVsnfxHjHLasvPsSEfVTFCUBnwYXSRkaxuAqIRrsGuwZYQeqrAiBumuXSQLyOrKEGajDgjLKaRseTyrfxGYND
\n     .<\/span>
\n     .<\/span>
\n     .<\/span>
\n     52714<\/span>        714<\/span> 01<\/span>\/<\/span>01<\/span>\/<\/span>00<\/span> tSSQcxTRqeFriPpPcJehJpKMYiVwopfTUOrszyotZlCVRmCoFGDodKxRBnMQnykLswjiNdIBvgCacNBdpYlRzckVCDJnxYERilDe
\n
\n179<\/span> linhas selecionadas.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span>
\nSQL ><\/span> column sql_id new_value m_sql_id
\nSQL ><\/span> column child_number new_value m_child_no
\nSQL ><\/span>
\nSQL ><\/span> SELECT sql_id,<\/span> child_number
\n  2<\/span>    FROM v$sql<\/span>
\n  3<\/span>   WHERE sql_text LIKE '%dbtw-061.3%'<\/span>
\n  4<\/span>     AND sql_text NOT LIKE '%v$sql%'<\/span>;<\/span>
\n
\nSQL_ID        CHILD_NUMBER
\n-------------<\/span> ------------<\/span>
\nfbx73g4dgzv04            0<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span>
\nSQL ><\/span> SELECT *<\/span>
\n  2<\/span>    FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'&m_sql_id'<\/span>,&<\/span>m_child_no,<\/span>'ALLSTATS LAST'<\/span>)<\/span>)<\/span>;<\/span>
\nantigo   2<\/span>:<\/span>   FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'&m_sql_id'<\/span>,&<\/span>m_child_no,<\/span>'ALLSTATS LAST'<\/span>)<\/span>)<\/span>
\nnovo   2<\/span>:<\/span>   FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'fbx73g4dgzv04'<\/span>,<\/span>         0<\/span>,<\/span>'ALLSTATS LAST'<\/span>)<\/span>)<\/span>
\n
\nPLAN_TABLE_OUTPUT
\n------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<\/span>
\nSQL_ID  fbx73g4dgzv04,<\/span> child number 0<\/span>
\n-------------------------------------<\/span>
\nSELECT \/* dbtw-061.3 *\/<\/span> *<\/span>   FROM dbtw061  WHERE dt_cad BETWEEN
\nto_date(<\/span>'19991231'<\/span>,<\/span>'yyyymmdd'<\/span>)<\/span> and to_date(<\/span>'20000101'<\/span>,<\/span>'yyyymmdd'<\/span>)<\/span>
\n
\nPlan hash<\/span><\/a> value:<\/span> 1720299405<\/span>
\n
\n--------------------------------------------------------------------------------------------------------------------<\/span>
\n|<\/span> Id  |<\/span> Operation                           |<\/span> Name               |<\/span> Starts |<\/span> E-<\/span>Rows |<\/span> A-<\/span>Rows |<\/span>   A-<\/span>Time<\/span><\/a>   |<\/span> Buffers |<\/span>
\n--------------------------------------------------------------------------------------------------------------------<\/span>
\n|<\/span>   0<\/span> |<\/span> SELECT STATEMENT                    |<\/span>                    |<\/span>      1<\/span> |<\/span>        |<\/span>    179<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>     193<\/span> |<\/span>
\n|<\/span>   1<\/span> |<\/span>  TABLE ACCESS BY INDEX ROWID BATCHED|<\/span> DBTW061            |<\/span>      1<\/span> |<\/span>    300<\/span> |<\/span>    179<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>     193<\/span> |<\/span>
\n|*<\/span>  2<\/span> |<\/span>   INDEX RANGE<\/span><\/a> SCAN                  |<\/span> DBTW061_DT_CAD_IDX |<\/span>      1<\/span> |<\/span>    300<\/span> |<\/span>    179<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>      15<\/span> |<\/span>
\n--------------------------------------------------------------------------------------------------------------------<\/span>
\n
\nPredicate Information (<\/span>identified by operation id)<\/span>:<\/span>
\n---------------------------------------------------<\/span>
\n
\n   2<\/span> -<\/span> access(<\/span>"DT_CAD"<\/span>>=<\/span>TO_DATE(<\/span>' 1999-12-31 00:00:00'<\/span>,<\/span> 'syyyy-mm-dd hh24:mi:ss'<\/span>)<\/span> AND "DT_CAD"<\/span><=<\/span>TO_DATE(<\/span>'
\n              2000-01-01 00:00:00'<\/span>,<\/span> 'syyyy-mm-dd hh24:mi:ss'<\/span>)<\/span>)<\/span>
\n
\n
\n21<\/span> linhas selecionadas.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> -------------------------------------------------------------------<\/span>
\nSQL ><\/span> --<\/span> SELETIVIDADE =<\/span> (<\/span>vval2-<\/span>vval1)<\/span>\/<\/span>(<\/span>(<\/span>maxval-<\/span>minval)<\/span>+<\/span>(<\/span>2<\/span> *<\/span> Density)<\/span>)<\/span>
\nSQL ><\/span> -------------------------------------------------------------------<\/span>
\nSQL ><\/span>
\nSQL ><\/span> SELECT MIN<\/span><\/a>(<\/span>dt_cad)<\/span> min<\/span><\/a>,<\/span>
\n  2<\/span>         MAX<\/span><\/a>(<\/span>dt_cad)<\/span> max<\/span><\/a>
\n  3<\/span>    FROM dbtw061;<\/span>
\n
\nMIN<\/span><\/a>      MAX<\/span><\/a>
\n--------<\/span> --------<\/span>
\n09<\/span>\/<\/span>03<\/span>\/<\/span>92<\/span> 24<\/span>\/<\/span>07<\/span>\/<\/span>19<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> SELECT column_name,<\/span>
\n  2<\/span>         density
\n  3<\/span>    FROM user_tab_cols
\n  4<\/span>   WHERE table_name=<\/span>'DBTW061'<\/span>
\n  5<\/span>     AND column_name=<\/span>'DT_CAD'<\/span>;<\/span>
\n
\nCOLUMN_NAME       DENSITY
\n--------------<\/span> ----------<\/span>
\nDT_CAD          ,<\/span>00009984<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span>
\nSQL ><\/span> SELECT (<\/span>to_date(<\/span>'20000101'<\/span>,<\/span>'yyyymmdd'<\/span>)<\/span>-<\/span>to_date(<\/span>'19991231'<\/span>,<\/span>'yyyymmdd'<\/span>)<\/span>)<\/span>\/<\/span>(<\/span>to_date(<\/span>'20190723'<\/span>,<\/span>'yyyymmdd'<\/span>)<\/span>-<\/span>to_date(<\/span>'19920308'<\/span>,<\/span>'yyyymmdd'<\/span>)<\/span>)<\/span>+<\/span>(<\/span>2<\/span>*<\/span>0.00010001<\/span>)<\/span> SELETIVIDADE
\n  2<\/span>    FROM dual;<\/span>
\n
\nSELETIVIDADE
\n------------<\/span>
\n   ,<\/span>00030004<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> -------------------------------------------------------------------<\/span>
\nSQL ><\/span> --<\/span> CARDINALIDADE =<\/span> SELETIVIDADE *<\/span> NUM LINHAS DA TABELA
\nSQL ><\/span> -------------------------------------------------------------------<\/span>
\nSQL ><\/span>
\nSQL ><\/span> SELECT ROUND<\/span><\/a>(<\/span>0.00030004<\/span>*<\/span>1000000<\/span>)<\/span> CARDINALIDADE
\n  2<\/span>    FROM dual;<\/span>
\n
\nCARDINALIDADE
\n-------------<\/span>
\n          300<\/span>
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

Observe no plano de execu\u00e7\u00e3o que o Otimizador desta vez estimou que seriam lidas 300 linhas da tabela, valor bem pr\u00f3ximo da quantidade de linhas selecionadas ao final da consulta (179 linhas), e como o Otimizador estimou uma pequeno n\u00famero de linhas ele decidiu fazer a leitura da tabela utilizando \u00edndice, com isso o desempenho da consulta foi muito superior ao da primeira consulta executada na etapa 2.<\/span>
\nLogo abaixo do plano de execu\u00e7\u00e3o temos os c\u00e1lculos realizados pelo Otimizador para estimar a leitura de 300 linhas da tabela.<\/span><\/p>\n

CONCLUS\u00c3O<\/span><\/h2>\n

Os tipos de campos que definimos para os dados que v\u00e3o popular as tabelas, al\u00e9m de servir para validar as informa\u00e7\u00f5es inseridas no banco de dados, servem para aumentar a precis\u00e3o dos algoritmos utilizados pelo Otimizador que por sua vez \u00e9 capaz de gerar planos de execu\u00e7\u00e3o com \u00f3timo desempenho. Quando decidimos ignorar a utiliza\u00e7\u00e3o dos tipos de campos adequados aos dados da tabela estamos sabotando o desempenho da aplica\u00e7\u00e3o.<\/span><\/p>\n

Refer\u00eancias<\/span><\/h2>\n

https:\/\/docs.oracle.com\/database\/121\/<\/a><\/span><\/strong><\/p>\n

 
\n 
\n 
\n\"Promo-D75L\"<\/a><\/p>\n

\"video-02\"<\/a>
\n 
\n 
\n 
\n 
\n 
\n 
\n 
\n 
\n 
\n 
\n 
\n <\/p>\n","protected":false},"excerpt":{"rendered":"

Algumas pessoas v\u00e3o ler o t\u00edtulo deste artigo e pensar que ele foi feito para leigos em tecnologia da informa\u00e7\u00e3o, mas quem trabalha com banco de dados sabe que muitas aplica\u00e7\u00f5es que existem no mercado utilizam somente os Datatypes num\u00e9rico e car\u00e1cter, esta pr\u00e1tica afeta negativamente o desempenho da aplica\u00e7\u00e3o pois o Otimizador n\u00e3o tem como deduzir, por exemplo, que<\/p>\n","protected":false},"author":2,"featured_media":2027,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[71],"tags":[221,163,39,82,10,119],"class_list":["post-2026","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-estatisticas","tag-datatypes","tag-dbms_stats","tag-estatisticas","tag-optimizer","tag-otimizador","tag-statistics"],"aioseo_notices":[],"_links":{"self":[{"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts\/2026","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/comments?post=2026"}],"version-history":[{"count":16,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts\/2026\/revisions"}],"predecessor-version":[{"id":2067,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts\/2026\/revisions\/2067"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/media\/2027"}],"wp:attachment":[{"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/media?parent=2026"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/categories?post=2026"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/tags?post=2026"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}