Usage of "users" tablespace for application tables

[Vinu Neelakandhan]

Hello :

I would like to know the advantages and disadvantages of using the "users" tablespace for creating all the tables for my application. This way, instead of having a specific tablespace created for my tables, I am using the default users tablespace.

Are there any potential problems/issues with this method?

Is it recommended to use a separate tablespace for usage by my application?

Thanks in advance,
Vinu

[Aashish Raj]

The application users objects must be in separate tablespace from other user.
We create multiple tablespaces for application tables,indexes depending on the size of application objects.

All the other users , we grant users as default tablespace which is a small tablespaces relative to application tablespace.

For database monitoring tools objects like statspack, we use tools tablespace.

Thanks
AR

[Volker Borowski]

Hi,

if you talk about oracle....yes, it is strongly recommended!

If you distribute data along tablespaces, you can move parts of your database physically on your filesystem according to your needs.
Highly frequented data onto the fastest disks.
Balance index and table data onto diffrent physical disks, so that the disk access is optimized.

In addition, there is no way but to export/RE-CREATE/import the entire database if you need to reorganize the SYSTEM tablespace. If you fragment your system tablespace with user data, this would be very bad.
One thing that would fragment your SYSTEM-tablespace to hell will be, if you put your rollback segments there.

Minimum Setup would be to heave
SYSTEM, TEMP, ROLLBACK and DATA (with DATA containing both indexes and data) to avoid the fragmentation problem of the SYSTEM tablespace.

Medium Setup would be to devide DATA into DATA and INDEX and put both onto diffrent physical disks for better access performance.

Best setup would be to devide DATA into MASTERDATA/INDEX and TRANSACTIONDATA/INDEX and put them onto diffrent physical disks for best access performance.

Why ?
Consider a business transaction, reading all items of september salesorders from all companies in wonderland !

You'll gonna scan the masterdata-index for companies in wonderland (assumed the region column of your company-table is indexed properly), and read the corresponding data information from the masterdata-data tablespace.
Now you scan the transactiondata-index for the salesorders belonging to the company id you found with your previous reads and skip those that are not dated september (yes, this will require an apropriate index also). With this indexinformation the database will finally read the salesorder items from the transactiondata-data tablespace.

If wonderland has quite a lot companies, compare the diskhead-movement that is required if you have a single tablespace on a single disk (would look like tap-dancing) against access to four diffrent physical disks.

Hope this was helpfull
Volker