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

Thread: index tablespace

  1. #1
    Join Date
    Jan 2001
    Posts
    318
    Is it a good idea to have separate tablespace for indexes ?
    In what cases would you prefer separate tablespace for indexes. What are the adavantages of having separate tablespace for indexes and the rest of the database.

    Does it improve performance by any ways ? If so how ?

    thanks
    sonali
    Sonali

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Originally posted by sonaliak
    Is it a good idea to have separate tablespace for indexes ?
    In what cases would you prefer separate tablespace for indexes. What are the adavantages of having separate tablespace for indexes and the rest of the database.

    Does it improve performance by any ways ? If so how ?

    thanks
    sonali
    I like to put indexes in their own seperate tablespace, always. It will improve performance if the datafiles for the index tablespace reside on a seperate device than the data. If the index and data tablespaces reside on the same disk, you performance improvement is negligible...
    Jeff Hunter

  3. #3
    Join Date
    Jan 2001
    Posts
    318
    thank you.
    Sonali

  4. #4
    Join Date
    Jan 2001
    Posts
    318
    Why will this improve performance, whats the difference in
    same device and the separate once ?
    " It will improve performance if the datafiles for the index tablespace reside on a seperate device than the data "
    I was trying to find why this would happen I could not find any help...

    Thanks
    Sonali

  5. #5
    Join Date
    Apr 2000
    Location
    Baltimore, MD
    Posts
    759
    If the dbf of the data and indexes are seperated, they improve performance in terms of I/O and contention.

  6. #6
    Join Date
    Mar 2000
    Location
    Chennai.Tamilnadu.India.
    Posts
    658

    Solution

    Hi, 9th May 2001 20:25 hrs chennai

    halo has told exactly.

    you can also understand the same as

    Tables and indexes should be split in to seperate tablespaces because both are often inserted into and read from simultaneously.

    That is why partitioned tables and Indexes will improve performance of operation in DSS.This can be achieved by dividing a large table or index into multiple physical segments residing in diferent TS.

    Cheers

    padmam
    Attitude:Attack every problem with enthusiasam ...as if your survival depends upon it

  7. #7
    Join Date
    Jan 2001
    Posts
    318
    Are indexes part of shared pool ? If so then they will be in the same shared pool as of the rest of tables even when I have different devices..
    Then how does it affact contention. I can understand it will improve IO as it will act like a parallel processing.

    thanks
    Sonali

  8. #8
    Join Date
    Mar 2000
    Location
    Chennai.Tamilnadu.India.
    Posts
    658

    Contention=db buffer cache

    Hi, 9th May 2001 21:10 hrs chennai

    sonali Data,Index,Rollback are all part of db buffer cache.

    They are not part of shared pool.In the sared pool library cache hold the parsed SQL/PLSQL information.

    shared pool Data Dictionary hold objects prvs information etc.

    Imagine if you fetch a table data to db buffer cache it will also load its corresponding Index blocks also to memory.

    So reading them from different disk or TS will greatly help read write operation easier.

    Cheers

    padmam
    Attitude:Attack every problem with enthusiasam ...as if your survival depends upon it

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