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