Re: Designing database tables for performance?

On Feb 23, 5:24 am, "Cimode" <cim..._at_hotmail.com> wrote:
> On Feb 23, 2:06 pm, Frank Hamersley <terabitemigh..._at_bigpond.com>
> wrote:
>
> > Cimode wrote:
>
> > [..]
>
> > > Yep. Last time I discussed database issues with an ORACLE guru, he
> > > was trying to convince me that RAM was logical as opposed to Hard
> > > drive which was physical. To the ORACLE gurus, as soon as it is in
> > > memory, it becomes totally logical. A total absurdity of course...
>
> > He was prolly talking about the types of IO's for a query that the
> > optimiser predicts and execution engine encounters. Sybase uses the
> > same terminology and weights them differently when costing out
> > (possible) plans.
>
> > Cheers Frank.
>
> In what RAM would be less physical than HD ? For any reason, an
> absurdity is an absurdity.

Not an absurdity, you just aren't paying attention to how the I/O is counted. From Oracle's point of view, if the desired data exists in Oracle's buffers, that is a logical I/O. If Oracle has to ask the OS to give it stuff to put in the buffers (or Oracle knows that it has to get it off a disk using its own raw I/O), that is counted as a physical I/O. In actual fact, data outside of Oracle may be in an OS buffer, a disk or SAN buffer, or actually have to wait for the rust. Beyond that, the counting may be modified by the fact that the disk may predictively grab more into its buffers and not have to actually make more reads (and vice versa on some platforms that transform, say, an 8K request into multiple .5K requests), and Oracle is often configured to ask for multiple blocks, effectively doing the same kind of preloading into its own buffers for certain types of data access. Also, Oracle buffers may have several versions of the same data that may or may not been written out, and has to be able to figure out which version a session needs. It turns out that more complicated ways of tracking the data allow greater scaling and performance than simply getting the data off disk each time, assuming you have multiple users and lots of data. In more recent Oracle versions (and maybe other db engines, I wouldn't know), it can even account for data and cpu usage patterns.

Now, you are welcome to think it is better to be more simple than that and just say RAM I/O is the same as HD, but that sounds patently absurd to me. Perhaps you have a better way of distinguishing the semantics?

jg

--
_at_home.com is bogus.
"There was a lot of data, whoa." - http://www.signonsandiego.com/uniontrib/20070223/news_1b23verdict.html