How to optimize sql query
1. Identify tables on which max no of columns in select statement, this table should be in main query where clause.
2. Identify tables which do have fewer columns ie..1 or 2 columns in select statement, move this in nested inline subquery in select statement as coumns rather than put in main where clause, this will reduce joins in main where clause & significantly improve query performance because of subquery caching of results by database.
3. Try to reduce your dataset ie..eliminate most of the rows in early steps of query execution. Identify such tables which have least cardinality or rows and put them as last table in FROM clause. In RBO driving table is last table in from clause, in CBO it is derived by stats and data dictionary but it can be overridden by index hints.
4. Again try best to eliminate max rows in initial steps of execution, provide best filters as predicates in ordered sequence in WHERE clause, again you can use hints to force which predicate is applied first.
5. Always try to use indexed column in joins, if column has very fewer type of data which is repeating many times like gender-M/F , use bitmap index, if from same table multiple columns are used in join , create composite index rather than individual index.
6. Always ensure stats are updated after heavy DML operations, this will significantly improve your query performance, old stats makes index unusable and causes full table scan even if index are present.
7. Always try to use EXIST clause rather than IN clause, EXIST will only check for indexes while IN will cause inline query to fetch real column data to compare.
8. Avoid writing NVL, UPPER or any SQL function on indexed column, in such cases indexes are ignored and FTS occurs, rewrite in such way that columns are able to use its indexes or if there is no way to avoid then use function based indexes.
9. Avoid DISTINCT to group results, rather first try other means in where clause to construct unique rows, distinct causes aggegration which can hit query performance.
These are some points i am able to recall although apart from this, there are many things to be taken care while designing table for reports or transactional system which significantly effects query performance, hope it helps somehow.