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
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.
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.
