Oracle Management Cloud

Introduction to Oracle Management Cloud We have been seeing so many products of Oracle off late. A new suite from Oracle named Oracle Manage...

Tuesday, February 27, 2024

Does performance of a SQL change when a new column is added to the existing script ?


 

 

Recently, I have noticed that there are numerous developers who struggle to comprehend how SQL functions when even the slightest modification is made to it. 

I collaborated with a Developer who provided me with a SQL script consisting of over 1800 lines and multiple joins. My team of DBAs and I dedicated nearly 3 days to fine-tune the query, resulting in a significant improvement from 2 minutes to just 20 milliseconds per execution. 

However, a week later, the same developer returned claiming that the SQL performance had deteriorated once again. Upon testing it with the optimized code I had, the results remained consistent. Nevertheless, the developer insisted that it was not functioning as expected. 

Upon further investigation during a call, we discovered that they were using a slightly altered query, although it appeared nearly identical. 

When questioned, the Developer mentioned, "We simply added 3 columns to the script after it was optimized. Why would that make a difference? They are additional columns from the same table," expressing confusion. 

 I had to clarify that even the smallest alteration, whether it involves adding a new column from existing tables or modifying a condition, will impact the entire SQL plan. 

 Although it was challenging for the developer to accept, it is crucial for them to understand this fundamental concept.

No comments:

Post a Comment

Most Viewed Posts