Re: Dreaming About Redesigning SQL

"andrewst" <member14183_at_dbforums.com> wrote in message news:3562363.1068042964_at_dbforums.com...
>
> >
>
> > > If minimising disk space is an issue, a DBMS
>
> > > like Oracle offers strategies for doing so, such as
> > clustering
>
> > > (physically storing the phone numbers with the person record -
> > without
>
> > > changing the logical 2 table design). Clustering also achieves
> > the
>
> > > "person together with all phone data in one hit"
> > requirement.
>
> >
>
> > Hmmm... I think I'd like to understand the distinction here a little
>
> > better.

One is logical. The other is physical. One is for expressing needs. The other is for delivering what was expressed. They are generally independent of each other but with some mapping between them.

> Perhaps if it's good enough for Oracle then maybe we could say
>
> > that maintaining the many:many relationship and data associated with
>
> > each phone in the Persons file using multivalues also retains the
>
> > "logical two table design". Doesn't seem quite kosher to me though.

It is not kosher because one does represent data as multivalues using a table or using a relation. One represents data as multivalues using a Pick file, but that is an entirely different creature.

> No, it wouldn't be kosher in Pick, because apparently for Pick the
> logical and physical model are the same thing. Using the MV approach
> you have 1 logical and 1 physical table. Whereas using a cluster means
> that we have 2 logical tables and 1 physical "cluster" which contains a
> combination of data from both logical tables. There is no way you can
> or would need to know that the data was clustered when using SQL; it
> appears to be in 2 separate tables - it IS in 2 separate tables, which
> are clustered. What this means is that if I want to select person
> "Mike" and all his phone numbers, I will query 2 logical tables ("select
> .. from person, person_phone where ..."), but that physically all the
> data will be read from 1 database "block" at once.
>
> > > there
> > > are "index-organized tables" for which person_phones is a
> > likely
> > > candidate;
>
> > Without knowing exactly what they are I would guess they're similar to
> > Pick indexes based on correlatives.
>
> Without knowing what THOSE are I couldn't say ;o)
>
> An index-organised table is a table that is, er, organised like a (b-
> tree) index. This is a physical implementation suitable for tables with
> only a few columns, and means that instead of doing an index lookup
> followed by a table lookup, we do a an index lookup and find the data we
> need right there in the index(-organised table).

Some products call this a clustered index because any non-indexed columns are clustered in the leaf pages of the index; although, you seem to suggest all the columns are indexed.

> > > there are hash join algorithms used when the person and
> > phone
> > > data for many people is required e.g. by a report or batch
> > process.
>
> > I don't think we'd need this so much in Pick.
>
> Maybe not. Question: when you read the person record for 1 person, does
> Pick actually only physically read into memory the data for that 1
> person, or does it read a "block" of records that include the record for
> that person? Hash joins would be useful in the latter case.
>
> > > But the idea that the relational approach is a
> > > "she'll be all right" approach is utterly absurd! I urge you to
> > educate
>
> > > yourself about it and understand why. Unlike Pick, the
> > relational model
> > > is not a product, it is a theory on which products can be based
> > (wholly
> > > or partially); a theory whose purpose is to find the BEST way to
> > store,
> > > manipulate and retrieve data.
>
> > How about if I tone it down and say instead it's an "any colour so
> > long as it's black" approach?

I suggest the mischaracterization is just as indicative of Mike's ignorance and stupidity as his previous mischaracterization.

> > I have to say again that I understand
> > that relational theory does an excellent job at determining the
> > optimum database design for *related* data - *BUT* it ignores
> > *relevance*. I think it is absurd to do so.

Relatedness is relevance. Imagining that related data are irrelevant to each other is just another symptom of Mike's ignorance and stupidity.

> Perhaps in Pick, if logical and physical are the same thing, it would be
> absurd - i.e. the choice of logical design impacts the physicla
> performance.
>
> I have tried to explain above why this is inapplicable in the
> relational model.
>
> > On reflection, we seem to have drifted off the "logical" differences
> > into "physical implementation" issues.

As if this stupid ignoramus could tell the difference.

> > Can we return to the
> > differences in the logical models. My argument is that Pick is more
> > flexible in adapting to real world requirements - *and* that it is
> > more efficient as a result.

Mike's statement amounts to: "Let's ignore performance and return to differences in expressing needs. Pick forces one to change the physical layout to match what one wants to express so Pick expresses things faster." Mike is intellectually impotent: he cannot conceive. Received on Wed Nov 05 2003 - 19:31:39 CET