Select count no Oracle - Otimizando a performance

Veja neste artigo como fazer select count em Oracle com boa performance, também serão apresentados alguns mitos e segredos sobre esse tema.



Figura 1: Select count no Oracle

Veja nesse artigo algumas maneiras de fazer o select count. Falaremos em mitos, performance e algumas maneiras mais eficazes de se fazer o select count, na realidade, de se obter quantidade de registros de uma tabela.

A primeira abordagem será sempre olhar para a DBA_TABLES ou ALL_TABLES (coluna num_rows), pois se a tabela estiver com as estatísticas atualizadas teremos a quantidade de registros na tabela sem muito esforço.

Alguns itens que temos de levar em consideração são:

O select count irá contar apenas registros not null;

Quando fazemos um select count(*), o Oracle irá validar todas as colunas da tabela e irá dar o valor de todos os registros not null de todas as colunas (maior quantidade). Se necessitarmos saber a quantidade de registros de uma coluna especifica e usarmos o select count(*) poderemos ter uma quantidade errada de registros, ou seja, o Oracle irá ignorar os null. Imaginemos uma tabela com duas colunas (Col1 e Col2) a Col1 é not null e na Col2 há registros nulos, queremos agora saber quantos registros existem nessa coluna. Se fizermos select count(*) tabela, o Oracle irá devolver a quantidade total de registros da tabela, tendo em consideração a Col1 (pois são todos not null) porém se fizermos select count (Col2) from tabela, teremos a quantidade exata de registros not null dessa coluna da tabela.

Procurar sempre verificar se temos índices na tabela;

Caso tenhamos uma coluna indexada, devemos usar essa coluna no select count(), pois o Oracle irá fazer um Fast Full Scan, que é melhor que o Full Table Scan.

Se tivermos uma PK ou Índice único na tabela, o Oracle sempre irá utilizar esse índice para fazer o count, caso não especifique o nome da coluna.

Outras formas de obter a quantidade estimada de registros;

Se precisamos obter a quantidade de registros estimada de uma tabela e a mesma não possui índices e nem estatísticas atualizadas e a tabela é grande, podemos usar o “sample” que nos dará um valor estimado de registros utilizando apenas uma estimativa da tabela.

Veremos agora mais abaixo os cenários descritos acima e alguns exemplos:

Caracterização do cenário:

Foi criada uma tabela com diferentes tipos de dados e alguns índices. Podem ver nos scripts abaixo:

Listagem 1: Script para criar uma tabela com índice

CREATE TABLE table1 ( name VARCHAR2 (255), id NUMBER, column2 VARCHAR2 (50), column3 VARCHAR2 (2000) ); CREATE INDEX H_ID ON SYSTEM.table1 (ID);

Listagem 2: Estatísticas da tabela

BEGIN DBMS_STATS.gather_table_stats (ownname => 'SYSTEM', tabname => 'TABLE1', estimate_percent => 100, granularity => 'ALL', cascade => TRUE); END;

Listagem 3: Script to populate table

DECLARE BEGIN FOR i IN 1 .. 10000 LOOP INSERT INTO table1 SELECT DBMS_RANDOM.string ('U', 254) str, DBMS_RANDOM.VALUE (100000000000, 999999999999), DBMS_RANDOM.string ('U', 49) str, DBMS_RANDOM.string ('U', 1998) str FROM DUAL; COMMIT; END LOOP; COMMIT; END;

Observação: Atenção que aleatoriamente foram inseridos 10 mil registros NULL, menos para a coluna ID.

Caso de teste 1

Precisamos saber quantidade de registros de uma tabela, onde não temos estatísticas, não tem PK e nem um unique index.

Nesse caso, se fizermos um select count(*) from tabela, iremos fazer um TAF (Table Access Full), se a tabela for pequena não teremos problema, porém se a tabela for de alguns Gigabytes e alguns acessos desse, iremos com certeza provocar uma grande utilização de recurso no servidor (I/O) e com isso podemos fazer a base de dados ficar mais lenta.

Sendo assim, temos que procurar uma coluna indexada para fazermos o select count (col Indexada) from table e com isso passamos de TAF para FFS (Fast Full Scan) no Índice.

Vejamos o exemplo abaixo:

Listagem 4: Select count(*)

select count(*) from system.table1;

Podemos observar nesse exemplo que a nossa tabela tem 41000 linhas e faz um TAF (Table Access FULL) na tabela TABLE1, mais abaixo podem ver a quantidade de Physical reads feita.

