logo
Entre em contato e saiba mais sobre como a DataUnique pode transformar seu negócio.

Fale Conosco

contato@dataunique.com.br
(62) 3932-2512
banner-winthor-sidebar
 

Banco de dados Oracle – Dicas sobre índices

Banco de dados Oracle – Dicas sobre índices

banner-winthor-header

Introdução

Para alguns predicados quais índices são indicados?

  • “IS NULL”, criei o índice mas não está sendo utilizado?!
  • “WHERE ID=500 AND COD=3” ou “WHERE COD=3”. Qual ordem das colunas na criação do índice?
  • “STATUS=’ATIVO’”. Sendo status com baixa cardinalidade.

Dicas 1 – “IS NULL”, criei o índice mas não está sendo utilizado?!

  • Podemos observar que a coluna “NULO” é indexada (linha 5 do documento), mesmo com a seletividade bem baixa de 0,05%, foi realizado FTS.
  • Índices B-Tree não indexa valores nulos.
  • Criando o índice “IDX2_TESTE01_ISNULL” composto com uma constante, podemos contornar isso.

 

-- Preparação do ambiente
SQL> CREATE TABLE TESTE01 AS SELECT ' ' AS NULO,'DATAUNIQUE' NAME FROM DUAL CONNECT BY LEVEL <= 1000000;
SQL> UPDATE TESTE01 SET NULO=null;
SQL> UPDATE TESTE01 SET NULO='A' WHERE ID >= 500;
SQL> CREATE INDEX IDX1_TESTE01_ISNULL ON TESTE01(NULO);
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>NULL, TABNAME=>'TESTE01', ESTIMATE_PERCENT=> NULL, METHOD_OPT=> 'FOR ALL COLUMNS SIZE 1');


SQL>SELECT INDEX_NAME, BLEVEL, LEAF_BLOCKS, CLUSTERING_FACTOR FROM USER_INDEXES WHERE INDEX_NAME LIKE '%TESTE01%';

INDEX_NAME BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR
---------------------------------------- ---------- ----------- -----------------
IDX1_TESTE01_ISNULL 2 1811 3582





SQL>SELECT * FROM TESTE01 WHERE NULO IS NULL;

501 linhas selecionadas.


Plano de Execução
----------------------------------------------------------
Plan hash value: 3707836917

-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 501 | 10521 | 1036 (7)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TESTE01 | 501 | 10521 | 1036 (7)| 00:00:01 |
-----------------------------------------------------------------------------

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

1 - filter("NULO" IS NULL)


Estatísticas
----------------------------------------------------------
19 recursive calls
0 db block gets
3645 consistent gets
0 physical reads
0 redo size
12959 bytes sent via SQL*Net to client
971 bytes received via SQL*Net from client
35 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
501 rows processed
----------------------------------------------------------

SQL> CREATE INDEX IDX2_TESTE01_ISNULL ON TESTE01(NULO,1);


SQL>SELECT INDEX_NAME, BLEVEL, LEAF_BLOCKS,CLUSTERING_FACTOR FROM USER_INDEXES WHERE INDEX_NAME LIKE '%TESTE01%';

INDEX_NAME BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR
---------------------------------------- ---------- ----------- -----------------
IDX1_TESTE01_ISNULL 2 1811 3582
IDX2_TESTE01_ISNULL 2 2092 3585


SQL>SELECT * FROM TESTE01 WHERE NULO IS NULL;

501 linhas selecionadas.


Plano de Execução
----------------------------------------------------------
Plan hash value: 525699655

-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 501 | 10521 | 6 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| TESTE01 | 501 | 10521 | 6 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX2_TESTE01_ISNULL | 501 | | 4 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------

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

2 - access("NULO" IS NULL)


Estatísticas
----------------------------------------------------------
2 recursive calls
0 db block gets
81 consistent gets
0 physical reads
0 redo size
12959 bytes sent via SQL*Net to client 
971 bytes received via SQL*Net from client
35 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
501 rows processed

Dicas 2 – “WHERE ID=500 AND COD=3” ou “WHERE COD=3”. Qual ordem das colunas na criação do índice?

  • Não importando a ordem das colunas podemos observar o mesmo planos e o mesmo número de consistente gets.
  • Utilizando apenas o COD no WHERE, no índice com a coluna mais distinta primeiro, ocorreu FTS, mas IDX1_TESTE01_COD_ID foi usado, como é possível se ambos índices tem a coluna COD?

