Category: Oracle

The use of LEFT JOIN in SQL queries is common but often misapplied. This post highlights two pitfalls: using LEFT JOIN when INNER JOIN is appropriate, and applying filter conditions incorrectly. Proper usage of joins enhances clarity and performance for optimizers.

SQL aggregation queries behave differently with and without a GROUP BY clause, particularly when no rows match. A query with GROUP BY returns no rows, while without it, a NULL value appears. To align results with expectations, using COALESCE() can return 0 instead of NULL for sums in such cases.

Many Oracle developers overlook that a PL/SQL function’s SELECT INTO statement will raise a NO_DATA_FOUND exception in PL/SQL but silently return NULL in SQL when no rows are found. This difference can lead to logic bugs. Understanding this behavior is essential for developing robust code and avoiding debugging challenges.

Scalar subquery caching is an Oracle SQL optimization technique that improves performance by storing results of scalar subqueries. This technique reduces redundant evaluations and recursive function calls. However, caution is advised when wrapping PL/SQL functions in these queries, as it may lead to unexpected results, particularly with functions needs unique values. Understanding this caching appropriately yields significant benefits.

The blog post discusses the behaviors of Oracle’s NVL and DECODE functions regarding NULL values. While NVL employs eager evaluation, executing all arguments regardless of necessity, DECODE uses lazy evaluation, only evaluating the second argument when needed. This difference can lead to performance issues or unexpected errors.

The content highlights key Oracle SQL and PL/SQL behaviors that, while seemingly simple, often result in unexpected issues in production environments. It emphasizes the importance of revisiting foundational topics to avoid bugs and performance problems. Through a series of blog posts, real-world examples are shared to assist others and enhance understanding of crucial functionalities, aiming to support the Oracle community effectively.