Quando usar os Function-Based Indexes?

Índice
Function-Based Indexes

Um dos problemas mais comuns encontrados num processo de tuning de instruções SQL são as operações FULL TABLE SCAN que muitas vezes são escolhidas pelo Otimizador por falta de opção já que não consegue utilizar um índice de uma determinada coluna pois o programador incluiu na cláusula WHERE uma função para essa coluna. A partir da versão Oracle 8i foi introduzido o recurso FUNCTION-BASED INDEXES que permitiu a criação de um índice onde os valores armazenados para a coluna indexada é o resultado da função incluída na cláusula WHERE o que possibilitou a utilização deste índice pelo Otimizador.

Por que usar FUNCTION-BASED INDEXES?

  1. É muito fácil utilizar este recurso basta criar um índice utilizando na coluna a mesma função presente na cláusula WHERE;
  2. Você pode utilizar este recurso como uma estratégia de Tuning de instrução SQL sem que seja necessário alterar uma linha de código;

Como habilitar o FUNCTION-BASED INDEXES?

  1. O usuário precisa ter os privilégios QUERY REWRITE e GLOBAL QUERY REWRITE para criar um FUNCTION-BASED INDEX numa tabela do seu esquema;
  2. Para o Otimizador utilizar FUNCTION-BASED INDEXES, é necessário definir os parâmetros abaixo a nível de sessão ou sistema:
    • QUERY_REWRITE_ENABLED=TRUE
    • QUERY_REWRITE_INTEGRITY=TRUSTED

 

Exemplo de FBI com UPPER()

 

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
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
SQL > SELECT * FROM V$VERSION where rownum < 2;

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

SQL >
SQL > create index EMP_FNAME_IX2 on EMPLOYEES(FIRST_NAME);

Index created.

SQL > ALTER SESSION SET statistics_level=ALL;

Session altered.

SQL > SELECT /* tst101 */ FIRST_NAME, LAST_NAME, MANAGER_ID
  2    FROM EMPLOYEES
  3   WHERE UPPER(FIRST_NAME) = 'KIMBERELY';

FIRST_NAME           LAST_NAME                 MANAGER_ID
-------------------- ------------------------- ----------
Kimberely            Grant                            149

SQL >
SQL >
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 '%tst101%'
  4     AND sql_text NOT LIKE '%v$sql%';

SQL_ID        CHILD_NUMBER
------------- ------------
c2a3qmrxcbnwj            0

SQL >
SQL >
SQL > SELECT *
  2    FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'basic iostats last'));

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
SELECT /* tst101 */ FIRST_NAME, LAST_NAME, MANAGER_ID   FROM EMPLOYEES
WHERE UPPER(FIRST_NAME) = :"SYS_B_0"

Plan hash value: 1445457117

-----------------------------------------------------------------------------------------
| Id  | Operation         | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |      1 |        |      1 |00:00:00.01 |       4 |
|*  1 |  TABLE ACCESS FULL| EMPLOYEES |      1 |      1 |      1 |00:00:00.01 |       4 |
-----------------------------------------------------------------------------------------

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

   1 - filter(UPPER("FIRST_NAME")=:SYS_B_0)


19 rows selected.

SQL >
SQL > create index EMP_FNAME_U_IX2 on EMPLOYEES(UPPER(FIRST_NAME));

Index created.

SQL > ALTER SESSION SET QUERY_REWRITE_ENABLED=TRUE;

Session altered.

SQL > ALTER SESSION SET QUERY_REWRITE_INTEGRITY=TRUSTED;

Session altered.

SQL > SELECT /* tst003 */ FIRST_NAME, LAST_NAME, MANAGER_ID
  2    FROM EMPLOYEES
  3   WHERE UPPER(FIRST_NAME) = 'KIMBERELY';

FIRST_NAME           LAST_NAME                 MANAGER_ID
-------------------- ------------------------- ----------
Kimberely            Grant                            149

SQL >
SQL >
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 '%tst003%'
  4     AND sql_text NOT LIKE '%v$sql%';

