Re: How many is too many

What about parallel execution + index scan? If a query only needs 12 of 100 columns then it's sort of columnar in that case.


I do, of course, agree on the damage to load times but it is conceivable that drop, load, index could be possible particularly when one knows partitioning.


We shouldn't forget that Oracle had the market share of DW before Exadata. Someone, somewhere worked out how to get indexes to work for DW. I agree that most people usually get it wrong though and I agree that working out I/O is a smart thing to do.  Most of us still Party Like It's 1999 thinking that high-performance, high-bandwidth I/O is some holy grail never to be achieved. 


A single 3.5" 15K RPM SAS/FC  drive can produce 200+ MB/s streaming. Start there, add drives and scale the plumbing. It's not  rocket science. 

P.S., Are you still buying the beer next time Greg? :-)



________________________________
From: Greg Rahn <greg_at_structureddata.org>
To: oracle-l_at_freelists.org
Sent: Thursday, August 11, 2011 10:47 PM
Subject: Re: How many is too many


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.


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