SQL Tuning Tips and Tricks Tutorial in Oracle -2

I will continue to explain Oracle SQL Tuning Tips and Tricks in this article.

Oracle SQL Tuning Tips and Tricks

Read first article before this.

SQL Tuning Tips and Tricks Tutorial in Oracle -1

SQL Tuning Tips and Tricks

11- Avoid Function usage like Substr

Try to avoid using too many functions, especially don’t  use SUBSTRING, instead use LIKE clause.

Difference between substr and like is Such as “where name like ‘DEV%”;” will use an index whereas a substr such as “where substr(name,1,2) = ‘Dev’; will require a function-based index (and extended optimizer statistics) for optimal performance.

Using the substr clause is best for large production queries, when you provide a function-based index and deploy extended optimizer statistics on the target column.

12- Use Truncate instead of Delete

You should prefer truncate table instead of delete table if you delete too many rows on a table. Too many delete operations cause fragmentation and performance problems.

13- Avoid Using Triggers

You should avoid using of TRIGGERS. Just use it as a last resort. It is better to use CONSTRAINT and STORED PROCEDURE to maintain the integrity of your databases.

14- Use Bulk DML instead of Single DML

You should use Bulk Insert,Delete and Update instead of Single Insert,Delete and Update if you are executing Bulk operation.

Bulk insert,Delete and Update will give your system a huge performance boost.

15- Avoid unnecessary columns

Unnecessary columns in Select clause incur more I/O on the database and increase network traffic.

You should analyze all columns in select or DML clause and if it is unnecessary, you should remove that columns in queries. Mostly Using SELECT * is not true for database and related query performance.

16- Avoid Distinct clause

DISTINCT operator causes sorting which slows down the SQL execution time.

If it is possible, it is better that remove Distinct clause in Select.

17- Use MINUS instead of EXISTS

Minus operator is mostly running in a faster execution plan than NOT IN and NOT Exists operator.

18- Use Alias for table column

If you use more than one table in a query, you should use table aliases for column to avoid confusion.

19- Use Parallel hint for large data access

If you execute any SQL which is accessing large data sets, then you may use Parallel hint to perform it in parallel.

When you use parallel hint, you should check operating system CPU counts and cores to specify how many parallel will you use it.

If lots of people use parallel hint everytime, operating system and database can  slow down or power off because of CPU bootleneck.

20- Use COUNT(1) instead of COUNT(*)

Using COUNT(1) instead of COUNT(*) is recommended  for SQL query performance optimization

I will continue to explain tips and tricks of SQL Tuning in the next article.

Do you want to learn Oracle Database Performance Tuning detailed, then Click this link.