{"id":1346,"date":"2018-01-14T20:45:27","date_gmt":"2018-01-14T22:45:27","guid":{"rendered":"http:\/\/dbtimewizard.com.br\/blog\/?p=1346"},"modified":"2019-10-10T22:58:19","modified_gmt":"2019-10-11T01:58:19","slug":"rebuild-ou-coalesce-qual-a-melhor-opcao","status":"publish","type":"post","link":"http:\/\/dbtimewizard.com.br\/blog\/rebuild-ou-coalesce-qual-a-melhor-opcao\/","title":{"rendered":"REBUILD ou COALESCE, Qual a melhor op\u00e7\u00e3o?"},"content":{"rendered":"

Existe uma discuss\u00e3o antiga entre os administradores de banco de dados Oracle quanto a necessidade de fazer periodicamente a reorganiza\u00e7\u00e3o dos \u00edndices das tabelas, neste artigo vamos abordar esse assunto com o foco no impacto desta atividade na performance das consultas no banco. Esse assunto tamb\u00e9m \u00e9 muito controverso com rela\u00e7\u00e3o a reorganizar os \u00edndices para liberar espa\u00e7o em disco, mas n\u00e3o vamos abordar aqui este aspecto.<\/span><\/p>\n

Do ponto de vista de performance n\u00e3o precisamos fazer REBUILD dos \u00edndices, essa \u00e9 uma opera\u00e7\u00e3o onerosa que impacta drasticamente a performance das aplica\u00e7\u00f5es durante a execu\u00e7\u00e3o do processo e o beneficio que ela propicia pode ser obtido pela opera\u00e7\u00e3o COALESCE que \u00e9 mais leve e n\u00e3o causa impacto de performance nas aplica\u00e7\u00f5es durante sua execu\u00e7\u00e3o.<\/span><\/p>\n

Neste artigo vamos demonstrar porque precisamos fazer periodicamente a opera\u00e7\u00e3o COALESCE nos \u00edndices das tabelas que tem por caracter\u00edsticas uma grande quantidade de exclus\u00e3o de linhas ao longo do tempo e como a falta dessa manuten\u00e7\u00e3o nos \u00edndices pode impactar a performance das aplica\u00e7\u00f5es.<\/span><\/p>\n

 <\/p>\n

Para realizar esta simula\u00e7\u00e3o vamos:<\/span><\/strong><\/p>\n

1) Criar\u00a0uma tabela com um \u00edndice para simula\u00e7\u00e3o da consulta<\/span>
\n2) Excluir uma grande quantidade de linhas da tabela <\/span>
\n3) Executar a consulta na tabela e verificar a estat\u00edsticas de performance<\/span>
\n4) Executar a opera\u00e7\u00e3o COALESCE no \u00edndice<\/span>
\n5) Executar a consulta novamente e verificar o impacto do COALESCE nas estat\u00edsticas<\/span><\/p>\n

1) Criar a tabela da simula\u00e7\u00e3o<\/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
<\/div><\/td>
SQL ><\/span> SELECT *<\/span> FROM V$VERSION<\/span> where rownum <<\/span> 2<\/span>;<\/span>
\n
\nBANNER
\n--------------------------------------------------------------------------------<\/span>
\nOracle Database 11g Enterprise Edition Release 11.<\/span>2.<\/span>0.<\/span>4.<\/span>0 -<\/span> 64bit Production
\n
\nSQL ><\/span>
\nSQL ><\/span> CREATE TABLE dbtw001
\n  2<\/span>  AS<\/span>
\n  3<\/span>  SELECT rownum col1,<\/span>
\n  4<\/span>         lpad(<\/span>'x'<\/span>,<\/span>5<\/span>,<\/span>'x'<\/span>)<\/span> col2
\n  5<\/span>  FROM dual
\n  6<\/span>  CONNECT BY level <=<\/span> 1000000<\/span>
\n  7<\/span>  \/<\/span>
\n
\nTabela criada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> create index dbtw001_idx  on dbtw001(<\/span>col1)<\/span>;<\/span>
\n
\n\u00cdndice criado.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> analyze index dbtw001_idx validate structure;<\/span>
\n
\n\u00cdndice analisado.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> select lf_rows,<\/span> lf_blks,<\/span> del_lf_rows from index_stats;<\/span>
\n
\n   LF_ROWS    LF_BLKS DEL_LF_ROWS
\n----------<\/span> ----------<\/span> -----------<\/span>
\n   1000000<\/span>       2226<\/span>           0<\/span>
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

