FUNCATION BASED INDEX

DOES ANYONE KNOW BENEFITS OF FUNTION BASED INDEX
CAN I USE INDEX WHERE
NVL(COLUMN name)= value

trunc(sysdate)=value

what will be the effect if i pass bind variable in value and hard core value in value.

Thanks
UKDBA

U can't use nvl, sysdate ... functions directly, because its not deterministic.

But u can create ur own deterministic function for it.
For example:

SQL> create index i_nvl_date on jobs(nvl(CLOSE_DATE, trunc(sysdate)));
create index i_nvl_date on jobs(nvl(CLOSE_DATE, trunc(sysdate)))
*
ERROR at line 1:
ORA-01743: only pure functions can be indexed
---------------------------------------------------
create or replace function idx_sysdate(p_date date,
def_date date default sysdate) return date
DETERMINISTIC
is
begin
if p_date is null then return trunc(def_date);
else return p_date;
end if;
end;
/
Function created.

SQL> create index i_nvl_date on jobs(idx_sysdate(CLOSE_DATE,'01-JAN-3001'));

Index created.

SQL> drop index i_nvl_date;

Index dropped.

SQL> create index i_nvl_date on jobs(idx_sysdate(CLOSE_DATE));

Index created.

And remember to collect histograms with FBIs in order to help the optimezer.

i mean this
Standard B-Tree index on SURNAME with cost based optimizer

create index non_fbi on sale_contacts (surname);
analyze index non_fbi compute statistics;
analyze table sale_contacts compute statistics;
SELECT count(*) FROM sale_contacts
WHERE UPPER(surname) = 'ELLISON';

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=17)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'SALES_CONTACTS' (Cost=3 Card=16 Bytes=272)

Now we use a function based index

create index fbi on sale_contacts (UPPER(surname));
analyze index fbi compute statistics;
analyze table sale_contacts compute statistics;
SELECT count(*) FROM sale_contacts WHERE UPPER(surname) = 'ELLISON';


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=17)
1 0 SORT (AGGREGATE)
2 1 INDEX (RANGE SCAN) OF 'FBI' (NON-UNIQUE) (Cost=2 Card=381 Bytes=6477)

you can use function in index creation in 8i.

I get the same error:
"ORA-01743: only pure functions can be indexed"

when I try to do this:

create index date_index
on mytable(to_date(date_column, 'MM/DD/RR'))
tablespace process_index;

See, this "date_column" is not of "date" datatype, rather of varchar, and, it's not consistent...meaning sometimes it's '8/6/02' and sometimes it's '8/06/02'...so implicit conversion doesn't work, therefore I have to use the to_date function in my queries.

I need to index this column becasue there's over 20 million rows in this table, but when I try to create a function based index with the above statement, I get the error.

Help...any sugestions?

The "RR" is non-deterministic because it's evaluation depends on sysdate.

You want fast access with a predicate that involves a function that checks for the first letter of the last name is 'A'. Here is the first query with the execution plan.

set autotrace traceonly explain;

select count(*) from emp_test where regexp_like( last_name, '^A' );



Execution Plan
----------------------------------------------------------
Plan hash value: 982230031

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 | 3040 (1)| 00:00:37 |
| 1 | SORT AGGREGATE | | 1 | 7 | | |
|* 2 | TABLE ACCESS FULL| EMP_TEST | 53500 | 365K| 3040 (1)| 00:00:37 |
-------------------------------------------------------------------------------

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

2 - filter( REGEXP_LIKE ("LAST_NAME",'^A'))



Without and index, a full table scan is performed. Here is a function and a function based index to improve the performance. I chose a bitmap index since the cardinality is 2, very low, either 0 or 1.

create or replace function check_name( name in varchar2 ) return number
DETERMINISTIC
is
begin
if regexp_like( name, '[A]' ) then
return 1;
end if;
return 0;
end;
/

set autotrace off

create bitmap index emp_test_ck_name_bmidx on emp_test( check_name( last_name ) );

set autotrace on traceonly explain;

select count(*) from emp_test where check_name( last_name ) = 1;

Execution Plan
----------------------------------------------------------
Plan hash value: 1380382320

------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 17 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
| 2 | BITMAP CONVERSION COUNT | | 10700 | 135K| 17 (0)| 00:00:01 |
|* 3 | BITMAP INDEX SINGLE VALUE| EMP_TEST_CK_NAME_BMIDX | | | | |
------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

3 - access("TEST"."CHECK_NAME"("LAST_NAME")=1)

Notice that the, very small, bitmap index is scanned and not the table.

There you have it.

David Bradford

Thank you for the contribution David, just a couple of notes.

1- Thread was going to be 10 years old on September 19th - now we will have to wait other 10 years for the big party!

2- Function Based Indexes are primarily used to avoid predicate invalidation when there is no way of avoiding the use of a built-in-function e.g. truncate(), etc.

3- Bitmap indexes are not designed to be used alone, bitmap indexes perform better when a number of them exist and are used on the predicate e.g. you want to select a RED car of the COMPACT type that has to have AUTOMATIC transmition and, has to have A/C - In this particular case four bitmap indexes would work together to return the few that comply with the search criteria.

Hope this helps.