05
jun
Banco de dados Oracle – Dicas sobre índices
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