Oracle Sql Query Optimization for Beginners SQL Tuning Techniques

                                                                              Oracle SQL query optimization is more important while you facing a performance problems with complicated Sql Query. I faced the same problem in my project when am using oracle as backend and java as front end. So that might be reason I would to share my experience with you.
                                                                          Usually SQL optimization is performed by DBA people because they are aware of Sql query execution plan.
                                                                        They are two types of optimization is available in Oracle.
1. Rule based Optimization
2. Cost based Optimization
                                                                         If you are using oracle 10g you should use Cost based optimization because rule based Optimization is no longer available in oracle 10g.
Oracle Sql Query Optimization techniques
1.       Check the Sql query. All the keywords are used correctly like avoid the use of NOT IN or HAVING instead of that you can use NOT EXISTS sub query, table Joins handled properly …
2.       Check Indexing applied for oracle tables.
3.       Take the the execution plan for SQL Query. Understand the execution plan (usually done by DBA) change the query according to execution plan.
4.       In your Sql query add SQL €œhints € to modify the execution plan. I used hints in my Sql query and I could see good performance. Check below oracle hint list. Add the suitable hint for query.
Note: hints are not recommended for all cases.
5.       Index scan will help you for faster execution (Not Suitable for large percentage of the table rows).
About mohan

This is Mohan Jayapalan author of TechPages Blog.I am a part time blogger and Currently working for private Software Concern.

© Copyright 2010-2016 · All Rights Reserved · Powered by WordPress ·