SQL_ID        CHILD_NUMBER
------------- ------------
7u8aw59ma0v1k            0

SQL >
SQL >
SQL > SELECT *
  2    FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'basic iostats last'));

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
SELECT /* tst003 */ FIRST_NAME, LAST_NAME, MANAGER_ID   FROM EMPLOYEES
WHERE UPPER(FIRST_NAME) = :"SYS_B_0"

Plan hash value: 1892810479

---------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name            | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                 |      1 |        |      1 |00:00:00.01 |       3 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES       |      1 |      1 |      1 |00:00:00.01 |       3 |
|*  2 |   INDEX RANGE SCAN          | EMP_FNAME_U_IX2 |      1 |      1 |      1 |00:00:00.01 |       2 |
---------------------------------------------------------------------------------------------------------

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

   2 - access("EMPLOYEES"."SYS_NC00012$"=:SYS_B_0)


20 rows selected.

SQL >
SQL >
SQL > drop index EMP_FNAME_IX2;

Index dropped.

SQL > drop index EMP_FNAME_U_IX2;

Index dropped.

SQL >

 

Exemplo de FBI com TRUNC()

 

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
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
SQL > @desc ORDERS
 Name                                Null?    Type
 ----------------------------------- -------- ------------------------
 ORDER_ID                            NOT NULL NUMBER(12)
 ORDER_DATE                          NOT NULL TIMESTAMP(6) WITH LOCAL
                                              TIME ZONE
 ORDER_MODE                                   VARCHAR2(8)
 CUSTOMER_ID                         NOT NULL NUMBER(6)
 ORDER_STATUS                                 NUMBER(2)
 ORDER_TOTAL                                  NUMBER(8,2)
 SALES_REP_ID                                 NUMBER(6)
 PROMOTION_ID                                 NUMBER(6)

SQL >
SQL >
SQL > ALTER SESSION SET statistics_level=ALL;

Session altered.

SQL >
SQL > SELECT /* tst001 */ ORDER_ID, ORDER_TOTAL, ORDER_DATE
  2    FROM ORDERS
  3   WHERE TRUNC(ORDER_DATE) = '02-OCT-07';

  ORDER_ID ORDER_TOTAL ORDER_DATE
---------- ----------- ---------------------------------------------------------------------------
      2454      6653.4 02-OCT-07 08.49.34.678340 PM
      2430     29669.9 02-OCT-07 09.18.36.663332 AM

2 rows selected.

SQL >
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 '%tst001%'
  4     AND sql_text NOT LIKE '%v$sql%';

SQL_ID        CHILD_NUMBER
------------- ------------
04vjn6ffqf9ns            0

1 row selected.

SQL >
SQL >
SQL > SELECT *
  2    FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'basic iostats last'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
SELECT /* tst001 */ ORDER_ID, ORDER_TOTAL, ORDER_DATE   FROM ORDERS
WHERE TRUNC(ORDER_DATE) = :"SYS_B_0"

Plan hash value: 1275100350

--------------------------------------------------------------------------------------
| Id  | Operation         | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |      1 |        |      2 |00:00:00.01 |       3 |
|*  1 |  TABLE ACCESS FULL| ORDERS |      1 |      1 |      2 |00:00:00.01 |       3 |
--------------------------------------------------------------------------------------

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

   1 - filter(TRUNC(INTERNAL_FUNCTION("ORDER_DATE"))=:SYS_B_0)


19 rows selected.

SQL >
SQL > ------------------------------------------------------------------------------------------------------------------------------------
SQL > ------------------------------------------------------------------------------------------------------------------------------------
SQL > create index ORD_ORDER_DATEF_IX on ORDERS(TRUNC("ORDER_DATE"));

Index created.

SQL > SELECT /* tst002 */ ORDER_ID, ORDER_TOTAL, ORDER_DATE
  2    FROM ORDERS
  3   WHERE TRUNC(ORDER_DATE) = '02-OCT-07';

  ORDER_ID ORDER_TOTAL ORDER_DATE
