DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Index tablespace

  1. #1
    Join Date
    Jan 2001
    Posts
    642
    Hi,

    1) I just want to know is there is any thing like Index tablespace, or it's like any data tablespace but managed to contain only indexes.

    2) How do I create an "Index " tablespace
    3) Can I assign a table to the index tablespace...

    More clarity required on this.
    Badri nath

  2. #2
    Join Date
    Feb 2001
    Location
    Baltimore,MD USA
    Posts
    27
    Dear,
    Yes Index tablespace is same as data tablespace.
    It was there to reduce contention.
    If u have a table in one ts and it's index on another ts then
    performance will increase.
    that's why Index TS was there
    (2) U can create table on index tablespace. and
    u create TS with same Create Tablespace command

    kuckoo

  3. #3
    Join Date
    Feb 2001
    Location
    Bombay,India
    Posts
    530
    Hi,
    See tablespace concept is logical and is used for administrative and for performance issue.See when u create the database there is only one tablespace called system which stores data dictionary,so a DBA creates various tablespaces which contains application data,indexes,temporary sorting etc as per its need.The DBA can create a tablespace called USERS_DATA to store the application data and a INDEX tablespace to store the associated indexes on the application tables.This helps in improved performance and to avoid contention.So instead of keeping ur application data and indexes in the same tablespaces DBA can create another tablespace to keep the indexes.Since indexes do fragment more as DML takes place on ur tables,it might slower down ur performance for retrival of data.So in all the organisations ,DBA keeps index seperate from DATA for performance reasons and to avoid contention.
    See the syntax for creating the INDEX tablespace is the same as the syntax for creating a normal tablespace.See u can easily assign the table to the index tablespace instead of assigning it to other tablespace when u specify the TABLESPACE clause in the CREATE TABLE syntax.U specify the index tablespace name rather than USERS_DATA tablespace.but the user should have quota on that tablespace.
    I hope that I have clearily anwered ur question,if u have any doubts please be free to write to me at rohitsn@altavista.com

    Regards,
    Rohit Nirkhe,Oracle DBA,OCP

Posting Permissions

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


Click Here to Expand Forum to Full Width