Ap\u00f3s a cria\u00e7\u00e3o do \u00edndice da tabela foi executado o comando ANALYZE e a consulta na estrutura do \u00edndice indica que ele tem 1.000.000 linhas e 2.226 LEAF BLOCKS para armazenar essas linhas.<\/span><\/p>\n

2) Excluir as linhas da tabela<\/span><\/h2>\n

Vamos excluir agora 700.000 linhas da tabela e verificar como fica a estrutura do \u00edndice.<\/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
<\/div><\/td>
SQL ><\/span> DELETE dbtw001 WHERE col1 BETWEEN 100001<\/span> AND 500000<\/span>;<\/span>
\n
\n400000<\/span> linhas deletadas.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> DELETE dbtw001 WHERE col1 BETWEEN 600001<\/span> AND 900000<\/span>;<\/span>
\n
\n300000<\/span> linhas deletadas.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> COMMIT;<\/span>
\n
\nCommit conclu\u00eddo<\/span>.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> analyze index dbtw001_idx validate structure;<\/span>
\n
\n\u00cdndice analisado.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> select lf_rows,<\/span> lf_blks,<\/span> del_lf_rows from index_stats;<\/span>
\n
\n   LF_ROWS    LF_BLKS DEL_LF_ROWS
\n----------<\/span> ----------<\/span> -----------<\/span>
\n   1000000<\/span>       2226<\/span>      700000<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span>
\nSQL ><\/span> EXEC<\/span><\/a> dbms_stats.<\/span>gather_table_stats(<\/span>USER,<\/span>'DBTW001'<\/span>,<\/span>method_opt=><\/span>'for all columns size auto'<\/span>,<\/span>cascade=><\/span>true<\/span>,<\/span>force=><\/span>true<\/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

Consultando a estrutura do \u00edndice ap\u00f3s a exclus\u00e3o das linhas observamos que a quantidade de LEAF BLOCKS continua igual, ou seja, 2.226 LEAF BLOCKS. Devido a grande quantidade de linhas exclu\u00eddas da tabela,\u00a0agora temos na estrutura do \u00edndice uma quantidade enorme de LEAF BLOCKS vazios.\u00a0<\/span><\/p>\n

3) Executar a consulta e verificar as estat\u00edsticas<\/span><\/h2>\n

