Query Optimization in Oracle SQL – Tips & Tricks
TIP#1 – Use only what you need It sounds very logical when you read it, but I’m sure we all had times when it was just easier to write SELECT s.* FROM sales s; then to list all fields we need from some Sales table. Don’t be like that. Unused columns that are needlessly fetched increase I/O on your database and add to network traffic. TIP#2 – Avoid the Cartesian product It’s obvious we’re still warming up, because this one is self-evident, but throughout many years in development, I the had urge to stress this one just on the safe side – we all get this. So here it comes one more time, avoid using the Cartesian product. Except when one of your two tables that you join consist of one row 😊. I know it sounds silly, but in everyday development you see everything. TIP#3 – Avoid modifying indexed columns with functions Do not modify indexed columns using Oracle built-in functions like TO_DATE, TRIM, LOWER etc. Using those functions on an indexed column will disable the optimizer from i...