Home » RDBMS Server » Performance Tuning » Seperate datafiles for tables in Same schema (Oracle 10g on Linux)
Seperate datafiles for tables in Same schema [message #303430] Fri, 29 February 2008 06:09 Go to next message
MIFI
Messages: 256
Registered: February 2008
Location: U.K.
Senior Member
Hi,

I am working on the structure of Schema in Oracle 10g, My Schema have different tables and used for different applications but in some reports they need to be joined.

I am creating seperate Index tablespace for other indexes and Prmary key index.

If i use seperate tablespace for different application tables and keep them in same schema, does it slow down the query joining tables from different application.

I want to keep them seperate to avoid contention in single file

Regards,

MIFI
Re: Seperate datafiles for tables in Same schema [message #303433 is a reply to message #303430] Fri, 29 February 2008 06:16 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
For performance
You need to store them in different disks, not just in separate tablespaces. Again, If you are using SAN, think about it.
Keeping indexes and Tables separated makes no difference in performance. May Help for administration though.
This is a dead topic. Discussed many many times. Just search the forum.
Re: Seperate datafiles for tables in Same schema [message #303443 is a reply to message #303433] Fri, 29 February 2008 06:28 Go to previous messageGo to next message
MIFI
Messages: 256
Registered: February 2008
Location: U.K.
Senior Member
We are using RAID 10, but i want to find out that seperating datafile for different tables will lower the contention or not and does not it will be very helpful to lower the risk if a corruption happen to any datafile. Application table in different datafile can be saved and other application will be running
Re: Seperate datafiles for tables in Same schema [message #303444 is a reply to message #303443] Fri, 29 February 2008 06:33 Go to previous message
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Idea is to distribute the IO.
Separating datafiles based on usage/administrative burden may help it.
But table data and index data are NEVER accessed in parallel and separating them makes no difference in performance.
>>very helpful to lower the risk if a corruption happen to any datafile
that is why the SAN/RAID thingies are for.
If you lose a disk, it can be replaced with minimal effort.
Again, Just search the forum.

Previous Topic: Tunning SQL Query
Next Topic: :x ORA-01920: user name 'PERFSTAT' conflicts with another user or role name :x
Goto Forum:
  


Current Time: Thu Dec 05 19:45:11 CST 2024