Você tambem menospreza esta questão?

duvida

É comum pensar que uma instrução SQL que acessa uma linha numa tabela pequena é muito rápida e não devemos perder tempo em verificar se é mais rápido fazer um FULL SCAN na tabela ou utilizar um índice para fazer o acesso. Muitas aplicações utilizam tabelas pequenas como parâmetros da aplicação e dependendo da quantidade de usuários e iteração destes com o sistema estas tabelas podem ser acessadas milhares de vezes por segundo, para estes casos o tempo e a quantidade de recursos gastos pelo banco para acessar uma linha da tabela são muito importantes e podem fazer muita diferença.

Pensando na importância deste assunto, neste artigo vamos fazer algumas simulações para responder a seguinte questão:

Qual a melhor maneira para acessar uma linha numa tabela pequena:

1) Seria através de um índice de chave primaria?
2) Seria através de um índice de chave não unica?
3) Seria através de uma tabela IOT?
4) Seria através de uma operação TABLE FULL SCAN?

1) Lendo uma linha usando índice de chave primária

 

Nesta primeira simulação vamos criar uma tabela pequena com um índice de chave primária que será utilizada no filtro da cláusula WHERE para selecionar a linha desejada.

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
SQL > SELECT * FROM V$VERSION where rownum < 2;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

SQL >
SQL > DROP TABLE dbtw_employees purge;

Tabela eliminada.

SQL >
SQL >
SQL > CREATE TABLE dbtw_employees (EMPLOYEE_ID NUMBER(6,0) NOT NULL PRIMARY KEY,
  2                               FIRST_NAME VARCHAR2(20),
  3                               LAST_NAME VARCHAR2(25) NOT NULL,
  4                               EMAIL  VARCHAR2(25) NOT NULL,
  5                               PHONE_NUMBER  VARCHAR2(20),
  6                               HIRE_DATE  DATE NOT NULL,
  7                               JOB_ID  VARCHAR2(10) NOT NULL,
  8                               SALARY  NUMBER(8,2),
  9                               COMMISSION_PCT NUMBER(2,2),
 10                               MANAGER_ID NUMBER(6,0),
 11                               DEPARTMENT_ID NUMBER(4,0));

Tabela criada.

SQL >
SQL >
SQL > INSERT INTO dbtw_employees SELECT * FROM hr.employees where rownum < 61;

60 linhas criadas.

SQL >
SQL > COMMIT;

Commit concluído.

