Re: Designing database tables for performance?

Bob Badour wrote:
> jgar the jorrible wrote:
>

>> 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.

I don't remember when I first heard or read the term "logical I/O". It might have been the in the early 1970's when IBM's VSAM access method first hit the streets. I'm pretty sure it was current in some circles then. Codd had written his first papers then but practically nobody in industry was even aware of them as IBM was pushing IMS and Vandl hard and people who knew him then told me later that there were big marketing forces at IBM that made working at the same company very difficult for him both personally and professionally. The term is a very unfortunate one since I'm sure misleads many newcomers to IT. As we can see here, it misleads many others.

(Anybody who was programming then has an historical advantage over younger people because it is so much easier to see what a revolution Codd started.)

On other themes in those days I'd say most people in industry didn't really recognize what was happening in an exploding field, took their guidance from rags like Datamation while the first big DP consultancies and syndicated advice newsletters were taking shape. They were also technically ill-prepared. The average DP/IT manager or VP didn't know his technical ass from a hole-in-the-ground when maybe a majority of them had cut their teeth in unit-record/tabulating operations. When IBM brought out demand memory paging (about ten years after one of the small Brit' companies) they called it "virtual" to distinguish it from physical, in a very lazy way. Of course, a memory page is no more virtual than an Oracle buffer fill is logical. But the "logical" nuance has done a lot more damage, as you suggest.

For that matter, another term that bugs me is "key". This is a definite hang-over from the first random access devices of the early 1960's, when a key was very much a physical artifact, in fact the hardward of those days was aware of physical keys, being wired to recognize certain disk or even tape blocks as of a completely different kind of thing than data. I remember somebody, perhaps on this group, called them antecedents. Not suggesting that should be the operative term in the RM, but it seems less misleading than "key".

On the other hand, it if weren't for multiple meanings, English poetry might not be as inspiring. Still, I think very precise, narrow human endeavours such as db should be talked about without these vague terms.   I think it would be good if the regulars in this group would agree to do that.

>
> 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.

Maybe somebody can come up with better terms, but since I can't at the moment, I think those are good suggestions.

p Received on Sat Feb 24 2007 - 00:10:09 CET