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.
Add a comment
|
2 Answers
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.
1 Comment
user2197657
Thanks for the reply David. I see.. my problem is the optimizer doesn't seem to see the index as a good execution path. CBO doesn't use the index.
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.
12 Comments
knagaev
It's quite strange but not, normal non-unique index will NOT work as well as the FBI in this case. The author wants to find smaller strings in fields for a bigger value in var1. For example, if var1 is 'JohhDoe', one of right records will have 'John' or 'Jo' and so on.
Jeffrey Kemp
Think about it, @knagaev. What if a row has fld1=
'%Johh'... I'd be surprised if you could demonstrate that the FBI will make a repeatable, measurable difference to the query vs. the normal index.knagaev
Yes, @Jeffrey Kemp, you're right. I was confused by different behavior of '%' in operator || and operator LIKE. FBI is created as value of fld1 with hooked symbol '%' but LIKE interprets it as a wildcard. By the way, I doubt the normal index will be helpful anyway - only full scan is possible, huh?
user2197657
Thanks for the replies :).. @knagaev Yes, you are right, var1 has bigger value than on fld1. I tried to create an index using the example given by Jeff, but still the optimizer didn't use the index. So I tried to force the cbo to use it using index hint. It worked, however the index wasn't helpful, FTS is still much faster. Any Ideas?
Jeffrey Kemp
Ok, next step is to work out why it's slower than you expect it to be. How many records in
mytable? How many records have a non-null fld1? How many records are returned by your test case? Also, do you really need to return all the columns (*)? Note: If your query is returning more than a small percentage of all the rows in the table, then a FTS is probably the best you can do anyway. |