Query optimization tips for Oracle



When we talk about query optimization, we basically wants to reduce response time for SQL Query processing and find more efficient way to process workload. One of the major way is make our queries to use indexes so that search time can be reduced.

We have compiled following basic but helpful tips for making a query to use indexes and optimizing a query.

1. Primary Key Search
2. Index on Foreign key
3. Simplified conditions
4. Use Outer Join avoid Not In
5. OR vs UNION
6. Function Based Index
7. Handling NULL Values
8. Index Hints and LIKE

Click on the above links for examples.

Scaling Hardware may hide bad code but a bigger hardware now will be insufficient tomorrow, when people are writing not optimized code. Code Optimization is must for survival of a system but we also need to understand that "Maruti 800" cannot be tuned into a "Ferrari".


Related Posts:
- Why Primary Key Foreign Key Relationship and Join Elimination
- How to Get Execution Plan and Statistics of Query
- DBMS_PROFILER: Overview and How to Install
- Oracle : TKPROF simple steps
- Enable PLUSTRACE role to generate AUTOTRACE Report
- SQL Tuning: Partition Pruning and HASH JOIN - Real Life Example
- Why my distinct query is not using the Index?
- Avoiding unnecessary function calls to optimize SQL statements
- Why prefer COALESCE over NVL
- Append String to CLOB in Optimized way
- Doubt: Truncate Command and Table Statistics
- Tune Complete Refresh of Materialized View by atomic_refresh=>false parameter of dbms_mview.refresh

6 comments:

  1. Its really good tips, this is really helpful, thanks to sharing.

    ReplyDelete
  2. hi nimish

    can you share email id . Have some doubts related to career , mithleshdb8 at the rate google mail

    ReplyDelete
  3. how to import zip file in oracle ?

    ReplyDelete
  4. This comment has been removed by the author.

    ReplyDelete
  5. Don’t follow your role model. Be the Role model person for others. But it's so simple by getting Hadoop training in Chennai. Because it is an assurance course to bounce back from a double salary. For joining call 7502633633.

    ReplyDelete