There has been alot of literature stating that you will recieve performance
improvements by seperating indexes and tables across multiple I/O points.
Ie... you have a tables tablespace and an index tablespace. If you put them on
seperate hard drives, you will have less I/O contention.
Now Im
I separate indexes and tables into different tablespaces for
maintenance purposes, not for performance, as there really is no
performance benefit if you are on a system with multiple users. At any
given time, many users will be doing queries that read the indexes and
many users will be doing
R,
Some of it depends on the disk storage. I have always followed the
time-proven method of organizing disks and placing indexes away from the
tables they belong to.
Our warehouse is using EMC external disk. What the warehouse architect did
was to stripe the EMC disks in such a way that all
Actually, there is a performance benefit, but is almost negligible.
Performance benefit comes from the fact that indexes are usually read
by using db_file_sequential_read, which is, as I was told by 3 or 4
wise men without any gifts, a single block read. Having vast majority
of I/O being short
I'll agree with Rachel's methodology and add another consideration.
Look at separating constraint indexes (primary keys, unique, perhaps even foreign
keys) from performance indexes. If you find resource constraints on backups
(time/disk), you can safely ignore the performance indexes. The
does anyoen disagree? Didnt this get started with the 'DBA Handbook' or was it a
different text?
From: Mercadante, Thomas F [EMAIL PROTECTED]
Date: 2003/07/15 Tue AM 11:10:05 EDT
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Subject: RE: should you seperate indexes from
-L [EMAIL PROTECTED]
cc:
Subject:should you seperate indexes from tables in seperate datafiles?
There has been alot of literature stating that you will recieve
performance improvements by seperating indexes and tables across multiple
I/O points.
Ie... you have a tables
Steroids, weight lifting, and a flattop hair cut (orange or green). After
two years of this, try talking to the storage guys while holding a beer in
one hand and a Polish sausage in the other. If you can manage a good belch
during the conversation, even better.
(Are you a visual person?)
I disagree with the concept of recovery not including some indexes because
they can be rebuilt later. To me, that's like going to a gas station and
only filling the tank half-way because I can get more gas later. You are
saving small amounts of time up front, but will pay for it later on.
I
access to data, then build the indexes while the instance is online?
From: Mercadante, Thomas F [EMAIL PROTECTED]
Date: 2003/07/15 Tue AM 11:39:32 EDT
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Subject: RE: should you seperate indexes from tables in seperate datafiles
I
Tom,
I prefer to backup the whole database, but in some situations that I have
encountered, it is not possible. If you look at the ratio of backups to recoveries,
the savings can be substantial. As part of the backup/recovery documentation, the
scripts to rebuild (actually the physical
Hi!
In some environments you just have to get some functions of database back
online ASAP and deal with other issues (like reporting performance or not
critical end users) later on.
OTOH, my experience with OLTP environments has shown that if you when you
lose performance indexes and try to use
- Original Message -
I must say that I haven't actually seen the benefits myself but my faith
is rock solid and I'll continue to separate data from indexes.
Don't want to debate faith... However, the technical side I can. ;)
The practice of separating indexes and tables into
respond to ORACLE-L
To: Multiple recipients of list ORACLE-L
[EMAIL PROTECTED]
cc:
Subject:should you seperate indexes from tables in
seperate datafiles?
There has been alot of literature stating that you will recieve
performance improvements by seperating
It's hot here. I wish I was at the beach and I feel like an ant.
Hmmm.
During an indexed query on a single table the index will be
accessed, then the table, then the index,then the table,
then the index,then the table then the index,then the table.
Assuming you get 4 rows returned :)
I may be way off base here, so any gurus please correct me with a gentle slap to the
back of the head...
Index and table access is not as simple as index entry..table row..index entry..table
row..etc. I just ran a quick test (which may not be represntative and is using the
primary key which
Hi!
During an indexed query on a single table the index will be
accessed, then the table, then the index,then the table,
then the index,then the table then the index,then the table.
Actually, if you think a little more, then you see that physical IO doesn't
occur like you described.
If
: Tuesday, July 15, 2003 10:25 AM
To: Multiple recipients of list ORACLE-L
Subject: RE: should you seperate indexes from tables in
seperate datafiles
Steroids, weight lifting, and a flattop hair cut (orange or
green). After two years of this, try talking to the storage
guys while
why is it useful to seperate different i/o pattersn? such as multi-block reads and
single block reads?
From: Nuno Souto [EMAIL PROTECTED]
Date: 2003/07/15 Tue AM 11:59:23 EDT
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Subject: Re: should you seperate indexes from tables
The thing that occurred to me a few years ago (as a result of a test
designed by Craig Shallahamer) is that what disks do gets very, very
complicated when you add users. On any system busy enough to have a
performance problem, the odds are usually slim that a disk is just sitting
there waiting for
PROTECTED]
Subject: RE: should you seperate indexes from tables in seperate
datafiles
R,
Some of it depends on the disk storage. I have always followed
the
time-proven method of organizing disks and placing indexes away
from the
tables they belong to.
Our warehouse
I LOVE THIS LIST!
A few more personalities (GAJA, ARI, IAN .) opinions added to this discussion and
we could publish a paper on this thread alone.
Thank you GURUS
You are the ones that make monitoring this list worth more than anything !
Luis
-Original Message-
Sent: Tuesday,
359
http://www.gridapp.com
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On
Behalf Of Stephen Lee
Sent: Tuesday, July 15, 2003 10:25 AM
To: Multiple recipients of list ORACLE-L
Subject: RE: should you seperate indexes from tables in
seperate datafiles
PROTECTED]
Subject: RE: should you seperate indexes from tables in seperate
datafiles
R,
Some of it depends on the disk storage. I have always followed
the
time-proven method of organizing disks and placing indexes away
from the
tables they belong to.
Our
indexes from tables in
seperate
datafiles
R,
Some of it depends on the disk storage. I have always followed
the
time-proven method of organizing disks and placing indexes away
from the
tables they belong to.
Our warehouse is using EMC external disk
: RE: should you seperate indexes from tables in
seperate datafiles
Matt
Thanks so much for your posting. I especially appreciated
your comment try not to be too smart. Would you consider
writing a book on the topic of I/O Devices for the Oracle
DBA? I would like to learn more
:
Sent by: Subject: RE: should you seperate
indexes from tables in seperate datafiles?
[EMAIL PROTECTED]
.com
cc:
Sent by: Subject: RE: should you
seperate indexes from tables in seperate datafiles?
[EMAIL PROTECTED]
.com
07/15/2003 12:19
PM
28 matches
Mail list logo
