Re: Separating data, index objects

Holger Baer <holger.baer_at_science-computing.de> wrote:

>> Forgive me for jumping in here, but *_a priori_*, it seems obvious
>> that it *_should_* serve such a purpose.

>As soon as you start a sentence with it seems obvious, it *is* obvious
>that it *isn't* obvious. In other words: what you just said is wrong.

I perhaps should have used the word "counterintuitive". It seems counterintuitive to me that separating the index from the actual data on different disks should make no impact on performance.

I'm trying to understand why it doesn't - i.e. I believe Daniel Morgan and I believe you that it doesn't affect performance.

>> What is the mechanism by which Oracle prevents this from happening?
>How are tables and indeces used?

>> Here, we are talking about tableX with indexX, but it does still make
>> sense to separate different tables and indices to avoid contention -
>> or is this not correct either?

>No. You separate *segments* to spread I/O. If the segment in question
>is an index or a table has nothing to do with it.

OK, so now here's where my understanding is breaking down. I thought that the DBA created tablespaces sufficiently large to hold the data (+ a bit more, just to be sure!), and that management of Segments and Extents was done internally within the instance itself.

>And if you're not interested in the last 5% of possible performance,
>then SAME might very well serve this purpose.

I looked up the SAME keyword in both Oracle Essentials and Oracle SQL and I can't find a reference to this or in Tom Kyte's book (Effective Oracle by Design).

>You know, the error most people make regarding this topic, the don't
>think how an index is used.

OK, fine - I am more than willing to learn about the internals and am willing to put in the effort to chase up URLs or do Googles or whatever it takes.

>An index is either
>a) a mechanism to quickly retrieve a block from a table, or

Aha! Now *_this_* I do understand! 8-)

>b) it can be used as a skinny version of a table.

IOT's - yes? Am I even warm?

>If we're talking about a), then any session using an index will do so
>first and then retrieve the table block. So why should tables and indexes
>contend inherently more than tables and tables or indexes or indexes if
>we throw in multiuser?

Say we have an app (I recently wrote a membership system for An Óige (the Irish Youth Hostel Organisation)), whose budget didn't stretch to Oracle, but let's take it as an example.

It was very simple - there was a Member table where the vast bulk of the data was stored, and then various other tables which were foreign keys into the Member table (title (Mr, Mrs, ...), County (Dublin...))

Now, this system receives approx 30 - 40 records a day, so we had no problems with performance, I'm happy to say.

However, imagine a scenario where this app was constantly being accessed, updated and added to by thousands of concurrent users. The main Member table being the one by far the most used.

Now, under a multi-concurrent user model, users are constantly referring to records in the Member table, by using the indices.

Now, if the table and the index are on different disks, then user1 can read the index, system goes off to search and retrieve the data, but while that's happening user2 can search the index, because there's no contention on the disks, them being on different disks.

My understanding of why it might be useful to separate data and indices. Why have tablespaces at all, if not for that reason? I believe, though am not certain, that PostgreSQL introduced a tablespace feature in their latest version 8 and separating indexes and tables was touted as an advantage. AFAIK, PostgreSQL uses a record-versioning (record shadowing) MVCC model similar to Oracle's?

Please don't take what I have written above as being my definitive statement of the facts - it is my understanding, and I am more than willing to be corrected if I am wrong.

>If we're talking about b), then there is no table access at all and the
>whole discussion is moot.

IOT's, you get the data with the index, yes?

Paul...

>Holger

-- 

plinehan __at__ yahoo __dot__ __com__

XP Pro, SP 2, 

Oracle, 9.2.0.1.0 (Enterprise Ed.)
Interbase 6.0.1.0;

When asking database related questions, please give other posters 
some clues, like operating system, version of db being used and DDL.
The exact text and/or number of error messages is useful (!= "it didn't work!").
Thanks.
 
Furthermore, as a courtesy to those who spend 
time analysing and attempting to help, please 
do not top post.