SQL >
SQL > exec dbms_stats.gather_table_stats(ownname=>USER, tabname=>'DBTW_EMPLOYEES', method_opt=>FOR ALL COLUMNS SIZE AUTO', cascade=>true);

Procedimento PL/SQL concluído com sucesso.

SQL >
SQL > ALTER SESSION SET statistics_level=ALL;

Sessão alterada.

SQL >
SQL > SELECT /* dbtw001  */ first_name, job_id, salary
  2    FROM dbtw_employees
  3   WHERE employee_id=143;

FIRST_NAME           JOB_ID         SALARY
-------------------- ---------- ----------
Randall              ST_CLERK         2600

SQL >
SQL > column sql_id new_value m_sql_id
SQL > column child_number new_value m_child_no
SQL >
SQL > SELECT sql_id, child_number
  2    FROM v$sql
  3   WHERE sql_text LIKE '
%dbtw001%'
  4     AND sql_text NOT LIKE '
%v$sql%';

SQL_ID        CHILD_NUMBER
------------- ------------
b5gb48ay5f5sh            0

SQL >
SQL >
SQL > SELECT *
  2    FROM TABLE (dbms_xplan.display_cursor ('
&m_sql_id',&m_child_no,'typical iostats last'));
antigo   2:   FROM TABLE (dbms_xplan.display_cursor ('
&m_sql_id',&m_child_no,'typical iostats last'))
novo   2:   FROM TABLE (dbms_xplan.display_cursor ('
b5gb48ay5f5sh',         0,'typical iostats last'))

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  b5gb48ay5f5sh, child number 0
-------------------------------------
SELECT /* dbtw001  */ first_name, job_id, salary   FROM dbtw_employees
WHERE employee_id=:"SYS_B_0"

Plan hash value: 3344810242

----------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name           | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                |      1 |        |       |     1 (100)|          |      1 |00:00:00.01 |       2 |
|   1 |  TABLE ACCESS BY INDEX ROWID| DBTW_EMPLOYEES |      1 |      1 |    24 |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       2 |
|*  2 |   INDEX UNIQUE SCAN         | SYS_C00315322  |      1 |      1 |       |     0   (0)|          |      1 |00:00:00.01 |       1 |
----------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("EMPLOYEE_ID"=:SYS_B_0)


20 linhas selecionadas.

SQL >

 

Como podemos observar no plano de execução da consulta, foram lidos 2 Buffers ou 2 blocos no Buffer Cache para acessar uma linha da tabela.

2) Lendo uma linha usando índice de chave não unica

Title of the document

Ajudo DBAs e analistas de sistema a se destacarem em suas empresas

e obter um crescimento mais acelerado em suas carreiras, quer saber mais click no link abaixo:

Agora vamos fazer uma simulação com mesma tabela só que desta vez vamos utilizar um índice não único no mesmo campo da tabela onde na primeira simulação havia um índice de chave primaria.

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
SQL > DROP TABLE dbtw_employees purge;

Tabela eliminada.

SQL >
SQL >
SQL > CREATE TABLE dbtw_employees (EMPLOYEE_ID NUMBER(6,0) NOT NULL,
  2                               FIRST_NAME VARCHAR2(20),
  3                               LAST_NAME VARCHAR2(25) NOT NULL,
  4                               EMAIL  VARCHAR2(25) NOT NULL,
  5                               PHONE_NUMBER  VARCHAR2(20),
  6                               HIRE_DATE  DATE NOT NULL,
  7                               JOB_ID  VARCHAR2(10) NOT NULL,
  8                               SALARY  NUMBER(8,2),
  9                               COMMISSION_PCT NUMBER(2,2),
 10                               MANAGER_ID NUMBER(6,0),
 11                               DEPARTMENT_ID NUMBER(4,0));

Tabela criada.

SQL >
SQL >
SQL > INSERT INTO dbtw_employees SELECT * FROM hr.employees where rownum < 61;

60 linhas criadas.

SQL >
SQL > COMMIT;

Commit concluído.

SQL >
SQL > CREATE INDEX dbtw_employees_idx on dbtw_employees(EMPLOYEE_ID);

Índice criado.

SQL >
SQL > exec dbms_stats.gather_table_stats(ownname=>USER, tabname=>'DBTW_EMPLOYEES', method_opt=>FOR ALL COLUMNS SIZE AUTO', cascade=>true);

Procedimento PL/SQL concluído com sucesso.

SQL >
SQL > ALTER SESSION SET statistics_level=ALL;

Sessão alterada.

SQL >
SQL > SELECT /* dbtw002  */ first_name, job_id, salary
  2    FROM dbtw_employees
  3   WHERE employee_id=143;

FIRST_NAME           JOB_ID         SALARY
-------------------- ---------- ----------
Randall              ST_CLERK         2600

SQL >
SQL > column sql_id new_value m_sql_id
SQL > column child_number new_value m_child_no
SQL >
SQL > SELECT sql_id, child_number
  2    FROM v$sql
  3   WHERE sql_text LIKE '
%dbtw002%'
  4     AND sql_text NOT LIKE '
%v$sql%';

SQL_ID        CHILD_NUMBER
------------- ------------
93pdp53t14ukg            0

SQL >
SQL >
SQL > SELECT *
  2    FROM TABLE (dbms_xplan.display_cursor ('
&m_sql_id',&m_child_no,'typical iostats last'));
antigo   2:   FROM TABLE (dbms_xplan.display_cursor ('
&m_sql_id',&m_child_no,'typical iostats last'))
novo   2:   FROM TABLE (dbms_xplan.display_cursor ('
93pdp53t14ukg',         0,'typical iostats last'))

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  93pdp53t14ukg, child number 0
-------------------------------------
SELECT /* dbtw002  */ first_name, job_id, salary   FROM dbtw_employees
WHERE employee_id=:"SYS_B_0"

Plan hash value: 3843110344

--------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name               | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                    |      1 |        |       |     2 (100)|          |      1 |00:00:00.01 |       3 |
|   1 |  TABLE ACCESS BY INDEX ROWID| DBTW_EMPLOYEES     |      1 |      1 |    24 |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       3 |
|*  2 |   INDEX RANGE SCAN          | DBTW_EMPLOYEES_IDX |      1 |      1 |       |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       2 |
--------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("EMPLOYEE_ID"=:SYS_B_0)


20 linhas selecionadas.

SQL >

 

Nesta simulação o resultado foi pior, pois foram necessárias a leitura de 3 Buffers contra a leitura de 2 na primeira simulação.

 

3) Lendo uma linha usando uma tabela IOT

 

Nesta simulação vamos utilizar uma tabela IOT (Index-Organized Table) com a mesma estrutura da tabela utilizada nas simulações anteriores e a mesma quantidade de linhas.

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
SQL > DROP TABLE dbtw_employees purge;

Tabela eliminada.

SQL >
SQL >
SQL > CREATE TABLE dbtw_employees (EMPLOYEE_ID NUMBER(6,0) NOT NULL PRIMARY KEY,
  2                               FIRST_NAME VARCHAR2(20),
  3                               LAST_NAME VARCHAR2(25) NOT NULL,
  4                               EMAIL  VARCHAR2(25) NOT NULL,
  5                               PHONE_NUMBER  VARCHAR2(20),
  6                               HIRE_DATE  DATE NOT NULL,
  7                               JOB_ID  VARCHAR2(10) NOT NULL,
  8                               SALARY  NUMBER(8,2),
  9                               COMMISSION_PCT NUMBER(2,2),
 10                               MANAGER_ID NUMBER(6,0),
 11                               DEPARTMENT_ID NUMBER(4,0))
 12                               ORGANIZATION INDEX;

Tabela criada.

SQL >
SQL > INSERT INTO dbtw_employees SELECT * FROM hr.employees where rownum < 61;

60 linhas criadas.

SQL >
SQL > COMMIT;

Commit concluído.

SQL >
SQL > exec dbms_stats.gather_table_stats(ownname=>USER, tabname=>'DBTW_EMPLOYEES', method_opt=>FOR ALL COLUMNS SIZE AUTO', cascade=>true);

Procedimento PL/SQL concluído com sucesso.

SQL >
SQL >   ALTER SESSION SET statistics_level=ALL;

Sessão alterada.

SQL >
SQL > SELECT /* dbtw003  */ first_name, job_id, salary
  2    FROM dbtw_employees
  3   WHERE employee_id=143;

FIRST_NAME           JOB_ID         SALARY
-------------------- ---------- ----------
Randall              ST_CLERK         2600

SQL >
SQL > column sql_id new_value m_sql_id
SQL > column child_number new_value m_child_no
SQL >
SQL > SELECT sql_id, child_number
  2    FROM v$sql
  3   WHERE sql_text LIKE '
%dbtw003%'
  4     AND sql_text NOT LIKE '
%v$sql%';

SQL_ID        CHILD_NUMBER
------------- ------------
4q5p4cbkrm6wj            0

SQL >
SQL >
SQL > SELECT *
  2    FROM TABLE (dbms_xplan.display_cursor ('
&m_sql_id',&m_child_no,'typical iostats last'));
antigo   2:   FROM TABLE (dbms_xplan.display_cursor ('
&m_sql_id',&m_child_no,'typical iostats last'))
novo   2:   FROM TABLE (dbms_xplan.display_cursor ('
4q5p4cbkrm6wj',         0,'typical iostats last'))

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  4q5p4cbkrm6wj, child number 0
-------------------------------------
SELECT /* dbtw003  */ first_name, job_id, salary   FROM dbtw_employees
WHERE employee_id=:"SYS_B_0"

Plan hash value: 4091736222

-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation         | Name               | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                    |      1 |        |       |     1 (100)|      1 |00:00:00.01 |       1 |
|*  1 |  INDEX UNIQUE SCAN| SYS_IOT_TOP_488246 |      1 |      1 |    24 |     0   (0)|      1 |00:00:00.01 |       1 |
-----------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("EMPLOYEE_ID"=:SYS_B_0)


19 linhas selecionadas.

SQL >

 

Verificando o plano de execução parece que encontramos a vencedora dessa disputa, utilizando a tabela IOT para recuperar uma linha da tabela foi necessária a leitura de 1 Buffer.

4) Lendo uma linha da tabela usando TABLE FULL SCAN

Por ultimo vamos criar a mesma tabela sem índices e verficar qual será o desempenho de um TABLE FULL SCAN para recuperar a mesma linha da tabela.

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
SQL > DROP TABLE dbtw_employees purge;

Tabela eliminada.

SQL >
SQL >
SQL > CREATE TABLE dbtw_employees  (EMPLOYEE_ID NUMBER(6,0) NOT NULL,
  2                               FIRST_NAME VARCHAR2(20),
  3                               LAST_NAME VARCHAR2(25) NOT NULL,
  4                               EMAIL  VARCHAR2(25) NOT NULL,
  5                               PHONE_NUMBER  VARCHAR2(20),
  6                               HIRE_DATE  DATE NOT NULL,
  7                               JOB_ID  VARCHAR2(10) NOT NULL,
  8                               SALARY  NUMBER(8,2),
  9                               COMMISSION_PCT NUMBER(2,2),
 10                               MANAGER_ID NUMBER(6,0),
 11                               DEPARTMENT_ID NUMBER(4,0));

Tabela criada.

SQL >
SQL >
SQL > INSERT INTO dbtw_employees SELECT * FROM hr.employees where rownum < 61;

60 linhas criadas.

SQL >
SQL > COMMIT;

Commit concluído.

SQL >
SQL > exec dbms_stats.gather_table_stats(ownname=>USER, tabname=>'DBTW_EMPLOYEES', method_opt=>FOR ALL COLUMNS SIZE AUTO', cascade=>true);

Procedimento PL/SQL concluído com sucesso.

SQL >
SQL > ALTER SESSION SET statistics_level=ALL;

Sessão alterada.

SQL >
SQL > SELECT /* dbtw004  */ first_name, job_id, salary
  2    FROM dbtw_employees
  3   WHERE employee_id=143;

FIRST_NAME           JOB_ID         SALARY
-------------------- ---------- ----------
Randall              ST_CLERK         2600

SQL >
SQL > column sql_id new_value m_sql_id
SQL > column child_number new_value m_child_no
SQL >
SQL > SELECT sql_id, child_number
  2    FROM v$sql
  3   WHERE sql_text LIKE '
%dbtw004%'
  4     AND sql_text NOT LIKE '
%v$sql%';

SQL_ID        CHILD_NUMBER
------------- ------------
2vnw6mgfa490h            0

SQL >
SQL >
SQL > SELECT *
  2    FROM TABLE (dbms_xplan.display_cursor ('
&m_sql_id',&m_child_no,'typical iostats last'));
antigo   2:   FROM TABLE (dbms_xplan.display_cursor ('
&m_sql_id',&m_child_no,'typical iostats last'))
novo   2:   FROM TABLE (dbms_xplan.display_cursor ('
2vnw6mgfa490h',         0,'typical iostats last'))

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  2vnw6mgfa490h, child number 0
-------------------------------------
SELECT /* dbtw004  */ first_name, job_id, salary   FROM dbtw_employees
WHERE employee_id=:"SYS_B_0"

Plan hash value: 1134787269

------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation         | Name           | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                |      1 |        |       |     2 (100)|          |      1 |00:00:00.01 |       7 |
|*  1 |  TABLE ACCESS FULL| DBTW_EMPLOYEES |      1 |      1 |    24 |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       7 |
------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("EMPLOYEE_ID"=:SYS_B_0)


19 linhas selecionadas.

SQL >

 

Consultando o plano de execução verificamos que o desempenho de uma operação TABLE FULL SCAN no nosso caso é muito ruim, foram necessárias a leitura de 7 Buffers para retorna um linha da tabela.

Conclusão

O melhor método de acesso para recuperar uma linha da tabela em nossas simulações foi a utilização da tabela IOT. Nos próximos trabalhos de Tuning quando se deparar com uma tabela pequena que seja muito acessada pela aplicação e que consuma boa parte dos recursos do banco, considere a utilização de tabela IOT.

Referências:

https://docs.oracle.com/cd/E25054_01/server.1111/e25789/indexiot.htm

 
 
 
Promo-D75L

video-02
 
 
 
 
 
 
 
 
 
 
 
 

Deixe um comentário

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *