Re: full-scan vs index for "small" tables

On 06/27/2006 10:52:50 AM, Cary Millsap wrote:
> Mladen,
>
>
>
> I'm going to stand firm on the following statement:
>
>
>
> Work first to reduce the biggest response time component of the
> business's most important task.

We agree here, completely. You've just expressed the basic premise of your book. If I had to aggregate your book into a single sentence, that would be the one.

>
>
>
> Everything else follows from that. But in order to know what your most
> important task is, you have to communicate with your business. And in
> order to know what that task's biggest response time component is, you
> have to measure your specific circumstance.

Yes, no doubt about that. Business determines priorities.

>
>
>
> The objection I have with the world at large is when people guess
> instead of measure.

It's much easier to guess. It's the same mentality that sells lottery tickets: luck definitely beats spending countless hours toiling laboriously. If lady luck is on your side, you don't have to work ever again. Unfortunately, it usually ain't. The same thing applies to guessing instead of diagnosing.

> Most people guess "It's got to be I/O." But "it" is
> I/O in fewer than 5% of cases I've witnessed since about 1995.

I haven't recorded percentages, but the problems that I encountered were usually centered around bad SQL. If SQL inadvertently causes full scan of a multi-million row table or generates Cartesian product, I consider that to be I/O problem. That is what I had in mind when objecting to underestimating the role of physical I/O

>
>
> My point ends with, "Why guess... When you can know."

But guessing is much easier and so much fun. Las Vegas lives of people who guess. Guessing is the world's oldest pastime. Sometimes, I feel that being a DBA is akin to the world's oldest profession. Hmmmm, I'm not going to explain that any further.

-- 
Mladen Gogala
http://www.mgogala.com

--
http://www.freelists.org/webpage/oracle-l