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@xxxxxxxxxxxxxxxxxx>
To: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxx> 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@xxxxxxxxxxxxxxxxxx> 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

Other related posts: