Re: index with very high LIO
- From: Greg Rahn <greg@xxxxxxxxxxxxxxxxxx>
- To: "Mark W. Farnham" <mwf@xxxxxxxx>
- Date: Tue, 22 Nov 2011 13:33:14 -0800
On Tue, Nov 22, 2011 at 7:15 AM, Mark W. Farnham <mwf@xxxxxxxx> wrote:
> It is a pity that there seems to be no way to use an index to grab block
> segments of a table to fts (and yes, then refilter the rows).
>
How does that help here? The system is limited by data movement between
strorage and host so retrieving more data would not seem to add
any benefit to me. Having just 35MB/s of bandwidth, sending any extra data
seems counter productive -- even it it means getting the filter IOPS bound
(which I doubt it is).
Just a thought. Might be more effort than value, but I think in the long
> haul everything just gets too big for a full FTS, even in parallel. Heh,
> the
> software effort would have been more defensible 20 years ago.
>
I'd say it's quite the opposite -- it is faster and more efficient to
leverage a parallel sequential scan and filter out rows when it's a "many
rows" problem than to get 1 block at a time for a "many rows" problem
-- especially if your storage is rotating rust. Access via rowid is great
for a "few rows" problem and "few" has an absolute ceiling IMO. This is
the same reason that star transformation using bitmap indexes works great
if the number of rowid gets is few, but once you cross into the 1 to 5
million range it degrades and a FTS with the appropriate hardware will beat
it out. The caveat here is that FTS does not mean read ever single row
blindly -- it means encompassing features like partitioning or storage
index type functionality that leverages ordering/clustering.
The high level challenge here that I see is the client trying to fit the
foot to the shoe, not the shoe to the foot which is resulting in some edge
case engineering. As one of my university CS professors often said, "just
because it works, doesn't mean it's right."
--
Regards,
Greg Rahn | blog <http://structureddata.org> |
twitter<http://twitter.com/GregRahn> |
linkedin <http://www.linkedin.com/in/gregrahn>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
