SQL Tuning Tips and Tricks Tutorial in Oracle -3

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

Oracle SQL Tuning Tips and Tricks

Read second article before this.

SQL Tuning Tips and Tricks Tutorial in Oracle -2

SQL Tuning Tips and Tricks

21- Use Partitioning for Large Tables

If Slow query contains large tables, then you can use table partitioning to make faster query.

You can read the following article to learn Partitioning detailed

Partitioning Types ( Range , List, Hash, Interval .. ) in Oracle Database

22- Use CASE statements instead of DECODE

You can use CASE statements instead of DECODE for the readability and performance of the query

23– Use IN instead of OR

You should use IN instead of OR in the where clause. IN is used for selective predicate is in the sub query,and EXISTS is used for selective predicate is in the parent query.

24– Avoid Complex expressions in SQL

You should avoid using complex expression in SQL to prevent the optimizer from assigning valid cardinality or selectivity estimates.

--Do not use:
WHERE SUBSTR(a.id, INSTR(b.id, ',') - 1) = SUBSTR(b.id, INSTR(b.id, ',') - 1)

--Use this instead of above:
WHERE a.id = b.id



--Avoid:
WHERE id = NVL(:a1, id)

WHERE NVL(id,-1) = ( 1, etc...)

NVL (col1,-999) = ….


TO_DATE(), TO_NUMBER(), and so on

25– Prefer INNER JOIN instead of OUTER JOIN

Inner join is recommended instead of Outer Join if possible. Because Outer join limits the database optimization options

26– Avoid ORDER BY and GROUP BY If possible

it is recommended to avoid ORDER BY, GROUP BY clause , if using ORDER BY, GROUP BY clause is not mandatory for you. Because ORDER BY, GROUP BY causes sorting and result in one of the most expensive operations in SQL execution.

27– Avoid Hint usage

Hint usage is not recommended instead optimizing your code.

Hint usage is the last thing you should consider for Performance tuning or it is a temporary solution.

28– Drop Unused Index

You should drop unused index on tables, monitor all indexes and check them if they are unnecessary and unused, you should drop them.

Most common mistake is using Index, Index is not good everytime. You should monitor it, If it is good for performance, you can keep it, if not you should drop it.

Read the following article to learn Index detailed.

What is Index and Why Should We Use Index?

29– Avoid using wildcard (%)

Using wildcard (%) at the beginning of a predicate is a known performance problem in all databases.

Wildcard (%) usage LIKE ‘%abc’ causes full table scan, so avoid Using wildcard (%).

30–  Use Hash Join for Large tables Join

You should examine execution plan carefully, If 2 large tables are joined, If they don’t use Hash Join, use Hash Join hint. If a large table is joined with a small table,  If they don’t use Nested loop Join, use Nested loop Join hint.

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