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".
- SQL Tuning: Partition Pruning and HASH JOIN - Real Life Example
- Tune Complete Refresh of Materialized View by atomic_refresh=>false parameter of dbms_mview.refresh
- 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
- DBMS_PROFILER: Overview and How to Install
- Oracle : TKPROF simple steps
- SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled
- Oracle: DBMS_UTILITY.ANALYZE_SCHEMA
- DBMS_STATS Gather Statistics of Schema, Tables, Indexes
- Oracle: Analyze Table or Index
- How to Get Execution Plan and Statistics of Query
- plsql_warnings: Ensure more quality and performance in plsql code using Oracle Compiler
- PLSQL Tuning: Bind Variables and execute immediate
- PLSQL Tuning: Bulk Collect with Dynamic SQL