Thanks to all who replies.
I've check the create table statement and found the index tablespace clause .
The database will be on a SAN so i have no control over it.
My interest in separating tablespace was to use a different blocksize and buffer pool than the ones for the tables.
Stephane
-----Original Message-----
From: [Email Address Removed] [mailto:[Email Address Removed] 2 décembre, 2003 15:52
To: russ.[Email Address Removed] DB2 UDB LazyDBA; Stephane Paquette
Subject: Re: Index versus tablespaces
Russ is correct. It really depends on your data storage. If you are
using SAN storage then separating index from data does no good since it
will all be going on the same SAN partitions anyway. Plus it is so quick,
you won't notice a performance difference. Most important to performance
is separating tablespaces to different bufferpools depending on their
usage.
Thanks,
Lisa M Gardner
Database Administrator, Corporate Internet Group
Bank One
russ.[Email Address Removed] 03:12 PM
To
"Stephane Paquette" <stephane.[Email Address Removed] "DB2 UDB LazyDBA"
<[Email Address Removed] Index versus tablespaces
Stephane, The real answer is - it depends.
You could always create the table using DMS and seperate them if you want
too. The definition of where the indexes are placed is in the create table
- not in the create index - does feel a bit strange for them to be there
doesn't it.
Indexes and data in the same tablespace from a pureist standpoint doesn't
seem to be logical. However, they are in different datasets within the
tablespace. Disk Striping will help to keep them seperate. I have never
experienced any real issues with them being together (and SMS is a whole
lot easier to manage than DMS).
In Oracle - the default is that Indexes and data go together (the DBA has
to seperate them). I have supported several Oracle database where the
previous DBA was lazy and didn't bother to seperate them and performence
is
not a problem.
It all depends on the purpose of your application. If less than a second
response is required then you look at ways to speed things up (maybe by
seperating data and index - or using raw devices instead of file systems).
If you are working on a data warehouse application then the placement of
indexes and data are not that critical.
Bottom line, I don't think you will see a real difference having data and
indexes in the same tablespace. But if you need sub-second response
consider seperating them as part of an overall plan to speed things up.
And welcome to the world of DB2.
Russ
"Stephane Paquette" <stephane.[Email Address Removed] on 12/02/2003
02:44:13 PM
To: "DB2 UDB LazyDBA" <[Email Address Removed] Index versus tablespaces
Hi,
Always been on Oracle, now I'm working on my first DB2 database (db2 udb
8.1 on aix)
It seems that the indexes are always created in the same tablespace than
the related table and there is no tablespace keyword in the create index
synthax.
Am I correct ?
Tables and indexes in the same tablespace, is that the way to go ?
TIA
Stephane Paquette
Administrateur de bases de données
Database Administrator
Standard Life
www.standardlife.ca
Tel. (514) 499-7999 poste 7470
stephane.[Email Address Removed] <
mailto:stephane.[Email Address Removed] CLICK REPLY-ALL TO SEND A REPLY TO EVERYONE
Get today's cartoon: http://www.LazyDBA.com
To unsubscribe, e-mail: db2udbdba-[Email Address Removed] additional commands, e-mail: db2udbdba-[Email Address Removed] CLICK REPLY-ALL TO SEND A REPLY TO EVERYONE
Get today's cartoon: http://www.LazyDBA.com
To unsubscribe, e-mail: db2udbdba-[Email Address Removed] additional commands, e-mail: db2udbdba-[Email Address Removed] transmission may contain information that is privileged, confidential and/or exempt from disclosure under applicable law. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution, or use of the information contained herein (including any reliance thereon) is STRICTLY PROHIBITED. If you received this transmission in error, please immediately contact the sender and destroy the material in its entirety, whether in electronic or hard copy format. Thank you.
DB2 & UDB email list listserv db2-l LazyDBA home page