Datafiles Efficiency: More Files vs Increase the Size

Which is more efficient; adding more data files or increasiong the size of the data files of a tablespace?
Running on oracle 9.2 on win 2000 server.

It's mostly a personal preference. One thing to consider is recovery. If you have fewer, larger files then restoring and recovering a file with a bad block will take longer than if you had more, smaller files.

If you are not using raid then it is better to spread the data on multiple files on multiple disks.

It depends what kind is your storage: local disks or RAID

For local disks - you want to have many datafiles on as much local disks as possible

For RAID - it really doesn't matter. The datafiles are stripped anyway. it's a more for convenience not to have huge datafiles, since if you have to move them latter it's harder to find 20 GB disk space than 5 chunks of 4GB.

If you are going to be using RAID-5 (which is common), I cannot see how chopping up your database will help a lot. Unless you have multiple ranks of RAID-5 storage, all your data is stored in one big blob over which you have little physical control, if any.

I usually separate my table-tablespaces and index-tablespaces into different tablespaces and subtending datafiles, more of a matter of management than of performance, since I am not a big fan of self-extending Oracle storage.

RAID-5 automatically scatters your data when it is stored; it is part of its reliability functions and also what can help your performance in queries. If I were you, I would separate the database logically, and not arbitrarily separate the storage on physical boundaries, given they are meaningless in the RAID-5 environment unless you have multiple controllers and RAID ranks.

Have a Oracle Question
Do you have an Oracle Question?

Oracle Books
Oracle Certification, Database Administration, SQL, Application, Programming Reference Books

Oracle Application
Oracle Application Hints and Tips

Oracle Home
Oracle Database, SQL, Application, Programming Tips

All the site contents are Copyright © www.erpgreat.com and the content authors. All rights reserved.
All product names are trademarks of their respective companies.
The site www.erpgreat.com is not affiliated with or endorsed by any company listed at this site.
Every effort is made to ensure the content integrity.  Information used on this site is at your own risk.
 The content on this site may not be reproduced or redistributed without the express written permission of
www.erpgreat.com or the content authors.