In my previous article i have given the basic idea about the SQL performance tuning tips,PL SQL performance Tuning tips as well as indexing in SQL.In this article i will try to give some checkpoints for SQL query optimization.I have explained the same checkpoints in other articles but this article is for start to end SQL Query Optimization.After reading this article user will get idea about What exactly the SQL tuning is and how to do it step by step..There are lot of factors we need to consider in Performance tuning like the Server hardware,Speed of Server,Database using,Volume of data e.t.c.In this article i am giving you the basic idea of SQL Performance Tuning Techniques.The programmers always confused about the starting point of SQL Query tuning. Following are the steps which programmer needs to follow to tune the Query.
SQL Query Optimization Technique is the process of ensuring that the SQL statements will run in the fastest possible time.
How to do SQL Query Optimization :
Step 1 : Use Explain Plan Feature to check cost of query
Every programmer have question in mind that from where to start tuning? As i have explained in my previous article of Optimizer that Oracle uses cost based optimizer. The cost of query is based on the data fetched by query and data in the table. User needs to use explain plan for checking cost of query.
There are multiple ways to check explain plan of the query. Explain plan is nothing but steps of execution of that specific query.
Type 1 : With Oracle SQL Developer
User can check the explain plan of the query by using Oracle SQL developer editor.Just go to the end line of the query and then Press F10 to check the plan of the query.User needs to reduce the cost of query.
Type 2 : With Explain Statement
User can check the explain plan of the statement directly using the explain statement in oracle.Explain statement will give you plan of execution of SQL Statement.
Syntax and Example :
Explain [Query]
Explain Select * from Employee;
Step 2 : Check for Full Scan tables from Explain Plan
User always needs to analyse the tables which are scanning full. Full Table Scan (also known as Sequential Scan) is a scan made on a database where each row of the table under scan is read in a sequential (serial) order and the columns encountered are checked for the validity of a condition.User always needs to check for a full table scan and try to avoid that with using indexing techniques. There are some rules with checking which user can reduce the full tables scans of query.Before applying the indexes on the table user needs to check for some points.
Step 3 : Some Important Checkpoints before applying indexes
Before moving forward to next step of SQL tuning user needs to check some points and do the changes accordingly before applying the indexes.After all points are correct then next step is to apply the appropriate indexing for table columns so that it will reduce the Full table scan.
Checkpoint 1 : Never use *(Star) to fetch all records from table
User should avoid the (*) star to fetch all the records in the query. In spite of using star(*) user needs to use column names from the table.
Checkpoint 2 : Try to avoid distinct keyword from the query
User needs to avoid the distinct keyword from the query. The distinct keyword will sort the result of the query so that it will scan full table.
Checkpoint 3: Check all filter conditions correctly
Try to use correct operator as per requirement given.
Not Recommended:
Select * from Employee where salary != 65000;
Recommended:
Select * from Employee where salary > 65000 and salary < 65000;
Checkpoint 4: Avoid having clause/group by statements
The group by clause always scans full table. So always try to avoid the group by and having clause in SQL Statement.
Checkpoint 5 : Avoid use of Functions in Where condition.
Not Recommended:
Select * from Employee where Substr(name,1,3)=’Ami’;
Recommended:
Select * from Employee where name like ‘Ami%’;
These are some checkpoints user always needs to check before applying the indexing techniques to tune the SQL Query.
Step 4 : Apply indexes to the table columns
When user tries to tune very complex sql query which has multiple joins,user needs to analyse which tables are scanning full. I am using some basic rules of indexing to tune the SQL Query
Rule 1 : Check that Where clause columns are indexed
The first rule is all the columns used in where condition have to be indexed properly. So user should always check that all columns used in where condition are indexed.
Rule 2 : Check for type of indexes
This is very important rule for applying the indexing to the columns. User needs to use proper indexing for specified columns. Following are some important points to check before applying the index to columns
2.1. Unique values : If the data is unique in the column of the table then user needs to create unique index. It will work more efficiently than the simple index.
2.2.Distinct values: If the data is distinct in the table (Values like ‘Yes’,’No’ or ‘True’ or ‘False’) then user needs to apply the Bit-map index in that case.
2.3. Dependent columns : If two columns from same table are used in the query then user needs to apply composite index in spite of simple indexes for two different columns.
Rule 3 : Dont index every column in the query
The indexing will decrease the performance of DML statements. So try to avoid over indexing for the columns in the query.It is not recommended to index every column from the specified query.
Rule 4: Gather Stats
After applying indexing to the table always gather recent stats of that table.It will help improving query performance.
These are above some important steps of applying indexing techniques If the indexing will not improve the performance of the query then go for partitioning techniques.
Step 5 : Apply Partitioning techniques
After applying the proper indexing if the query is not performing properly,user needs to go for table partitioning techniques.There are so many aspects which are important in improving the performance of SQL. Partition allows tables,indexes and index organized tables to be subdivided into smaller pieces.Table partition is used to reduce the cost and improving performance of the application.There are some partition mechanisms using which one can divide a table into smaller pieces.Partitions can be used in so many application where we need to improve the performance.Each partition has its own name and it has own memory storage.
partition allows table,index or index organized tables to be subdivided in to smaller pieces and each piece of table,index or index organized table is called as Partition.
There are some rules for applying the partitioning to the table :
1.Table should be greater than 2 GB
2.Tables which contains historical data in which new data will be added in to newest partition. The real life example of this is historical table which contains updatable data for one year other data is read only.
3.When contents of the table needs to be distributed in different storage devices.
4.When table performance is weak and we need to improve performance of application.
According to data and requirement of customer user needs to partition the tables.There are following types of Table partition user can apply to the specific table.:
1.Range Partition
2.List Partition
3.Hash Partition
User should remember that after applying the partition to the specific table user needs to convert the global indexes to local indexes for that table.
These are above 5 steps of SQL Query Optimization. Hope this article will useful for programmers.Kindly comment on comment section if you like this article.