— trace dump do índice
SELECT object_id FROM user_objectsWHERE object_name = IDX1_TESTE01_ID_COD ‘;

ALTER SESSION SET EVENTS ‘immediate trace name treedump level object_id’;

—– begin tree dump
branch: 0x417ca9 4291753 (0: nrow: 5, level: 2) — root block
branch: 0x418caf 4295855 (-1: nrow: 556, level: 1)
leaf: 0x417caa 4291754 (-1: row:425.425 avs:824)
—– fim

SQL> select dbms_utility.data_block_address_file(4291753), dbms_utility.data_block_address_block(4291753) from dual;

— dump do nó raiz
SQL> alter system dump datafile 1 block 97456;

“v$diag_info” -> para informações da localização do dump.

  • Temos agora o dump do nó raiz de ambos os índices. Notem que o índice IDX1_TESTE01_ID_COD não indexa a coluna COD, já que a coluna ID é suficiente.
  -- preparação do ambiente de testes  					
 CREATE TABLE TESTE02 AS SELECT mod(rownum,500000) id, mod(rownum,10000) cod, 'DATAUNIQUE' name FROM dual CONNECT BY LEVEL <= 1000000;  					
   					
 -- Para os predicados citados, é comum o pensamento de colocar ID na primeira posição do indice	 Mas			   	
 -- Indices possiveis	  				
   					
 SQL> CREATE INDEX IDX1_TESTE01_ID_COD ON TESTE01(ID,COD);  					
 SQL> CREATE INDEX IDX1_TESTE01_COD_ID ON TESTE01(COD,ID) INVISIBLE;  					
   					
 -- Coletando estatísticas  					
 EXEC DBMS_STATS	GATHER_TABLE_STATS(OWNNAME=>NULL, TABNAME=>'TESTE01', ESTIMATE_PERCENT=> NULL, METHOD_OPT=> 'FOR ALL COLUMNS SIZE 1');   				
   					
 -- distribuição   					
 SQL>SELECT TABLE_NAME, COLUMN_NAME, NUM_DISTINCT FROM DBA_TAB_COLS WHERE OWNER = 'SYS' AND TABLE_NAME LIKE 'TESTE01' AND COLUMN_NAME IN ('COD','ID');  					
   					
 TABLE_NAME                     COLUMN_NAME                    NUM_DISTINCT  					
 ------------------------------ ------------------------------ ------------  					
 TESTE01                        ID                                   500000   (Mais valores distintos)					
 TESTE01                        COD                                    1000  					
    					
   					
					
 					
 SQL>SELECT * FROM TESTE01 WHERE ID=500 AND COD=3;  					
   					
 não há linhas selecionadas  					
   					
 Plano de Execução  					
 ----------------------------------------------------------  					
 Plan hash value: 2374796228  					
   					
 -----------------------------------------------------------------------------------------------------------  					
 | Id  | Operation                           | Name                | Rows  | Bytes | Cost (%CPU)| Time     |  					
 -----------------------------------------------------------------------------------------------------------  					
 |   0 | SELECT STATEMENT                    |                     |     2 |    40 |     5   (0)| 00:00:01 |  					
 |   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| TESTE01             |     2 |    40 |     5   (0)| 00:00:01 |  					
 |*  2 |   INDEX RANGE SCAN                  | IDX1_TESTE01_ID_COD |     2 |       |     3   (0)| 00:00:01 |  					
 -----------------------------------------------------------------------------------------------------------  					
   					
 Predicate Information (identified by operation id):  					
 ---------------------------------------------------  					
   					
    2 - access("ID"=500 AND "COD"=3)  					
   					
   					
 Estatísticas  					
 ----------------------------------------------------------  					
           0  recursive calls  					
           0  db block gets  					
           3  consistent gets  					
           0  physical reads  					
           0  redo size  					
         477  bytes sent via SQL*Net to client  					
         597  bytes received via SQL*Net from client  					
           1  SQL*Net roundtrips to/from client  					
           0  sorts (memory)  					
           0  sorts (disk)  					
           0  rows processed  					
   					
 ----------------------------------------------------------  					
    					
 					
 SQL>SELECT * FROM TESTE01 WHERE COD=3;  					
   					
 100 linhas selecionadas	  				
   					
   					
 Plano de Execuão  					
 ----------------------------------------------------------  					
 Plan hash value: 3707836917  					
   					
 -----------------------------------------------------------------------------  					
 | Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |  					
 -----------------------------------------------------------------------------  					
 |   0 | SELECT STATEMENT  |         |   100 |  2000 |  1003   (7)| 00:00:01 |  					
 |*  1 |  TABLE ACCESS FULL| TESTE01 |   100 |  2000 |  1003   (7)| 00:00:01 |  					
 -----------------------------------------------------------------------------  					
   					
 Predicate Information (identified by operation id):  					
 ---------------------------------------------------  					
   					
    1 - filter("COD"=3)  					
   					
   					
 Estatísticas  					
 ----------------------------------------------------------  					
           0  recursive calls  					
           0  db block gets  					
        3469  consistent gets  					
           0  physical reads  					
           0  redo size  					
        2883  bytes sent via SQL*Net to client  					
         674  bytes received via SQL*Net from client  					
           8  SQL*Net roundtrips to/from client  					
           0  sorts (memory)  					
           0  sorts (disk)  					
         100  rows processed  					
   					
 ----------------------------------------------------------  					
        SQL> ALTER INDEX IDX1_TESTE01_ID_COD INVISIBLE;  					
 SQL> ALTER INDEX IDX1_TESTE01_COD_ID VISIBLE;  					
   					
 SQL>SELECT * FROM TESTE01 WHERE ID=500 AND COD=3;  					
   					
 não há linhas selecionadas  					
   					
   					
 Plano de Execução  					
 ----------------------------------------------------------  					
 Plan hash value: 3469596195  					
   					
 -----------------------------------------------------------------------------------------------------------  					
 | Id  | Operation                           | Name                | Rows  | Bytes | Cost (%CPU)| Time     |  					
 -----------------------------------------------------------------------------------------------------------  					
 |   0 | SELECT STATEMENT                    |                     |     2 |    40 |     5   (0)| 00:00:01 |  					
 |   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| TESTE01             |     2 |    40 |     5   (0)| 00:00:01 |  					
 |*  2 |   INDEX RANGE SCAN                  | IDX1_TESTE01_COD_ID |     2 |       |     3   (0)| 00:00:01 |  					
 -----------------------------------------------------------------------------------------------------------  					
   					
 Predicate Information (identified by operation id):  					
 ---------------------------------------------------  					
   					
    2 - access("COD"=3 AND "ID"=500)  					
   					
   					
 Estatísticas  					
 ----------------------------------------------------------  					
 0  recursive calls  					
           0  db block gets  					
           3  consistent gets  					
           0  physical reads  					
           0  redo size  					
         477  bytes sent via SQL*Net to client  					
         597  bytes received via SQL*Net from client  					
           1  SQL*Net roundtrips to/from client  					
           0  sorts (memory)  					
           0  sorts (disk)  					
           0  rows processed  					
   					
 ----------------------------------------------------------  					
 SQL>SELECT * FROM TESTE01 WHERE COD=3;  					
   					
 100 linhas selecionadas	  				
   					
   					
 Plano de Execução  					
 ----------------------------------------------------------  					
 Plan hash value: 3469596195  					
   					
 -----------------------------------------------------------------------------------------------------------  					
 | Id  | Operation                           | Name                | Rows  | Bytes | Cost (%CPU)| Time     |  					
 -----------------------------------------------------------------------------------------------------------  					
 |   0 | SELECT STATEMENT                    |                     |   100 |  2000 |   103   (0)| 00:00:01 |  					
 |   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| TESTE01             |   100 |  2000 |   103   (0)| 00:00:01 |  					
 |*  2 |   INDEX RANGE SCAN                  | IDX1_TESTE01_COD_ID |   100 |       |     3   (0)| 00:00:01 |  					
 -----------------------------------------------------------------------------------------------------------  					
   					
 Predicate Information (identified by operation id):  					
 ---------------------------------------------------  					
   					
    2 - access("COD"=3)  					
   					
   					
 Estatísticas  					
 ----------------------------------------------------------  					
           0  recursive calls  					
           0  db block gets  					
         111  consistent gets  					
           0  physical reads  					
           0  redo size  					
        2638  bytes sent via SQL*Net to client  					
         674  bytes received via SQL*Net from client  					
           8  SQL*Net roundtrips to/from client  					
           0  sorts (memory)  					
           0  sorts (disk)  					
         100  rows processed  					
 ----------------------------------------------------------  					
   					
 					
 					
