Re: Designing database tables for performance?

On Mar 7, 4:04 pm, "Walt" <wami..._at_verizon.net> wrote:
> "Cimode" <cim..._at_hotmail.com> wrote in message
>
> news:1173214333.040736.29540_at_q40g2000cwq.googlegroups.com...
> On 6 mar, 05:59, "d..._at_smooth1.co.uk" <d..._at_smooth1.co.uk> wrote:
>
> > > The time to complete is therefore far less and the "cost" in terms of
> >> time is much less.
> > So because time is the difference that makes less physical. Don't you
> > see anything wrong in that?
>
> Messages in this discussion have expressed it wrong, but there is a
> sensible way to describe this situation.
>
> When reference is made to "database data" within a client process, a
> "logical I/O" takes place regardless of whether a "Physical I/O" takes place
> or not. The "logical I/O" is the transfer of data between space managed by
> the agent of the DBMS within the client and space managed by the client
> process runtime environment. Its convenient to call the former "buffer
> space" and the latter "working storage".
>
> The Physical I/O is the transfer between persistent secondary storage (e.g.
> "disk") and "buffer space".
>
> In the case of a Physical I/O immediately followed by a logical I/O, the
> delay time due to the Physical I/O eclipses the delay due to the logical
> I/O.
>
> In the case of a buffer already containing the needed data, the logical I/O
> is the only delay. Something physical *is* happening in the case of logical
> I/O, BTW. It just isn't I/O.
I do not agree.

Transactional issues are totally independent from the subject of separation between the physical and logical layers. The first reason why such confusion arises is because the SQL DBMS are direct image systems that break the rule of independence between the two layers. The second reason is that proprietary and subjective definitions are continuously poured onto the ground ignoring all work that has been done before on the subject.

For clarity's sakes, one could consider that any *Requirement of direct representation of tuples* *physically* on any physical media (RAM, hd) as it could be presented to the user for interpretation, automatically establishes it as part of the physical layer. Such representation is systematic on direct image systems and therefore immediately qualifies the operation of acquisition of the user view as a physical operation and nothing else.

OTOH, one could reasonnably state that a system with true independence between the two layers does two kind of operations:

> a physical operation to establish an internal representation of data at compile or linkage edit time.
> a logical operation to establish a user view of the data at run time only

For clarity's sake and at the risk of sounding oversymplyistic (and maybe be plain wrong), the chances for any system to perform a logical operation in RM perspective, if such system represents data in memory as a user view, are quasi null. Received on Wed Mar 07 2007 - 17:19:25 CET