CODEWITHSUNDEEP
oracle-databaseindexing
user2197657
user2197657

Reputation: 3

How do I create Oracle Function Index?

Just a simple question, How do I create a function based index on this query.

select * from mytable A where :var1 like A.fld1 || '%';

mytable has a normal, non-unique index on fld1.

Upvotes: 0

Views: 385

Answers (2)

David Aldridge
David Aldridge

Reputation: 52356

You don't create a function-based index on a query, you create it on a function. What you have there is an operator, not a function.

What you want to do is find all rows in the table where fld1 matches the characters with which var1 begins, and the index that would helpyou with that is just one on fld1. It could be that the best plan you can hope of is a fast full index scan because this isn't really amenable to a range scan.

Upvotes: 1

Jeffrey Kemp
Jeffrey Kemp

Reputation: 60262

Well, you can create a function-based index on the expression you've used in your query:

create index myindex on mytable (fld1 || '%');

However, it's unlikely to be of any benefit to this query. I'd say your normal non-unique index on fld1 will work just as well as the function-based index.

Upvotes: 0

Related Questions