A utilização do índice B-TREE com uma coluna ou com um grupo de colunas é muito comum nas aplicações, estas colunas são referenciadas na cláusula WHERE das instruções SQL e possibilitam uma rápida recuperação dos dados que o usuário deseja, mas existem outras formas de utilizar os índices que são pouco…
Você tambem menospreza esta questão?
É 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
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
