Oracle query optimization tips

Query optimization tips :-

1.Rewrite complex subqueries with temporary tables.

2.Use minus instead of EXISTS subqueries

3.Use SQL analytic functions

4.Re-write NOT EXISTS and NOT EXISTS subqueries as outer joins

5. Index all the comparing columns in query

6.Avoid the use of NOT IN or HAVING

7.Avoid the LIKE predicate

8.Use decode and case

9.Always use table aliases when referencing columns

10.Never mix data types

comparision between regexp_like and like statements

Search using like is always faster than the regexp_like.
REGEXP_LIKE is similar to the LIKE condition, except REGEXP_LIKE performs regular expression matching instead of the simple pattern matching performed by LIKE.
This condition evaluates strings using characters as defined by the input character set.

LIKE syntax for pattern is simple and supports a small set of wildcards,
but does not support the full regular expression syntax.

Whereas the equality operator (=) exactly matches one character value to another, the LIKE conditions match a portion of one character value to another by searching the first value for the pattern specified by the second.
LIKE calculates strings using characters as defined by the input character set