Question: I have a SQL statement using a CASE
statement and it does not use an index. How can I make an index
that can be used by a CASE statement? Here is my SQL statement
which causes a full-table scan, but only when the CASE statement is
included:
select ...
from
TABLE_1
where ....
and SOURCE_TRAN = "PO"
and
case
SOURCE_TRAN
when 'PO' then PO_ID
when 'VOUCHER' then voucher_id
ELSE journal_id
end = '0000000001'
The table has regular indexes defined, based on PO, VOUCHER &
Journal. I've found that, without the CASE statement in the SQL, the
SOURCE_TRAN index is used, but the index is not used when I add the CASE
statement to the SQL. How can I use a CASE expression to index this
query?
Answer: Using function-based indexes (FBI) you
can create an index on any built-in function, including a CASE
expression. Here we use CASE within the create index
syntax:
create index
case_index as
(case SOURCE_TRAN
when 'PO' then PO_ID
when 'VOUCHER' then voucher_id
ELSE journal_id
end = '0000000001'
END);
Once created, you need to create CBO
statistics, but beware that there are numerous bugs and issues when
analyzing a function-based index. See these important notes on
statistics and
function-based indexes:
EXEC DBMS_STATS.gather_index_stats('OWNER', 'CASE_INDEX');
exec dbms_stats.gather_table_stats(
ownname=>null,
tabname=> ‘CASE_TAB,
estimate_percent=>null,
cascade=>true,
method_opt=> ‘FOR ALL HIDDEN COLUMNS SIZE 1′
);
exec dbms_stats.gather_table_stats(
ownname =>
'OWNER',
tabname => 'CASE_TAB',
cascade => TRUE);
As a final step, run the execution plan for the
query and ensure that your SQL with CASE is using the appropriate index.
Followup: Thanks
B, FBI works great!!!
See these additional notes on using function-based indexes here.
-
-
-
-
-
