Use of functional indexes in Oracle

This was a question for Tom, but he is busy right now.

I am not a DBA, just a simple Java EE developer, also I am new to Oracle, and I was asked to optimize some queries.

I have a large table, like this

create table test_table(
  DUMMY VARCHAR2(50),
  COB DATE,
  DATA VARCHAR2(100)
);

Real table have many more 开发者_开发百科columns, structured arround COB date. To process this table I use a view

create or replace force view test_view("DUMMY", "COB", "DATA") 
AS
  SELECT 
      DUMMY,
      COB,
      DATA
  FROM test_table 
  WHERE 
      DUMMY IS NULL 
      OR DUMMY <> 'INTFR';

I query this view by COB date mainly.

SELECT * from TEST_VIEW where COB = '15-Dec-2010'

And I want to make a specific functional index for query optimization, which would look like this

CREATE INDEX "TEST_TABLE_DUMMY_COB_IDX" ON "TEST_TABLE" (
  case  
    when DUMMY IS NULL then COB
    when DUMMY <> 'INTFR' then COB
  end
);

As I understand, this way I'll have all recrds for the day in a B*Tree index table, with the key of COB, will this optimize my general query use, or do I need some more magic to make it work?

It will not work as you have it, since the expression in the index definition is not used in the WHERE clause of the view. This would work:

create index test_table_dummy_cob_idx on test_table
( case when dummy is null or dummy != 'INTFR' then cob end );

create or replace view test_view as
select dummy,
       cob, 
       data
from test_table
where case when dummy is null or dummy != 'INTFR' then cob end = cob;

Whether this would actually make your queries more efficient depends on the data: you will need to compare to see.