Tuesday, November 21, 2006


I've bugged Jonathan Lewis for a long time to do a blog.  Even if it was just a blog to point to new articles he wrote on his site so we could easily subscribe to them.

He finally took it from me and others to heart and well - the result is really good.  I suggest you subscribe.  I have not been disappointed yet, take this recent example - something you don't see every day, but it'll be filed in the back of my head.  If it comes up again with someone I'm working with - I'll appear to be "magic".

But it is just something I learned from the forums...

Something I talk about in my seminars - I spend a great amount of time in my "SQL Techniques" talk about how participating in the community at large is what really made me have the amount of Oracle knowledge I have today.  Examples of people "one-up-ing me" on  asktom (I really enjoy that), examples of what I've learned recently about Oracle by reading others works.

Really nice blog he's got going there.  Check it out.

I liked this entry "How Long...".  But only because I've had so many people give me queries and say "this takes too long".  When I look at the

a) query

b) underlying data

c) the query again

I can only answer - "what are your expectations, are they even reasonable - this is a big question against a ton of data" sometimes.

Many times people don't think about "how long" should it really take.  And I like the way he just threw it out there as a question - sans answer.



Anonymous Anonymous said....

Well... when the end users compare it with an existing legacy system, thats when it gets difficult to answer.

Awhile ago, I was involved in a data migration project. There were couple of tables with 10 to 15 million rows. When the user runs a query to retrieve certain record from mainframe DB2 database system it returns in 12 seconds whereas new oracle system takes minutes. Thats all they see. They dont look at the fact that the db2 tables are flat and hence the lookup codes are stored there and no need to join many tables to get the results compared to the normalized oracle tables and several other factors.

Tue Nov 21, 01:34:00 PM EST  

Blogger Noons said....

a few years ago I worked at a site where we had a contest on how long a query we could find.
the winner was something that looked innocent enough but caused, on estimates, something like 100 trillion trillion logical reads.
we called THE "legacy query", because the results would only ever be seen by our grandchildren...

Tue Nov 21, 05:08:00 PM EST  

Anonymous Anonymous said....

man Jonathan's blog content is deeeeeep, at least to me.
So deep it's almost scary

Tue Nov 21, 06:08:00 PM EST  

Blogger Mathew Butler said....

Regarding how long...

I just wondered whether you had read SQL Tuning by Dan Tow.

It it supposed to help answer the question "How do you decide which execution plan a query should use".

Jonathan makes a mention to it in one of his blog comments ( I searched for the comment but couldn't find it ). I ordered it off the back of this mention and am currently giving it a read.

So far it is good. Although I haven't yet got into the real meat of it. I'm hoping it will give me a different way of thinking about what happens at SQL execution time. It looks promising.

Best Regards,

Wed Nov 22, 10:23:00 AM EST  

Anonymous RobH said....

"Our query is 5 seconds longer than it was!"

Data rows before 200,000
Data rows now 40,500,000

Um, you're kidding me right? I don't know how to even respond

Wed Nov 22, 02:52:00 PM EST  


<< Home