Parte dump nó raiz do indice IDX1_TESTE01_ID_COD					
------------------------------------------------------------------
   Branch block dump  					
   =================  					
   header address 1903575108=0x71764044  					
   kdxcolev 2  					
   KDXCOLEV Flags = - - -  					
   kdxcolok 0  					
   kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y  					
   kdxconco 3  					
   kdxcosdc 0  					
 kdxconro 3  					
 kdxcofbo 34=0x22  					
 kdxcofeo 8026=0x1f5a  					
 kdxcoavs 7992  					
 kdxbrlmc 4298659=0x4197a3  					
 kdxbrsno 0  					
 kdxbrbksz 8056  					
 kdxbr2urrc 0  					
 row#0[8046] dba: 4299330=0x419a42  					
col 1; TERM                       ----------------------
   row#1[8036] dba: 4300001=0x419ce1  					
   col 0; len 4; (4):  c3 34 05 5a  					
   col 1; TERM  					
   row#2[8026] dba: 4300620=0x419f4c  					
   col 0; len 4; (4):  c3 4d 33 5a  					
   col 1; TERM  					
   ----- end of branch block dump -----  					
     					
   ------------------------------------------------------------------  					
   					
 Parte dump nó raiz do indice IDX1_TESTE01_COD_ID  					
 ------------------------------------------------------------------  					
 Branch block dump  					
 =================  					
 header address 1886658628=0x70742044  					
 kdxcolev 2  					
 KDXCOLEV Flags = - - -  					
 kdxcolok 0  					
 kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y  					
 kdxconco 3  					
 kdxcosdc 0  					
 kdxconro 4  					
 kdxcofbo 36=0x24  					
 kdxcofeo 8004=0x1f44  					
 kdxcoavs 7968  					
 kdxbrlmc 4295855=0x418caf  					
 kdxbrsno 0  					
 kdxbrbksz 8056  					
 kdxbr2urrc 0  					
 row#0[8043] dba: 4296396=0x418ecc  					
 col 0; len 2; (2):  c1 03  					
 col 1; len 4; (4):  c3 10 4c 3f  					
 col 2; TERM  					
 row#1[8030] dba: 4296937=0x4190e9  					
 col 0; len 2; (2):  c1 05  					
 col 1; len 4; (4):  c3 14 2d 2d  					
 col 2; TERM  					
 row#2[8017] dba: 4297478=0x419306  					
 col 0; len 2; (2):  c1 07  					
 col 1; len 4; (4):  c3 18 0e 25  					
 col 2; TERM  					
 row#3[8004] dba: 4297938=0x4194d2  					
 col 0; len 2; (2):  c1 09  					
 col 1; len 4; (4):  c3 1b 53 1d  					
 col 2; TERM

