Monday, November 27, 2006
Day 1 of our Optimizing Oracle SQL Intensive course started off with 10 bright-eyed students today in Beaverton, Oregon. This week we're fortunate to be hosted by the Oregon Graduate Institute at their wonderful facility.
Things got off to a good start and day 1 ended with one student commenting "boy...my head is full"! And it's only day 1!
Monday, November 20, 2006
Of course, they probably didn't need to upgrade their servers either....it was probably just bad SQL!
[Dilbert comic strip from Aug. 5, 2006 - copyright Scott Adams, Inc.]
Thursday, November 16, 2006
Now...if the course I'm developing ends up having lots of coffee and jazz references in the notes, you'll know where they came from!
Wednesday, November 15, 2006
Call me crazy, call me mad, but somehow I don't see the point. These queries would result in reports of thousands of screens long! I can't believe that's really what the business users want. So apart from trying to speed up the queries, we'll ask those business users what it is they really want. Chances are, we can add some default where clauses to these queries so the result sets will end up being a bit more sensible...
Jan van Mourik
Last night I was sitting in the parking lot of the hotel and pulled up Neverlost, I went to Yellow Pages, then Hotels, then Nearest, I picked my hotel. Neverlost said my hotel was 25 miles away. Gee that’s funny I can look out the window of the car and the hotel is RIGHT THERE!
Well at least FedEx knows where my hotel was. The package sent to the hotel for me was here when I arrived as expected.
I tried using Neverlost to find a Kinko’s around here, again it was miles off. I wasn’t able to find the place until I asked a local where it was.
The map view on Neverlost does seem to work. And I hope it can find its way back to the airport at the end of the week.
Tuesday, November 14, 2006
Production Environment 1
OLD -- Elapsed: 00:00:07.01
NEW -- Elapsed: 00:00:00.06
Production Environment 2
OLD -- Elapsed: 00:00:19.06
NEW -- Elapsed: 00:00:01.06
Notice in the test output below how the LIO statistics for both the original and new SQL tests are identical. The plans are very similar as well. But, the big difference appears to be the time saved by removing the extra work required by the sum(case...) constructs.
The moral of the story here is that when writing SQL, you need to consider many different things and always make sure that you attempt to write the code to do the least amount of work possible to get the result you desire.
I say this to students in class all the time - Don't do work you don't have to do!
You can find the details of the tests (conducted on a 10.1 database) here.
Monday, November 13, 2006
This blog will be mostly about life on the road, people we meet and things we learn along the way. It's pretty amazing at how much we learn as we teach and we thought it would be fun to share some of the adventures with you.
So, come along for the ride!