Oracle Sql Best Practices

1. Never Mix Data Types
2. Avoid Like Predicate
3. Avoid use of functions in column which appears in JOIN or WHERE clause
4. Use appropriate data type and size
5. Avoid UPDATE as far as possible.
6. Avoid sub-queries whenever it is possible
• EXISTS and IN sub-queries can be converted to INNER JOINs.
• NOT EXISTS and NOT IN sub-queries can be converted to OUTER JOINs
7. Take advantage of PL/SQL-specific enhancements for SQL:
• BULK COLLECT and FORALL, cursor variables, table functions
8. Proper Indexing
9. Always use table aliases when referencing columns.
10. Use ANSI JOINS in contrast to ‘Traditional’ JOINS
11. Take advantage of MERGE
12. Take advantage of INSERT ALL/FIRST
13. Gather statistics
14. Usage of hints
15. Pin table to memory
16. Global Temporary Table
17. Materialized View
18. Reduce volume of data
• Creating small intermediate tables with or without applying some business logic.
• Implementing Partitioning feature to achieve partition pruning and preventing Full table scan.
19. Hit aggregated data
20. Re-use of intermediate table