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.
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
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 email@example.com
Rohit Nirkhe,Oracle DBA,OCP
Click Here to Expand Forum to Full Width