Sorry to write this but it is nothing new and you might read exactly the same information on Oracle www.
Some hint if you want to use those items:
- If you use "=" or "IN" or "ANY" or similar operators then index might be used.
- If you use LIKE then index might be used only if the starting part is fixed (so pattern "abc%" might use index but "%abc" would not use it). Use function based indexes to reverse that (so that "abc%" might not be using index while "%abc" would use it).
- If you use regexps then index is in general not used. Avoid it if possible. Use only if there is no other option.