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.