Listagem 5: Physical reads do primeiro exemplo

SQL> set autotrace on explain statistics SQL> ALTER SYSTEM FLUSH SHARED_POOL; --Limpar a memoria System altered. Elapsed: 00:00:00.06 SQL> select count(*) from system.table1; COUNT(*) ---------- 41000 Elapsed: 00:00:00.06 Execution Plan ---------------------------------------------------------- Plan hash value: 3207558960 --------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | --------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 2934 (1)| 00:00:36 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | TABLE ACCESS FULL| TABLE1 | 41000 | 2934 (1)| 00:00:36 | --------------------------------------------------------------------- Statistics ---------------------------------------------------------- 1208 recursive calls 18 db block gets 11018 consistent gets 10814 physical reads 0 redo size 527 bytes sent via SQL*Net to client 524 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 17 sorts (memory) 0 sorts (disk) 1 rows processed

Caso de teste 2

Temos que obter o total de registros da tabela, temos uma coluna (ID) que possui índice e sabemos que essa coluna não tem nulos. Esse cenário resolve o nosso problema lindamente, ou seja, teremos a quantidade exata de registros na tabela (Coluna ID não tem nulos) e não iremos fazer um TAF e sim um FFS no índice, diminuindo assim brutalmente o tempo de execução do select count como também utilizaremos muito menos recursos no servidor.

Listagem 6: Select count sobre a coluna not null

select count(ID) from system.table1;

Podemos ver abaixo que temos um INDEX FAST FULL SCAN no índice da coluna ID (índice H_ID) e com isso já temos uma redução drástica de consumo de recursos do servidor. A query é resolvida em 1/3 do tempo, mas o mais importante aqui é mesmo a redução do consumo de recursos para executar a mesma query. Imaginem 100 pessoas em simultâneo a fazer a mesma query, teríamos que ter grandes servidores.

Listagem 7: Execução do select count sobre a coluna not null

SQL> ALTER SYSTEM FLUSH SHARED_POOL; System altered. Elapsed: 00:00:00.02 SQL> select count(ID) from system.table1; COUNT(ID) ---------- 41000 Elapsed: 00:00:00.02 Execution Plan ---------------------------------------------------------- Plan hash value: 866143595 ------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 22 | 53 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 22 | | | | 2 | INDEX FAST FULL SCAN| H_ID | 41000 | 880K| 53 (0)| 00:00:01 | ------------------------------------------------------------------------------ Statistics ---------------------------------------------------------- 303 recursive calls 0 db block gets 241 consistent gets 194 physical reads 0 redo size 528 bytes sent via SQL*Net to client 524 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 6 sorts (memory) 0 sorts (disk) 1 rows processed

Caso de teste 3

Esse cenário é um cenário onde não conseguimos ter nenhum dos cenários acima, ou seja, a tabela é muito grande e não há nenhum índice, não tem estatísticas atualizadas. Porém precisamos ter apenas uma estimativa de quantidade de registros que essa tabela possui, uma contagem não tão precisa.

Listagem 8: Count sem muita precisão

SELECT COUNT(1) *100 FROM system.table1 SAMPLE (1); --retornará 1% da tabela e se fizermos a multiplicação por 100 , teremos a estimativa da quantidade total de registros.

Listagem 9: Execução da contagem não precisa

SQL> SELECT COUNT(1) *100 FROM system.table1 SAMPLE (1); COUNT(1)*100 ------------ 52700 Elapsed: 00:00:00.07 Execution Plan ---------------------------------------------------------- Plan hash value: 2121025407 ------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 12 | 2934 (1)| 00:00:36 | | 1 | SORT AGGREGATE | | 1 | 12 | | | | 2 | TABLE ACCESS SAMPLE| TABLE1 | 410 | 4920 | 2934 (1)| 00:00:36 | ------------------------------------------------------------------------------- Statistics ---------------------------------------------------------- 236 recursive calls 0 db block gets 1395 consistent gets 1982 physical reads 0 redo size 531 bytes sent via SQL*Net to client 524 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 4 sorts (memory) 0 sorts (disk) 1 rows processed

Observação: Nesse exemplo temos a tabela com mais registros e por isso o tempo é diferente do teste inicial, porém se olharmos para as estatísticas de execução da query, veremos um consumo bem menor para a execução da query.

Bom, pessoal, fico por aqui nessa pequena dica. Abraços.


Artigos relacionados