So according to The oracle documentation on catsearch it's not possible to use left-truncated searches with a catsearch text-query. However, I've found that using double wildcards does have the desired effect of left-truncated queries.
E.g. select * from foo where CATSEARCH(bar, '**zing', '')>0; would find records with bar having the value of "this is amazing" while
select * from foo where CATSEARCH(bar, '*zing', '')>0; wouldn't
For the configuration of my word list, I have enabled substring_index and prefix_index. In the substring_index description, it does mention left-truncated (and double-truncated) wildcard usage.
I cannot find any reason as to why/how the double wildcard usage works, how optimized it is and whether or not it has any other side-effects.
So the ultimate questions:
- Why does this syntax work, is there anywhere that this is described?
- Is it equal, performance-wise?
- Are there any other side-effects people should be aware of when using this syntax?
