index tablespace

[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

[marist89]

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...

[sonaliak]

thank you.

[sonaliak]

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

[Halo]

If the dbf of the data and indexes are seperated, they improve performance in terms of I/O and contention.

[padmam]

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

[sonaliak]

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

[padmam]

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