---------- ----------- ---------------------------------------------------------------------------
      2454      6653.4 02-OCT-07 08.49.34.678340 PM
      2430     29669.9 02-OCT-07 09.18.36.663332 AM

2 rows selected.

SQL >
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 '%tst002%'
  4     AND sql_text NOT LIKE '%v$sql%';

SQL_ID        CHILD_NUMBER
------------- ------------
06nprpfdu35g0            0

1 row selected.

SQL >
SQL >
SQL > SELECT *
  2    FROM TABLE (dbms_xplan.display_cursor ('&m_sql_id',&m_child_no,'basic iostats last'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
SELECT /* tst002 */ ORDER_ID, ORDER_TOTAL, ORDER_DATE   FROM ORDERS
WHERE TRUNC(ORDER_DATE) = :"SYS_B_0"

Plan hash value: 2271898552

------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name               | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                    |      1 |        |      2 |00:00:00.01 |       4 |
|   1 |  TABLE ACCESS BY INDEX ROWID| ORDERS             |      1 |      1 |      2 |00:00:00.01 |       4 |
|*  2 |   INDEX RANGE SCAN          | ORD_ORDER_DATEF_IX |      1 |      1 |      2 |00:00:00.01 |       2 |
------------------------------------------------------------------------------------------------------------

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

   2 - access("ORDERS"."SYS_NC00009$"=:SYS_B_0)


20 rows selected.

SQL >
SQL >
SQL >
SQL >
SQL > drop index ORD_ORDER_DATEF_IX;

Index dropped.

SQL >

 

Exemplo de FBI com DECODE()

 

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
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
SQL> CREATE TABLE T1 AS SELECT * FROM ALL_OBJECTS WHERE OBJECT_TYPE IN ('SYNONYM','JAVA','INDEX','TABLE','EDITION');

Tabela criada.

SQL> CREATE INDEX T1_IDX ON T1(OBJECT_TYPE);

Índice criado.

SQL> exec dbms_stats.gather_table_stats(ownname => 'CURSO01',tabname => 'T1', ESTIMATE_PERCENT=>100 ,method_opt=>'FOR ALL INDEXED COLUMNS SIZE 254',cascade=>true);

Procedimento PL/SQL concluído com sucesso.

SQL> ALTER SESSION SET statistics_level=ALL;

Sessão alterada.

SQL> select /* tst001 */ OBJECT_NAME, CREATED
  2    from T1
  3   where DECODE(OBJECT_TYPE,'SYNONYM',10,'JAVA',20,'INDEX',30,'TABLE',40,'EDITION',50) = 50;

OBJECT_NAME                    CREATED
------------------------------ --------
ORA$BASE                       25/08/13

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 '%tst001%'
  4     AND sql_text NOT LIKE '%v$sql%';

SQL_ID        CHILD_NUMBER
------------- ------------
46cgdpzwtnc60            0

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

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select /* tst001 */ OBJECT_NAME, CREATED   from T1  where
DECODE(OBJECT_TYPE,'SYNONYM',10,'JAVA',20,'INDEX',30,'TABLE',40,'EDITION
',50) = 50

Plan hash value: 3617692013

---------------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |      1 |00:00:00.02 |     592 |    589 |
|*  1 |  TABLE ACCESS FULL| T1   |      1 |    410 |      1 |00:00:00.02 |     592 |    589 |
---------------------------------------------------------------------------------------------

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

   1 - filter(DECODE("OBJECT_TYPE",'SYNONYM',10,'JAVA',20,'INDEX',30,'TABLE',40,'EDIT
              ION',50)=50)


21 linhas selecionadas.

SQL>
SQL> --------------------------------------------------------------------------------
SQL>
SQL> CREATE INDEX T1_FBI_IDX ON T1( DECODE(OBJECT_TYPE,'SYNONYM',10,'JAVA',20,'INDEX',30,'TABLE',40,'EDITION',50));

Índice criado.

SQL> SELECT INDEX_NAME, COLUMN_EXPRESSION
  2    FROM USER_IND_EXPRESSIONS
  3   WHERE INDEX_NAME='T1_FBI_IDX';

INDEX_NAME                     COLUMN_EXPRESSION
------------------------------ --------------------------------------------------------------------------------
T1_FBI_IDX                     DECODE("OBJECT_TYPE",'SYNONYM',10,'JAVA',20,'INDEX',30,'TABLE',40,'EDITION',50)

SQL>
SQL> ALTER SESSION SET QUERY_REWRITE_ENABLED=TRUE;

Sessão alterada.

SQL> ALTER SESSION SET QUERY_REWRITE_INTEGRITY=TRUSTED;

Sessão alterada.

SQL> select /* tst002 */ OBJECT_NAME, CREATED
  2    from T1
  3   where DECODE(OBJECT_TYPE,'SYNONYM',10,'JAVA',20,'INDEX',30,'TABLE',40,'EDITION',50) = 50;

OBJECT_NAME                    CREATED
------------------------------ --------
ORA$BASE                       25/08/13

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 '%tst002%'
  4     AND sql_text NOT LIKE '%v$sql%';

SQL_ID        CHILD_NUMBER
------------- ------------
42s8f39knwb63            0

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

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select /* tst002 */ OBJECT_NAME, CREATED   from T1  where
DECODE(OBJECT_TYPE,'SYNONYM',10,'JAVA',20,'INDEX',30,'TABLE',40,'EDITION
',50) = 50

Plan hash value: 2651436047

-------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |      1 |        |      1 |00:00:00.01 |       3 |      6 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1         |      1 |    410 |      1 |00:00:00.01 |       3 |      6 |
|*  2 |   INDEX RANGE SCAN          | T1_FBI_IDX |      1 |    164 |      1 |00:00:00.01 |       2 |      1 |
-------------------------------------------------------------------------------------------------------------

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

   2 - access("T1"."SYS_NC00016$"=50)


21 linhas selecionadas.

SQL> drop table t1;

Tabela eliminada.

SQL>

 

Exemplo de FBI com NVL()

 

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
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
SQL> CREATE TABLE T1 AS SELECT * FROM ALL_OBJECTS;

Tabela criada.

SQL> UPDATE T1 SET OBJECT_TYPE = '' WHERE OBJECT_TYPE = 'CLUSTER';

10 linhas atualizadas.

SQL> COMMIT;

Commit concluído.

SQL> CREATE INDEX T1_IDX ON T1(OBJECT_TYPE);

Índice criado.

SQL> exec dbms_stats.gather_table_stats(ownname => 'CURSO01',tabname => 'T1', ESTIMATE_PERCENT=>100 ,method_opt=>'FOR ALL INDEXED COLUMNS SIZE 254',cascade=>true);

Procedimento PL/SQL concluído com sucesso.

SQL> ALTER SESSION SET statistics_level=ALL;

Sessão alterada.

SQL> select /* tst101 */ OBJECT_NAME, CREATED
  2    from T1
  3   where NVL(OBJECT_TYPE,'NULL') = 'NULL';

OBJECT_NAME                    CREATED
------------------------------ --------
C_COBJ#                        25/08/13
C_TS#                          25/08/13
C_FILE#_BLOCK#                 25/08/13
C_USER#                        25/08/13
C_OBJ#                         25/08/13
SMON_SCN_TO_TIME_AUX           25/08/13
C_OBJ#_INTCOL#                 25/08/13
C_TOID_VERSION#                25/08/13
C_MLOG#                        25/08/13
C_RG#                          25/08/13

10 linhas selecionadas.

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 '%tst101%'
  4     AND sql_text NOT LIKE '%v$sql%';

SQL_ID        CHILD_NUMBER
------------- ------------
6v62jdzdh8jhy            0

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

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select /* tst101 */ OBJECT_NAME, CREATED   from T1  where
NVL(OBJECT_TYPE,'NULL') = 'NULL'

Plan hash value: 3617692013

------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |     10 |00:00:00.01 |    1137 |
|*  1 |  TABLE ACCESS FULL| T1   |      1 |     11 |     10 |00:00:00.01 |    1137 |
------------------------------------------------------------------------------------

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

   1 - filter(NVL("OBJECT_TYPE",'NULL')='NULL')


19 linhas selecionadas.

SQL>
SQL> --------------------------------------------------------------------------------
SQL>
SQL> CREATE INDEX T1_FBI_IDX ON T1( NVL(OBJECT_TYPE,'NULL') );

Índice criado.

SQL>
SQL> SELECT INDEX_NAME, COLUMN_EXPRESSION
  2    FROM USER_IND_EXPRESSIONS
  3   WHERE INDEX_NAME='T1_FBI_IDX';

INDEX_NAME                     COLUMN_EXPRESSION
------------------------------ --------------------------------------------------------------------------------
T1_FBI_IDX                     NVL("OBJECT_TYPE",'NULL')

SQL>
SQL> ALTER SESSION SET QUERY_REWRITE_ENABLED=TRUE;

Sessão alterada.

SQL> ALTER SESSION SET QUERY_REWRITE_INTEGRITY=TRUSTED;

Sessão alterada.

SQL> select /* tst102 */ OBJECT_NAME, CREATED
  2    from T1
  3   where NVL(OBJECT_TYPE,'NULL') = 'NULL';

OBJECT_NAME                    CREATED
------------------------------ --------
C_COBJ#                        25/08/13
C_TS#                          25/08/13
C_FILE#_BLOCK#                 25/08/13
C_USER#                        25/08/13
C_OBJ#                         25/08/13
SMON_SCN_TO_TIME_AUX           25/08/13
C_OBJ#_INTCOL#                 25/08/13
C_TOID_VERSION#                25/08/13
C_MLOG#                        25/08/13
C_RG#                          25/08/13

10 linhas selecionadas.

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 '%tst102%'
  4     AND sql_text NOT LIKE '%v$sql%';

SQL_ID        CHILD_NUMBER
------------- ------------
5ymy60w0vy796            0

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

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select /* tst102 */ OBJECT_NAME, CREATED   from T1  where
NVL(OBJECT_TYPE,'NULL') = 'NULL'

Plan hash value: 2651436047

-------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |      1 |        |     10 |00:00:00.01 |      10 |      7 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1         |      1 |    789 |     10 |00:00:00.01 |      10 |      7 |
|*  2 |   INDEX RANGE SCAN          | T1_FBI_IDX |      1 |    316 |     10 |00:00:00.01 |       3 |      7 |
-------------------------------------------------------------------------------------------------------------

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

   2 - access("T1"."SYS_NC00016$"='NULL')


20 linhas selecionadas.

SQL>
SQL> drop table t1;

Tabela eliminada.

SQL>
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:

https://dbtimewizard.com.br/mentoria

 

Conclusão

Nesse artigo ilustramos o poder do recurso FUNCTION-BASED INDEXES com alguns exemplos, existem outras possibilidades de utilização desse recurso, é notório o ganho de performance das instruções SQL depois que passam a utilizar um FBI, basta observar a quantidade de blocos acessados no plano de execução antes e depois da criação e utilização do FBI.

 

 

Referências

https://docs.oracle.com/cd/E11882_01/appdev.112/e41502/adfns_indexes.htm#ADFNS00505

 
 
Promo-D75L

video-02
 
 
 
 
 
 
 
 
 
 
 
 
 
 

Artigos semelhantes:

  • índice composto
    A ordem das colunas num índice composto faz diferença?

    Em um artigo anterior já mostramos a importância do índice composto para melhorar o desempenho de uma instrução SQL, agora vamos estudar um pouco como a ordem das colunas no índice composto pode impactar o desempenho de uma instrução SQL. A ordem das colunas no índice composto é importante, no…

  • Índice composto
    Quando devemos criar um índice composto?

    É comum encontrar aplicações cujas tabelas possuem muitos índices unitários (uma coluna) e nenhum índice composto (duas ou mais colunas), as ultimas versões do banco de dados Oracle já trata essa deficiência de estrutura das aplicações com o uso da operação "BITMAP AND" no plano de execução, mas em alguns…

Leave a comment

Deixe um comentário

O seu endereço de e-mail não será publicado.