Agora vamos executar uma consulta na tabela que vai utilizar o \u00edndice criado e em seguida vamos verificar as estat\u00edsticas no plano de execu\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
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
<\/div><\/td>
SQL ><\/span> ALTER SESSION SET statistics_level =<\/span> ALL;<\/span>
\n
\nSess\u00e3o alterada.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> SELECT \/* DBTW501 *\/<\/span> col1,<\/span> col2
\n  2<\/span>    FROM dbtw001
\n  3<\/span>   WHERE col1 BETWEEN 599999<\/span> AND 900010<\/span>;<\/span>
\n
\n      COL1 COL2
\n----------<\/span> -----<\/span>
\n    599999<\/span> xxxxx
\n    600000<\/span> xxxxx
\n    900001<\/span> xxxxx
\n    900002<\/span> xxxxx
\n    900003<\/span> xxxxx
\n    900004<\/span> xxxxx
\n    900005<\/span> xxxxx
\n    900006<\/span> xxxxx
\n    900007<\/span> xxxxx
\n    900008<\/span> xxxxx
\n    900009<\/span> xxxxx
\n    900010<\/span> xxxxx
\n
\n12<\/span> linhas selecionadas.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> --<\/span> Recupera o SQL_ID e o CHILD_NUMBER  da consulta executada
\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 '%DBTW501%'<\/span>
\n  4<\/span>     AND sql_text NOT LIKE '%v$sql%'<\/span>;<\/span>
\n
\nSQL_ID        CHILD_NUMBER
\n-------------<\/span> ------------<\/span>
\nd7dm563jz7rbs            0<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> --<\/span> Gera o relat\u00f3rio do<\/span> plano de execu\u00e7\u00e3o da consulta
\nSQL ><\/span> SELECT *<\/span> FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'&m_sql_id'<\/span>,&<\/span>m_child_no,<\/span>'typical iostats last'<\/span>)<\/span>)<\/span>;<\/span>
\nantigo   1<\/span>:<\/span> SELECT *<\/span> FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'&m_sql_id'<\/span>,&<\/span>m_child_no,<\/span>'typical iostats last'<\/span>)<\/span>)<\/span>
\nnovo   1<\/span>:<\/span> SELECT *<\/span> FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'d7dm563jz7rbs'<\/span>,<\/span>         0<\/span>,<\/span>'typical iostats last'<\/span>)<\/span>)<\/span>
\n
\nPLAN_TABLE_OUTPUT
\n--------------------------------------------------------------------------------------------------------------------------------------<\/span>
\nSQL_ID  d7dm563jz7rbs,<\/span> child number 0<\/span>
\n-------------------------------------<\/span>
\nSELECT \/* DBTW501 *\/<\/span> col1,<\/span> col2   FROM dbtw001  WHERE col1 BETWEEN
\n:<\/span>"SYS_B_0"<\/span> AND :<\/span>"SYS_B_1"<\/span>
\n
\nPlan hash<\/span><\/a> value:<\/span> 3268869844<\/span>
\n
\n--------------------------------------------------------------------------------------------------------------------------------------<\/span>
\n|<\/span> Id  |<\/span> Operation                    |<\/span> Name        |<\/span> Starts |<\/span> E-<\/span>Rows |<\/span>E-<\/span>Bytes|<\/span> Cost (<\/span>%<\/span>CPU)<\/span>|<\/span> E-<\/span>Time<\/span><\/a>   |<\/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>       |<\/span>     8<\/span> (<\/span>100<\/span>)<\/span>|<\/span>          |<\/span>     12<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>     676<\/span> |<\/span>
\n|*<\/span>  1<\/span> |<\/span>  FILTER                      |<\/span>             |<\/span>      1<\/span> |<\/span>        |<\/span>       |<\/span>            |<\/span>          |<\/span>     12<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>     676<\/span> |<\/span>
\n|<\/span>   2<\/span> |<\/span>   TABLE ACCESS BY INDEX ROWID|<\/span> DBTW001     |<\/span>      1<\/span> |<\/span>   1176<\/span> |<\/span> 12936<\/span> |<\/span>     8<\/span>   (<\/span>0<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>01<\/span> |<\/span>     12<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>     676<\/span> |<\/span>
\n|*<\/span>  3<\/span> |<\/span>    INDEX RANGE<\/span><\/a> SCAN          |<\/span> DBTW001_IDX |<\/span>      1<\/span> |<\/span>   1176<\/span> |<\/span>       |<\/span>     5<\/span>   (<\/span>0<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>01<\/span> |<\/span>     12<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>     673<\/span> |<\/span>
\n--------------------------------------------------------------------------------------------------------------------------------------<\/span>
\n
\nPredicate Information (<\/span>identified by operation id)<\/span>:<\/span>
\n---------------------------------------------------<\/span>
\n
\n   1<\/span> -<\/span> filter(<\/span>:<\/span>SYS_B_1>=:<\/span>SYS_B_0)<\/span>
\n   3<\/span> -<\/span> access(<\/span>"COL1"<\/span>>=:<\/span>SYS_B_0 AND "COL1"<\/span><=:<\/span>SYS_B_1)<\/span>
\n
\n
\n22<\/span> linhas selecionadas.<\/span>
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

Analisando o plano de execu\u00e7\u00e3o verificamos que o Oracle teve que ler 673 blocos de \u00edndice para retornar somente 12 linhas, fica claro que os blocos vazios est\u00e3o sendo acessados inutilmente, ou seja, existe um consumo desnecess\u00e1rio de recursos do banco causando perda de performance na consulta.<\/span><\/p>\n

4) Executar a opera\u00e7\u00e3o COALESCE no \u00edndice<\/span><\/h2>\n

Para otimizar a estrutura do \u00edndice vamos executar o comando COALESCE e verificar como ela vai ficar.<\/span><\/p>\n

 <\/p>\n

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
<\/div><\/td>
SQL ><\/span> alter index dbtw001_idx coalesce;<\/span>
\n
\n\u00cdndice alterado.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> analyze index dbtw001_idx validate structure;<\/span>
\n
\n\u00cdndice analisado.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> select lf_rows,<\/span> lf_blks,<\/span> del_lf_rows from index_stats;<\/span>
\n
\n   LF_ROWS    LF_BLKS DEL_LF_ROWS
\n----------<\/span> ----------<\/span> -----------<\/span>
\n    300000<\/span>        668<\/span>           0<\/span>
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

Consultando a estrutura do \u00edndice ap\u00f3s o comando COALESCE verificamos que o n\u00famero de LEAF BLOCKS diminuiu de 2.226 para 668, ou seja todos os blocos vazios foram exclu\u00eddos da estrutura do \u00edndice e aqueles que continham poucas linhas foram unidos.<\/span><\/p>\n

5) Executar a consulta novamente e verificar se houve melhoria<\/span><\/h2>\n

Com o \u00edndice reestruturado vamos executar a consulta novamente e verificar se haver\u00e1 alguma melhoria no acesso aos blocos do \u00edndice.<\/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
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
<\/div><\/td>
SQL ><\/span> SELECT \/* DBTW502 *\/<\/span> col1,<\/span> col2
\n  2<\/span>    FROM dbtw001
\n  3<\/span>   WHERE col1 BETWEEN 599999<\/span> AND 900010<\/span>;<\/span>
\n
\n      COL1 COL2
\n----------<\/span> -----<\/span>
\n    599999<\/span> xxxxx
\n    600000<\/span> xxxxx
\n    900001<\/span> xxxxx
\n    900002<\/span> xxxxx
\n    900003<\/span> xxxxx
\n    900004<\/span> xxxxx
\n    900005<\/span> xxxxx
\n    900006<\/span> xxxxx
\n    900007<\/span> xxxxx
\n    900008<\/span> xxxxx
\n    900009<\/span> xxxxx
\n    900010<\/span> xxxxx
\n
\n12<\/span> linhas selecionadas.<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> --<\/span> Recupera o SQL_ID e o CHILD_NUMBER  da consulta executada
\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 '%DBTW502%'<\/span>
\n  4<\/span>     AND sql_text NOT LIKE '%v$sql%'<\/span>;<\/span>
\n
\nSQL_ID        CHILD_NUMBER
\n-------------<\/span> ------------<\/span>
\n5fxp0gakr1h9u            0<\/span>
\n
\nSQL ><\/span>
\nSQL ><\/span> --<\/span> Gera o relat\u00f3rio do<\/span> plano de execu\u00e7\u00e3o da consulta
\nSQL ><\/span> SELECT *<\/span> FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'&m_sql_id'<\/span>,&<\/span>m_child_no,<\/span>'typical iostats last'<\/span>)<\/span>)<\/span>;<\/span>
\nantigo   1<\/span>:<\/span> SELECT *<\/span> FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'&m_sql_id'<\/span>,&<\/span>m_child_no,<\/span>'typical iostats last'<\/span>)<\/span>)<\/span>
\nnovo   1<\/span>:<\/span> SELECT *<\/span> FROM TABLE (<\/span>dbms_xplan.<\/span>display_cursor (<\/span>'5fxp0gakr1h9u'<\/span>,<\/span>         0<\/span>,<\/span>'typical iostats last'<\/span>)<\/span>)<\/span>
\n
\nPLAN_TABLE_OUTPUT
\n---------------------------------------------------------------------------------------------------------------------------------------<\/span>
\nSQL_ID  5fxp0gakr1h9u,<\/span> child number 0<\/span>
\n-------------------------------------<\/span>
\nSELECT \/* DBTW502 *\/<\/span> col1,<\/span> col2   FROM dbtw001  WHERE col1 BETWEEN
\n:<\/span>"SYS_B_0"<\/span> AND :<\/span>"SYS_B_1"<\/span>
\n
\nPlan hash<\/span><\/a> value:<\/span> 3268869844<\/span>
\n
\n--------------------------------------------------------------------------------------------------------------------------------------<\/span>
\n|<\/span> Id  |<\/span> Operation                    |<\/span> Name        |<\/span> Starts |<\/span> E-<\/span>Rows |<\/span>E-<\/span>Bytes|<\/span> Cost (<\/span>%<\/span>CPU)<\/span>|<\/span> E-<\/span>Time<\/span><\/a>   |<\/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>       |<\/span>     8<\/span> (<\/span>100<\/span>)<\/span>|<\/span>          |<\/span>     12<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>       8<\/span> |<\/span>
\n|*<\/span>  1<\/span> |<\/span>  FILTER                      |<\/span>             |<\/span>      1<\/span> |<\/span>        |<\/span>       |<\/span>            |<\/span>          |<\/span>     12<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>       8<\/span> |<\/span>
\n|<\/span>   2<\/span> |<\/span>   TABLE ACCESS BY INDEX ROWID|<\/span> DBTW001     |<\/span>      1<\/span> |<\/span>   1176<\/span> |<\/span> 12936<\/span> |<\/span>     8<\/span>   (<\/span>0<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>01<\/span> |<\/span>     12<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>       8<\/span> |<\/span>
\n|*<\/span>  3<\/span> |<\/span>    INDEX RANGE<\/span><\/a> SCAN          |<\/span> DBTW001_IDX |<\/span>      1<\/span> |<\/span>   1176<\/span> |<\/span>       |<\/span>     5<\/span>   (<\/span>0<\/span>)<\/span>|<\/span> 00<\/span>:<\/span>00<\/span>:<\/span>01<\/span> |<\/span>     12<\/span> |<\/span>00<\/span>:<\/span>00<\/span>:<\/span>00.01<\/span> |<\/span>       5<\/span> |<\/span>
\n--------------------------------------------------------------------------------------------------------------------------------------<\/span>
\n
\nPredicate Information (<\/span>identified by operation id)<\/span>:<\/span>
\n---------------------------------------------------<\/span>
\n
\n   1<\/span> -<\/span> filter(<\/span>:<\/span>SYS_B_1>=:<\/span>SYS_B_0)<\/span>
\n   3<\/span> -<\/span> access(<\/span>"COL1"<\/span>>=:<\/span>SYS_B_0 AND "COL1"<\/span><=:<\/span>SYS_B_1)<\/span>
\n
\n
\n22<\/span> linhas selecionadas.<\/span>
\n
\nSQL ><\/span><\/div><\/td><\/tr><\/tbody><\/table><\/div>\n

 <\/p>\n

Verificando o plano de execu\u00e7\u00e3o acima podemos constatar que o plano de execu\u00e7\u00e3o permanece o mesmo e que foram selecionadas as mesmas 12 linhas, porem o numero de blocos acessados na leitura do \u00edndice diminuiu de 673 para 5, tornando a consulta mais eficiente.<\/span><\/p>\n

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

Para tabelas que tem um grande volume de exclus\u00e3o de linhas ao longo do tempo, \u00e9 uma boa pr\u00e1tica agendar uma rotina peri\u00f3dica para fazer a opera\u00e7\u00e3o COALESCE nos \u00edndices relacionados a essas tabelas.<\/span><\/p>\n

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

https:\/\/docs.oracle.com\/cd\/B28359_01\/server.111\/b28286\/statements_1009.htm#SQLRF00805<\/a><\/strong><\/span><\/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":"

Existe uma discuss\u00e3o antiga entre os administradores de banco de dados Oracle quanto a necessidade de fazer periodicamente a reorganiza\u00e7\u00e3o dos \u00edndices das tabelas, neste artigo vamos abordar esse assunto com o foco no impacto desta atividade na performance das consultas no banco. Esse assunto tamb\u00e9m \u00e9 muito controverso com rela\u00e7\u00e3o a reorganizar os \u00edndices para liberar espa\u00e7o em disco,<\/p>\n","protected":false},"author":2,"featured_media":1335,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[62],"tags":[188,191,190,187,189],"class_list":["post-1346","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-indice","tag-coalesce-index","tag-fragmentacao-indice","tag-manutencao-indice","tag-rebuild-index","tag-reorganizar-indice"],"aioseo_notices":[],"_links":{"self":[{"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts\/1346","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=1346"}],"version-history":[{"count":24,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts\/1346\/revisions"}],"predecessor-version":[{"id":2053,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/posts\/1346\/revisions\/2053"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/media\/1335"}],"wp:attachment":[{"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/media?parent=1346"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/categories?post=1346"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/dbtimewizard.com.br\/blog\/wp-json\/wp\/v2\/tags?post=1346"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}