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.
Confira outros conteúdos:
Por
Haysar
Em 2012
Veja os resultado dos nossos alunos
Conquistas reais de quem está aplicando o método
<Perguntas frequentes>
Carreira
Metodologia
Assinatura e Pagamentos
Cadastro