Dicas 3 – “STATUS=’ATIVO’”. Sendo status com baixa cardinalidade.

CREATE TABLE TBSTATUS(ID NUMBER, status varchar2(10));  												
  												
INSERT INTO TBSTATUS SELECT rownum,'INATIVO' FROM DUAL CONNECT BY LEVEL <= 1000000;  												
  												
UPDATE TBSTATUS SET status='ATIVO' WHERE ID > 999950;  												
  												
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>NULL, TABNAME=>'TBSTATUS', ESTIMATE_PERCENT=> NULL, METHOD_OPT=> 'FOR COLUMNS STATUS SIZE 2');   												
  												
SQL>SELECT COUNT(*),STATUS FROM TBSTATUS GROUP BY STATUS;  												
  												
  COUNT(*) STATUS  												
---------- ----------  												
    999950 INATIVO  												
        50 ATIVO  												
  												
  												
CREATE INDEX IDX_TBSTATUS_01 ON TBSTATUS(status);  												
  												
  												
SQL>SELECT * FROM TBSTATUS WHERE status = 'ATIVO';  												
  												
50 linhas selecionadas.  												
  												
  												
Plano de Execução  												
----------------------------------------------------------  												
Plan hash value: 3383094675  												
  												
-------------------------------------------------------------------------------------------------------  												
| Id  | Operation                           | Name            | Rows  | Bytes | Cost (%CPU)| Time     |  												
-------------------------------------------------------------------------------------------------------  												
|   0 | SELECT STATEMENT                    |                 |    50 |   650 |     4   (0)| 00:00:01 |  												
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| TBSTATUS        |    50 |   650 |     4   (0)| 00:00:01 |  												
|*  2 |   INDEX RANGE SCAN                  | IDX_TBSTATUS_01 |    50 |       |     3   (0)| 00:00:01 |  												
-------------------------------------------------------------------------------------------------------  												
  												
