Interleaved Order e Online Table Move

18 mar

Interleaved Order e Online Table Move

Design sem nome (2)

Introdução

Neste Lab vamos melhorar o clustering factor da tabela utilizando “Interleaved Order” e a nova feature do oracle 12.2 “Online Table Move”.

Order e Online Table Move (12.2)

Vamos criar uma tabela no nosso ambiente de testes e inserir dados aleatórios na tabela.

SQL> create table dados (id number, codigo number, grau number, nome varchar2(42));
Tabela criada.

SQL> insert into dados select rownum, mod(rownum, 100)+1, ceil(dbms_random.value(0,100)), 'DADOS ALEATORIOS' from dual connect by leve
l <= 1000000;
4000000 linhas criadas.

SQL> commit;
Commit concluido.

SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=> 'DADOS', method_opt=>'FOR ALL COLUMNS SIZE 1');
Procedimento PL/SQL concluido com sucesso.

SQL> create index dados_idx_codigo on dados(codigo);
Indice criado.

SQL> create index dados_idx_grau on dados(grau);
Indice criado.

SQL> select index_name, clustering_factor, num_rows from user_indexes where table_name='DADOS';

INDEX_NAME CLUSTERING_FACTOR NUM_ROWS
------------------------------ ----------------- ----------
DADOS_IDX_GRAU 1648174 4000000
DADOS_IDX_CODIGO 1862900 4000000

Temos um clustering factor ruim nos Índices GRAU e CODIGO devido a distribuição de ambas as colunas em toda a tabela.
Mesmo tendo somente 100 valores distintos (e seletividade de 1%) o CBO vai considerar os índices ineficientes.

SQL> select * from dados where codigo = 42;
40000 linhas selecionadas.


Plano de Execu▒▒o
----------------------------------------------------------
Plan hash value: 1548001723

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 40000 | 1132K| 6920 (1)| 00:01:24 |
|* 1 | TABLE ACCESS FULL| DADOS | 40000 | 1132K| 6920 (1)| 00:01:24 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("CODIGO"=42)

Estat▒sticas
----------------------------------------------------------
0 recursive calls
0 db block gets
24758 consistent gets
0 physical reads
0 redo size
564025 bytes sent via SQL*Net to client
1393 bytes received via SQL*Net from client
81 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
40000 rows processed
SQL> select * from dados where grau = 42;
39930 linhas selecionadas.
 

Plano de Execu▒▒o
----------------------------------------------------------
Plan hash value: 1548001723

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 40000 | 1132K| 6923 (1)| 00:01:24 |
|* 1 | TABLE ACCESS FULL| DADOS | 40000 | 1132K| 6923 (1)| 00:01:24 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("GRAU"=42)

Estat▒sticas
----------------------------------------------------------
1 recursive calls
0 db block gets
24758 consistent gets
0 physical reads
0 redo size
563134 bytes sent via SQL*Net to client
1393 bytes received via SQL*Net from client
81 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
39930 rows processed

Mesmo que o CBO tenta estimado a quantidade de linhas corretamente, nos 2 casos o CBO escolhe fazer um full table scan.
Vamos adicionar agrupamento na coluna CODIGO e utilizar a nova feature do 12.2 (Move Online).

SQL> alter table DADOS add clustering by linear order (CODIGO);
Tabela alterada.

SQL> alter table DADOS move online; 
Tabela alterada.

SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=> 'DADOS', method_opt=>'FOR ALL COLUMNS SIZE 1');
Procedimento PL/SQL conclu▒do com sucesso.

Notamos que ordenando os dados da tabela pelo CODIGO reduziu significantemente o clustering factor para o índice DADOS_IDX_CODIGO.

SQL> select index_name, clustering_factor, num_rows from user_indexes where table_name='DADOS';

INDEX_NAME CLUSTERING_FACTOR NUM_ROWS
------------------- ----------------- ----------
DADOS_IDX_CODIGO 25201 3986167
DADOS_IDX_GRAU 1591574 3847250

Vamos executar o select novamente.

SQL> select * from dados where codigo = 42;
40000 linhas selecionadas.

Plano de Execução
----------------------------------------------------------
Plan hash value: 3203332890

--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 40000 | 1132K| 337 (1)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| DADOS | 40000 | 1132K| 337 (1)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | DADOS_IDX_CODIGO | 40000 | | 81 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------

Agora o CBO está utilizando o índice para a query, mas como o campo GRAU também é de extrema importância para o negócio, e o clustering factor continua pobre para o campo, vamos criar ordenação na tabela nos 2 campos (CODIGO e GRAU).

SQL> ALTER TABLE DADOS DROP CLUSTERING;
Tabela alterada.
 
SQL> alter table DADOS add clustering by linear order (CODIGO, GRAU);
Tabela alterada.

SQL> ALTER TABLE DADOS MOVE ONLINE;
Tabela alterada.

SQL> alter table DADOS add clustering by linear order (CODIGO, GRAU);

SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=> 'DADOS', method_opt=>'FOR ALL COLUMNS SIZE 1');
Procedimento PL/SQL conclu▒do com sucesso.

SQL> select index_name, clustering_factor, num_rows from user_indexes where table_name='DADOS';

INDEX_NAME CLUSTERING_FACTOR NUM_ROWS
-------------------- ---------------- ----------
DADOS_IDX_CODIGO 24570 3858382
DADOS_IDX_GRAU 34795 3957770

O Clustering factor seria melhor utilizando ordenação somente em uma coluna, porém, como ambas as colunas são importantes para o negócio, será melhor um clustering factor mediano nas duas colunas que um clustering factor perfeito somente em uma coluna.