Revisiting old code

One of our customers has an internal IT department with a section that deals with their data warehouse and BI tool. The spend most of their day writing the reports that are beyond the casual user or creating data extracts to move information to other systems. These guys have been doing this for years, since last century in fact!

So, they know what they are doing then? Well, in the main, yes; the occasional bit of poor sql, but they know that if they need to they can ask us to help optimise it. But today they submitted a piece of SQL to generate a flat file of all the sales of a group of products for all stores for three months - that's about 50 million transactions that need to queried. Instead of sending just the query we got the whole sql script and there we saw the three things that held them back for better performance.

As I said they have been doing this work for years. Their first data warehouse was on the first release of Oracle 8i, we used composite range/hash partitioning of weekly organised data. Next time around we rewrote the DW to use range partitions only on daily data and upgraded to 9.2.

In early releases of Oracle 8i there was a bug with sub-partitioned data returning incorrect results - it was fixed by 8.1.4! But when it first hit us Oracle gave us a couple of undocumented ALTER SESSION commands to work around the problem. This inhibited partition-wise joins and guess what? They still religiously copied these alter session commands into each and every script they wrote, not knowing that it was no longer necessary to use it to get correct results.

The moral of the story is to make sure people know that they can stop using a work around; probably was our fault for not telling them.