Re: Designing database tables for performance?

On Mar 7, 1:16 am, "Cimode" <cim..._at_hotmail.com> wrote:
> On Mar 7, 12:21 am, "joel garry" <joel-ga..._at_home.com> wrote:
>
>
>
> > On Mar 6, 12:52 pm, "Cimode" <cim..._at_hotmail.com> wrote:
>
> > > On 6 mar, 05:59, "d..._at_smooth1.co.uk" <d..._at_smooth1.co.uk> wrote:
>
> > > > On 24 Feb, 13:30, "Cimode" <cim..._at_hotmail.com> wrote:
>
> > > > > On 23 fév, 22:33, "jgar the jorrible" <joel-ga..._at_home.com> wrote:
>
> > > > > > > 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.
>
> > > > > So you say there are *ways* to count IO's. Fair enough. Question is:
> > > > > what has the way of counting IO's has any bearing on the media that
> > > > > supports them and therefore qualifies their nature as physical or
> > > > > logical? What is the difference: speed?
>
> > > > Yes. A logical I/O does not go down an I/O channel but comes via the
> > > > memory bus.
>
> > > And in what a memory bus is less *physical* than an IO channel? How
> > > do you think such bus is filled with data at some point in time
> > > otherwise than by a pull on the IO channel.
>
> > ??? The whole point is that it might have been updated only in memory
> > and may have nothing to do with I/O until some time in the future, or
> > maybe never if it is rolled back.
>
> You are talking about transactional aspects now and how such
> trasactional aspect may impact the definition of how is defined a
> logical IO vs a physical IO. Does not that sound strange to you.

No, I happen to work mostly on transaction oriented database applications.

> Besides, you have not answered the question I have asked you so I
> rephrase it: in what do you think a memory bus is less physical than
> an IO channel and how do you think such bu is filled in time otherwise
> than by an IO channel pull....Answer this question please.

It's not less physical. A particular piece of software counts hits in memory one way and requests to the operating system another way. The memory may be filled by memory transfers or updates from the software, or from fulfilled requests from the operating system. In the case of many people sharing the same data, it is generally faster for all of them to not be requesting that data over and over from the operating system.

>
> > You _really_ should read the Oracle concepts manual, as well as come
> > into this century to see how modern databases deal with multiversion
> > consistency. Thinking there is a one-to-one relationship between what
> > is in memory and what is on disk is just stupid.
>
> For your information, not only I know *exactly* how ORACLE works on
> transactional handling but also equally DB2 and SQL Server...
>
> According to your definition, because there is a difference in time
> and speed such difference determines the nature of how an operation is
> considered as being logical or physical. You are calling any other
> definition plain stupid. Why do you think this would be stupid?

I did not say the time and speed determines the nature. That was someone else. Watch your attributions. In fact, I don't agree with it. It is entirely possible for a heavily cpu-bound machine with fast I/O and a long chain of in-memory block copies to have faster I/O than memory. Certainly not an ideal situation, but it happens, and usually makes phones ring. And not in a "good" way.

I'm calling _your_ definition (ie, "How do you think such bus is filled with data at some point in time otherwise than by a pull on the IO channel.") stupid. If you understood how Oracle can have multiple copies of blocks in memory, you would not say such things. If five people are updating your birthday and none have committed yet and the I/O is saturated, where do you think the data is?

>
> > > > 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?
>
> > Not at all. One defines the terms from a viewpoint. In this case,
> > the viewpoint is "stuff that the Database has control over versus
> > stuff the Operating System has control over."

I goofed here by saying "Not at all." I was more determined to make the "viewpoint" point than pointing out you were attributing something to me that I didn't say. Sorry if I don't get paid to post on usenet.

>
> I am sorry but I am trying to understand by phrasing out exactly what
> you have stated previously.
>
> So you consider that they may be different viewpoints on what's
> logical and what's physical. What you will probably discover some day
> if you get a chance to put your hands on DB2 or SQL Server is that
> they also have different viewpoints than one another. According to
> the approach you are preaching, each editor has its own definition of
> the difference between logical and physical: In other words, this is
> an example of proprietary definition. What I am telling you is
> basically that there is a precise fundamental nonproprietary
> definition of the difference between a logical and physical IO and
> such definition is certainly neither ORACLE's nor, MS nor DB2
> definition but something that has been defined to be technology
> independent. Do you understand what I am getting at?

Yes, you are saying that proprietary definitions are useless and wrong. I disagree with that (although I can agree with the viewpoint). Yes, I can agree with theory and work with practice, they are not mutually exclusive, and I think you guys who say that they are simply don't know how to keep them separate. I think that demonstrates one of Fabian's points: You guys don't know how to think. So what if there is a precise fundamental nonproprietary definition? How does that make someone explaining a proprietary definition a borg-mind?

And what makes you think I've only worked on one engine?

>
> > If your theory can't deal with those concepts, it must not be a good
> > theory. I'm sure we'll all readily concede the terminology won't make
> > everyone happy, but that is no excuse for rejecting the terminology.
> > That just anti-semanticism.
>
> What terminology do you think we are rejecting ORACLE's, DB2's, SQL's
> server...And what theory do you think I am promoting? What I am
> presenting to you is simply the theory of RM that gave birth to the
> entire db industry. Its creator is Tedd Codd. Do you think that Codd
> himself did not know how to make the difference between logical and
> physical IO's?

I think you are using a transparent appeal to authority.

>
> I don't know what you mean by *anti semantiscism*. My point is not to
> discuss the words but what's behind the words.

That's an old joke. Say it out loud quickly in a newscasters voice: "Students protested outside the library. They claimed they were antisemantic."   Imagine a smiley.

jg

--
_at_home.com is bogus.
http://www.dailytech.com/Lockheeds+F22+Raptor+Gets+Zapped+by+International+Date+Line/article6225.htm