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]DB2 & UDB email list listserv db2-l LazyDBA home page