Using PX with an index FFS could be a viable solution for a "skinny" table scan. The big problem that I've seen is when its not a multi-block read FFS, it's index -> table by rowid, which results in single block reads and that is very inefficient for a large amount of rows/data via direct path reads from disk. Sure - I'm still buying beers. [?][?][?][?][?][?] On Fri, Aug 12, 2011 at 9:48 AM, Kevin Closson <ora_kclosson@xxxxxxxxx>wrote: > 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<http://en.wikipedia.org/wiki/5_Whys> > . > > 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 > > > -- Regards, Greg Rahn http://structureddata.org