Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2003
    Posts
    13

    Question Unanswered: Indexes in different tablespaces

    I was told that keeping table indexes in a different tablespace can improve the performance by reducing the I/O time. I guess this is true if the tablespaces are in different disks.
    But can you get any performance improvement if those tablespaces are in the same disk?
    If this is the case, I would like to do so since I only have one available hard disk.
    However, the Control Center doesn't seem to let you set the tablespace when creating indexes.
    Does anybody know how to do it?

    Regards,

    Cesar.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Cesar,
    You can get some performance improvement if the indexes are in a different tablespace but on the same disk. You need to create a separate bufferpool for index tablespace that should be big enugh to hold the entire index. That way the index is always cached, which nets tour performance gain.

    You do not set the index tablespace when you create the index, you can only set it when you create the table. I guess this is because the implicit indexes IBM creates (Primary, unique) can be define at table creation.

    HTH

    Andy

  3. #3
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: Indexes in different tablespaces

    As Andy says, caching the entire index in the bufferpool will result in improved performance even if the index is on the same disk ....

    You might see a slight performance imporvement(based on various conditions beyond your control) as there will be less fragmentation of the data and index entries if the table and index is held on differnet tablespaces though on the same disk ..

    You can have a separate tablespace for index , only if the table is created in a DMS Tablespace ..... You will have to specify that you want a different tablespace to hold the indexes when defining the table ...

    HTH

    Sathyaram


    Originally posted by cesar
    I was told that keeping table indexes in a different tablespace can improve the performance by reducing the I/O time. I guess this is true if the tablespaces are in different disks.
    But can you get any performance improvement if those tablespaces are in the same disk?
    If this is the case, I would like to do so since I only have one available hard disk.
    However, the Control Center doesn't seem to let you set the tablespace when creating indexes.
    Does anybody know how to do it?

    Regards,

    Cesar.

  4. #4
    Join Date
    Feb 2003
    Posts
    13

    Thumbs up Re: Indexes in different tablespaces

    Thanks a lot Andy and Sathyaram for your remarks and suggestions.
    I will update my tables this way.

    Cesar.


    Originally posted by sathyaram_s
    As Andy says, caching the entire index in the bufferpool will result in improved performance even if the index is on the same disk ....

    You might see a slight performance imporvement(based on various conditions beyond your control) as there will be less fragmentation of the data and index entries if the table and index is held on differnet tablespaces though on the same disk ..

    You can have a separate tablespace for index , only if the table is created in a DMS Tablespace ..... You will have to specify that you want a different tablespace to hold the indexes when defining the table ...

    HTH

    Sathyaram

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •