Re: Separating data, index objects

Mark A wrote:

> "DA Morgan" <damorgan_at_psoug.org> wrote in message 
> news:1120576807.104572_at_yasure...
> 
>>Not dishonorable at all. it isn't true with Oracle even on a single disk
>>drive. Go to google.com and read the copious comments by the authors I
>>previously referenced. Perhaps you are assuming Oracle and DB2 are the
>>same.
>>-- 
>>Daniel A. Morgan
> 
> 
> You apparently did not read my posts. I said (and was the first in this 
> thread to say so) that there is no practical difference in performance by 
> splitting the table and index data. You somehow have twisted what I posted 
> to suggest that I said the opposite. 

I just went into google.com and read every single one of your posts. With apologies ot others here they are, unedited, with my comments preceded by asterisks.

<qazmlp1209_at_rediffmail.com> wrote in message news:1120490229.230731.282800_at_f14g2000cwb.googlegroups.com...

 >> This is for Oracle version:9i.
 >>
 >> I understood that, separating data and index objects does not have much
 >> impact on improving the performance. But, still I wanted to know it
 >> just for information purpose.
 >>

Depends on your bufferpool hit ratio (which depends mostly on the size of bufferpool relative to total amount of data/indexes).

• And my comment was that bufferpool hit ratio, etc. are irrlevevant.

In theory, if the table data and index data are in the same tablespace, the disk contention could occur if there was a significant amount of I/O against the table and index at the same time.

• This is not true. Just not true. And the words above are yours and
• unedited.

But in an OLTP environment like you described, there could be a high transaction rate on the same tables and indexes, where it could make a difference.

• Once again ... this is not true in Oracle.

You are completely ignoring the fact that I said the differences were only theoretical and not necessarily noticeable or even measurable. Even the theoretical advantage is non-existent in a small to medium sized database with well configured bufferpools.

• Reread your previous posts above this one. That is not what you said.
• "Where it could make a difference" is NOT theoretical. Nowhere do you
• mention small to medium ... and while you do mention bufferpool
• configuration I've already stated that this is irrelevant to the
• topic and issues being discussed in this thread. If you disagre then
• post FACT not OPINION ... cite references. I'll acknowledge I am
• wrong if you can show me specifically where that is demontrated by
• testing.

You are basically repeating what I said about bufferpools and that if the data was already in SGA, there is no physical I/O.

• Definitely NOT repeating what you said about bufferpools as I started
• with the position that you are wrong and they are irrelevant to the
• thread.

If your SGA is 2 GB and your table is 4 terabytes and you are doing a mass insert on a table, where table rows and indexes are updated at the same time. I am not going to say "measurable" difference (although it could be), I just said theoretical. Some disk subsystems may be fast enough to handle it without a difference (especially those with asynchronous I/O).

• Once again I disagree. If you believe otherwise provide a
• demonstration that proves you are correct. Work published by
• Howard Rogers and others AFAIK say otherwise.

As is the usually case, you quote me out of context ....

Above you are quoted in-context and in your entirety. If you have any evidence obtained from benchmarking, StatsPack, AWR Report, or other please show me where I am incorrect. And I will state my opinion again for the record:

Separation of tables and indexes serves no useful purpose with respect to scalability and performance.

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)