Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2002
    Location
    Hamilton
    Posts
    150

    Unanswered: Create Index syntax

    I looked in the manuals and cannot find the proper syntax for creating an index and putting that index on a separate filesystem on AIX.

    That is, I would like to move the indexes to a separate filesystem and tablespace that would be solely used for this purpose.

    Any guidance would be appreciated.

    Mark

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Create table table1(....) in tab_tspace index in ind_tspace long in lng_tspace

    When creating the tablespaces, you should put them in containers you want.

    When creating an index, you cannot specify the tablespace name ...

    Cheers
    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Feb 2002
    Location
    Hamilton
    Posts
    150
    So what you are saying is that if Table a is in Tablespace a then index a has to be in Tablespace a as well?

  4. #4
    Join Date
    Feb 2002
    Location
    Hamilton
    Posts
    150
    Sorry forgot to include this information I got from an IBM Db2 V8 help page

    Specify separate table spaces for indexes
    Indexes can be stored in a different table space from the table data. This can allow for more efficient use of disk storage by reducing the movement of read/write heads during index access. You can also create index table spaces on faster physical devices. In addition, you can assign the index table space to a different buffer pool, which might keep the index pages in the buffer longer because they do not compete with table data pages.
    When you do not place indexes in separate table spaces, both data and index pages use the same extent size and prefetch quantity. If you use a different table space for indexes, you can select different values for all the characteristics of a table space. Because indexes are usually smaller than tables and are spread over fewer containers, indexes often have smaller extent sizes, such as 8 and 16 pages. The SQL optimizer considers the speed of the device for a table space when it chooses an access plan.

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    I guess what Sathyaram is saying is that you can only specify an index tablespace for all indexes (indices?) built on a certain table at the time when you create the table, and you cannot change it afterwards without re-creating the table.

    --
    Last edited by n_i; 10-21-04 at 16:56.

Posting Permissions

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