Re: How many is too many

From: Greg Rahn <greg_at_structureddata.org>
Date: Thu, 11 Aug 2011 22:47:16 -0700
Message-ID: <CAGXkmit+SuEZ8qqNr086PGiHpip6i+8kLuOOZ=YM8RmxON+XLw_at_mail.gmail.com>

For OLTP, adding indexes is a known quantity (in general). There is a known and finite number of queries so adding indexes is about limiting the number of blocks visited.

For DW, adding indexes in this manner (12 indexes on a table, etc.), is the beginning of the end. Data loads suffer and since query workloads are frequently unknown (ad-hoc), DBAs often misapply OLTP techniques - adding indexes for each problem query they see. Seems people forget why databases have indexes -- as a manner to efficiently access *a small number of rows.* When indexes are used as a means to reduce IO, it is then being mis-applied because the platform is not appropriately sized and/or correct DW technologies are not being applied (e.g. parallel execution + table scans) for *a large number of rows* problem.

In any case, one has to start asking what good reasoning there is for 12 indexes. That just shouts bad design and/or inappropriate tuning methodologies. Start asking why 5 times<http://en.wikipedia.org/wiki/5_Whys> .

On Thu, Aug 11, 2011 at 8:14 PM, Ram Raman <veeeraman_at_gmail.com> wrote:

>
> Rule of thumb! Thanks for the response Greg. Does this apply to Data
> warehouse or OLTP applications or both?
>
> On Thu, Aug 11, 2011 at 7:03 PM, Greg Rahn <greg_at_structureddata.org>wrote:
>
>> Sounds like the "index death spiral" to me.
>> My simple sanity check: when the total number of columns indexed is
>> greater than the number of columns in the table, there is likely a design or
>> tuning problem. Often times, even before then...
>>
>

-- 
Regards,
Greg Rahn
http://structureddata.org

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

Received on Fri Aug 12 2011 - 00:47:16 CDT