Tuning

pms_reddy1
pms_reddy1 used Ask the Experts™
on
Do we need to create table  and Index( on the same table) in different table spaces in Oracle 9i/10g/11g ?
If so,Is it mandatory? If I am creating both on same table space what are the issues/problems I will face?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
It is not manditory.  With today's disk subsystems, it is really a personal perference.  I still do it but it is the way I learned back in the day.  Old habits...

The main reason for it in days past was distributing IO across different disks.  Talbes and indexes are typically accessed the same ways at the same times:  A read form an index usually means a read from a table.  Same for writes.
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
I supposed I should also add that different tablespaces can have different block sizes.  This can make a difference in performance and can help you decide what goes in what tablespace.
No, you do not need to create table and indexes in different tablespaces.  You can choose to put your tables and indexes in the same tablespace or in different tablespaces, it is entirely upto you.

Tablespaces allow you to manage disk space and to place different objects onto different disks.  This used to be a big issue in the "good old days", and there were performance improvements to be gained by ensuring your tables and indexes were placed on different disks.

Depending on your Oracle server this may still be something you need to consider or not as the allocation of data to disks is controlled much more at the o/s level.  If your o/s is setup to use striping then don't worry about it.

However, you may choose to use different tablespaces for administrative reasons i.e. to allow your dba to take certain tablespaces offline.
Amazon Web Services

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

Commented:
If your application is an OLTP ( deletes/updates/inserts and reads are almost equally distributed) then put them on a different tablespaces.

If your application is an DSS (reads at daytime and writes when ETL runs) then it does not matter (especially if your disk is on SAN)
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
>>then put them on a different tablespaces.

Still personal preference.  With the disk subsystems these days, actual performance benefit is pretty much non-existent but that does depend on how the disks were set up.
Geert GOracle dba
Top Expert 2009
Commented:
might want to add partitioning of tables

when creating partitioned tables, the tablespace for indexes is indicated as local
> which means indexes are in the same tablespace as the table

although this can be a flavor too, it isn't a must

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial