Function based index

all,
the exmaples I have seen for FBIs are mostly of type

CREATE INDEX area_index ON rivers (area(geo));

but what if I have an expression like below?
mine is failing..
SQL> create index fb_idx_test on MONAGA (SMCID(<>0));
create index fb_idx_test on MANAGA (SMCID(<>0))

ERROR at line 1:
ORA-00936: missing expression

this is for a query that ends thus
where SMCID <> 0; => need to add the FBI here.
thanks

The single most important prerequisite to create a Function Based Index is to have a function.

If you had an index on SMCID and you did where SMCID <> 0; that index would not be used. So I thought a FBI could help...

Quote:

---

Originally Posted by Tuma

I thought a FBI could help...

---

A function is a piece of code that returns a value.
"SMCID <> 0" is a comparison between a column name and a literal.
As implied in its name, a function based index requires a function to be used.