Predicate Information (identified by operation id):  												
---------------------------------------------------  												
  												
   2 - access("STATUS"='ATIVO')  												
  												
  												
Estatísticas  												
----------------------------------------------------------  												
          0  recursive calls  												
          0  db block gets  												
         12  consistent gets  												
          0  physical reads  												
          0  redo size  												
       1733  bytes sent via SQL*Net to client  												
        641  bytes received via SQL*Net from client  												
          5  SQL*Net roundtrips to/from client  												
          0  sorts (memory)  												
          0  sorts (disk)  												
         50  rows processed  												
----------------------------------------------------------  												
  												
CREATE INDEX IDX_TBSTATUS_02 ON TBSTATUS(DECODE(status, 'ATIVO', 'ATIVO', NULL));  												
  												
SQL>SELECT index_name, blevel, leaf_blocks, distinct_keys, num_rows FROM user_indexes WHERE table_name='TBSTATUS';  												
  												
INDEX_NAME                         BLEVEL LEAF_BLOCKS DISTINCT_KEYS   NUM_ROWS  												
------------------------------ ---------- ----------- ------------- ----------  												
IDX_TBSTATUS_01                         2        2653             2    1000000  												
IDX_TBSTATUS_02                         0           1             1         50  -- novamente B-tree não indexa null 												
  											  -- assim o índice ocupa menos espaço.	
  												
-- realizado esta mudança no SQL, Temos:  												
SQL>SELECT * FROM TBSTATUS WHERE DECODE(status,'ATIVO','ATIVO',null) = 'ATIVO';  												
  												
50 linhas selecionadas.  												
  												
  												
Plano de Execução  												
----------------------------------------------------------  												
Plan hash value: 3422389394  												
  												
-------------------------------------------------------------------------------------------------------  												
| Id  | Operation                           | Name            | Rows  | Bytes | Cost (%CPU)| Time     |  												
-------------------------------------------------------------------------------------------------------  												
|   0 | SELECT STATEMENT                    |                 |    50 |   700 |     2   (0)| 00:00:01 | -- Custo reduziu 50%												
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| TBSTATUS        |    50 |   700 |     2   (0)| 00:00:01 |  												
|*  2 |   INDEX RANGE SCAN                  | IDX_TBSTATUS_02 |    50 |       |     1   (0)| 00:00:01 |  												
-------------------------------------------------------------------------------------------------------  												
  												
Predicate Information (identified by operation id):  												
---------------------------------------------------  												
  												
   2 - access(DECODE("STATUS",'ATIVO','ATIVO',NULL)='ATIVO')  												
  												
  												
Estatísticas  												
----------------------------------------------------------  												
   	0  recursive calls  											
   	0  db block gets  											
   	10  consistent gets  -- diferença de 2 Consistent gets. 											
   	0  physical reads  											
   	0  redo size  											
   	1733  bytes sent via SQL*Net to client  											
641  bytes received via SQL*Net from client  												
5  SQL*Net roundtrips to/from client  												
0  sorts (memory)  												
0  sorts (disk)  												
50  rows processed

Referências:
https://docs.oracle.com/cd/E11882_01/server.112/e40540/indexiot.htm#CNCPT1170
https://docs.oracle.com/cd/E11882_01/server.112/e41084/functions049.htm#SQLRF00631
https://docs.oracle.com/database/121/REFRN/GUID-89772970-11A3-4508-A3D3-C7149B1F8642.htm#REFRN30502

Hugo Castro

hugo.castro@dataunique.com.br

Nenhum Comentário

Escreva um Comentário

Comentário
Nome
Email
Website