Interleaved Order e Online Table Move

Interleaved Order e Online Table Move

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.

COMENTÁRIOS

Política de Privacidade e Termos de Uso de Dados - Dataunique Tecnologia da Informação LTDA

A Dataunique Tecnologia da Informação LTDA, empresa devidamente registrada sob o CNPJ 15.179.495/0001-35, compromete-se a proteger a privacidade e segurança dos dados pessoais de seus usuários. Esta política descreve como coletamos, usamos, compartilhamos e protegemos as informações pessoais fornecidas por você.

1. Informações Coletadas

1.1. A Dataunique coleta informações fornecidas voluntariamente por você, como nome, endereço, e-mail, número de telefone, entre outras, durante o cadastro ou utilização de nossos serviços.

1.2. Dados de acesso e utilização de nossos serviços, como endereço IP, tipo de navegador, páginas visitadas e tempo de permanência, podem ser automaticamente registrados para melhorar a qualidade dos serviços oferecidos.

2. Uso de Informações

2.1. As informações coletadas são utilizadas para fornecer, manter, proteger e melhorar nossos serviços, bem como para desenvolver novos serviços.

2.2. Podemos utilizar seus dados para personalizar conteúdos, oferecer suporte ao cliente, enviar atualizações, newsletters e informações sobre novos produtos ou serviços.

3. Compartilhamento de Informações

3.1. A Dataunique não compartilha informações pessoais com terceiros, exceto quando necessário para cumprir obrigações legais, proteger nossos direitos ou em situações autorizadas por você.

4. Segurança de Dados

4.1. Utilizamos medidas de segurança adequadas para proteger suas informações contra acessos não autorizados, alterações, divulgação ou destruição não autorizada.

5. Cookies e Tecnologias Semelhantes

5.1. Utilizamos cookies e tecnologias semelhantes para melhorar a experiência do usuário, analisar o tráfego e personalizar conteúdos.

6. Seus Direitos

6.1. Você tem o direito de acessar, corrigir ou excluir suas informações pessoais. Para exercer esses direitos ou esclarecer dúvidas, entre em contato com nosso Encarregado de Proteção de Dados (DPO) através do e-mail [email protected].

7. Alterações na Política de Privacidade

7.1. Reservamo-nos o direito de alterar esta política a qualquer momento, e as alterações serão comunicadas por meio de nossos canais de comunicação.

Ao utilizar nossos serviços, você concorda com os termos desta Política de Privacidade. Recomendamos a leitura regular desta política para se manter informado sobre como tratamos seus dados pessoais.

Dados de Contato:

  • Endereço: Rua T30, 2515, Quadra 99 Lote 11/14, Sala 1404 e 1405, Edif Walk Bueno Business Edif e Lifestyle, SET BUENO, Goiânia – GO, 74215-060.
  • Telefone: (62) 99906-0584
  • Fax/Mensageiro Online: (62) 3223-2257
  • E-mail: [email protected]

Data de vigência: [Data de atualização da política]

Atenciosamente,

Dataunique Tecnologia da Informação LTDA