Re: Separating data, index objects

"IANAL_VISTA" <IANAL_Vista_at_hotmail.com> wrote in message news:Xns9689B683499B6SunnySD_at_68.6.19.6...
>
> HUH?
> "separating the two probably applies more to Decision Support than OLTP."
> Based upon which metrics?
>
> How in the world did you conclude what I described was OLTP & not DSS?
>
> Assuming high transaction rate against an index, please explain how having
> the index in its own tablespace "it could make a difference.". After the
> block has been read from disk, the data will be in the SGA if it needed by
> another session (assuming it has not been aged out). If a different block
> is requested from the same table or index, what difference does it make in
> which tablespace it resides?
>
> Yes, more spindles can sustain more I/O requests per second, but on a per
> spindle basis when the maximum rate is hit, the I/O requests start to
> queue.
>
> It would be nice to see actual reproducable metrics, rather than
> unsubstantiated generalities.
>

You are completely ignoring the fact that I said the differences were only theoretical and not necessarily noticeable or even measurable. Even the theoretical advantage is non-existent in a small to medium sized database with well configured bufferpools.

You are basically repeating what I said about bufferpools and that if the data was already in SGA, there is no physical I/O.

Regarding Decision Support, the idea is that the data could be so large that the bufferpool hit ratios are lower, meaning there is more simultaneous (or at least near-simultaneous) disk I/O against the table and indexes. Notice I said "more" and do not mean to imply that all disk access in decision support is simultaneous against the table and indexes.

With certain kinds of queries (a large nested loop join for one) there is a lot of access back and forth between the table and index in near simultaneous time slices, and if the bufferpool is not large enough for at least the index to reside in memory, then there could be more disk contention than on a OLTP system with shorter transactions. The same could also be true with loads or massive inserts of large amounts of data, where there can be near simultaneous I/O on the table and index.

When you start dealing with databases that are multiple terabytes and larger, then you will see more of a benefit of optimizing disk (and using faster disk) than if a much smaller OLTP system is used with intelligently configured bufferpools. Received on Mon Jul 04 2005 - 20:20:33 CDT