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.