I have a nightly SQL Agent job that aggregates backup information from system views to power a Power BI dashboard that the DBA's review periodically for database backup failures. It is actually a PowerShell job that runs every night in parallel to collect the data from hundreds of instances (both SQL Server and Oracle). As … Continue reading SQL Server: Fix: Transaction (Process ID) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction
Oracle – Simplify Your Joins – Using “USING” In JOIN Clause?!
Same SQL written in 3 different ways “There are a thousand ways to skin a cat” or so they say. Let us look at just three ways of writing the same SQL and build the case for using the USING clause. The query below is to get the current Oracle session’s effective “parallel” related parameter … Continue reading Oracle – Simplify Your Joins – Using “USING” In JOIN Clause?!
Oracle Tip – ORDER BY with NULLS LAST or NULLS FIRST
The purpose of this tip is to introduce you to an seldom-used by very useful clause within the ORDER BY of a SQL statement – NULLS FIRST or NULLS LAST. Here is a situation that I ran into: I was looking into the Oracle 11g system view v$sql_plan_monitor to find out the top execution plan … Continue reading Oracle Tip – ORDER BY with NULLS LAST or NULLS FIRST
Oracle and SQL Server – Conditions In WHERE Clause vs. JOIN Clause – Why And When It Matters? Must Read!
This post is about the various types of JOIN's and how the conditions affect the results when placed in the JOIN clause versus the WHERE clause. OUTER JOINS Database programmers use OUTER JOINS in their job day in and day out. There are certain “gotchas” that you need to watch out for. One not only … Continue reading Oracle and SQL Server – Conditions In WHERE Clause vs. JOIN Clause – Why And When It Matters? Must Read!