Database Query Optimization Techniques

1. Use count(Column_name) instead of count(*).

2. Use UNION ALL statement instead of UNION, whenver possible
        The UNION ALL  statement is much faster than UNION, because UNION ALL statement does not

         look for duplicate rows, and UNION statement does look for duplicate rows.

3. Avoid using the DISTINCT clause, whenever possible.

         Using DISTINCT  clause wll result in some performance degrading, use this clause only when it is

         necessary.

4. Avoid the HAVING clause, whenever possible

          Having clause is used to restrict the results set returned by the GROUP BY clause. When you use

          GROUP BY  with the HAVING clause divides the rows into sets of grouped rows and aggregates

          their value, and then HAVING clause eliminates undesired aggregated groups.

         Also do not place any ( where) conditions after the HAVING clause (first_name like '%thomos%') -

          this would reduce the performance of the query.

5. Choose order of the  where clause  carefully. 

        Write the where condition such that the first column is of indexed and fetches less number of rows for the

        given query condition .

6. Use subselects in the place of sub-queries

7. Use Prepared statements

8. Avoid using OR condition 

         AND condition - restricts the data set

          OR condition - grows the stat set

        when the different OR conditions might intersect thus forcing the optimizer to effectively to a DISTINCT

        operation on the result.

         Use IN condition instead of OR.

            

9. Avoid using function  in the where clause  onleft hand side of condition which would evaluate to some value instead of specifying column names.

      BAD : WHERE function(param1,param2,param3) between to_date(fromDate,'mm/dd/yyyy') and

                 to_date(toDate,'mm/dd/yyyy')

     GOOD: where join_date between to_date(fromDate,'mm/dd/yyyy') and to_date(toDate,'mm/dd/yyyy')

10.  Use of  certain functions like upper(), trim() on the right side of the where condition would 

      retard the  performance of the query

         BAD: WHERE city_name =upper(value)

                   WHERE city_name =trim(value)

          GOOD: WHERE city_name=value.toUpperCase()

                          WHERE city_name =value.trim()

         the uppercase and trim can be done in application level coding like in java and not at query level.

11. Use function based Indexing

            select first_name,last_name,join_date from emp where upper(first_name)='MICHEAL'

            Though indexing exist on first_name column, the index wouldn't get applied as UPPER() function

            is applied on the column.


Best Practices
Queries
- Use table and column aliases to make the query easier to read.
- Use column names in group by and order by clause (instead of numbers)
- Limit the number of tables in a query
-Use bind variables- if bind variables are not used then Oracle has to rephrase the query every time it is run unless the variables in the where clause match exactly.
 syntax changes can avoid full table scan
    example:
            use inclusive range condition or in condition rather than  "!=" and "<>"
             avoid: where status !='I'
             preferred: where status in('A','V','N')
    
             use in condition rather than exists

              Use a default value check  instead of null check
                        avoid:where status is null
                        preferred: where status='Z'

            use functions on the left hand side to use indexes
                    avoid: upper(name)='HELLO'
                    preferred: name=upper(variable)
Complex Queries
Index only Tables
functions
materialized view
stored procedures
global temporary tables
inline views


Normalization
 To Maintain data in one place - reduce space and remove data inconsistency.
Flexibility

 FirstNormal Form
     No Repeating groups or eliminate duplicate columns from same table
                  Example: Bid(bidItem, minAmount, bid1,bid2,bid3,amount1,amount2,amount3)
                  change to: Bid(bidItem,minAmount)
                                    Bid_Details(bidItem,bid,amount)


Second Normal Form
     Remove Subsets of data that apply to multiple rows of a table and place them in separate tables.

 Third Normal Form
     No column is dependent on another column which is not a primary key.

       Example:  Customer (CustomerName, customer id, creditLimt, creditBalance)

–       Change to:  Customer (CustomerName, customer id, creditLimt,)

      here creditBalance is calculated field based on creditLimt., hence does not qualify to be a column

    No of Visits