Oracle TableSpace Table and Index Ratio

Now another question......

Is there a ratio for creating oracle table spaces using RAW file system.

Say I have PRODDTAT at 200 GB what will an adequate amount to assign to PRODDTAI.

JDE told me to do 50/50 but I think this will be a waste of space since we are using RAW volumes, because once created I will not be able to take back space unless the table spaces are dropped, and because of RAW I cannot do auto extend.

They are, probably, correct. The total index size is about the same as the
data's.

Regards,
Alexander Pastuhov
Pastuhov Consulting Pty. Ltd.
E-Mail: [email protected]
Mobile: 0414 453 433
WWW: http://www.pastuhov.com.au/index.htm
CNC Consulting and Software Tools for OneWorld®
Check out our Special Offers at:
http://www.pastuhov.com.au/special_offers.htm

RESTRICTIONS ON USE REPRODUCTION OR DISCLOSURE
The information contained in this email and attachments (if any) is intended
to be communicated only to the person or persons named as addressees.
Copyright subsists in this email. If you are not named as an addressee you
are prohibited from using, reproducing, or disclosing the information in
this email and the existence of this email itself. The information in this
email may also be confidential and subject to professional privilege.
If so, all rights are reserved by the owner of those rights. If you are not
a named addressee please advise us immediately and destroy all copies of
this email in your possession or control. Pastuhov Consulting Pty. Ltd.

----- Original Message -----
From: "cleolai" <[email protected]>
To: <[email protected]>
Sent: Friday, September 05, 2003 6:49 AM
Subject: Oracle TableSpace Table and Index Ratio


PRODDTAI.
are using RAW volumes, because once created I will not be able to take back
space unless the table spaces are dropped, and because of RAW I cannot do
auto extend.
http://www.jdelist.com/ubb/showthreaded.php?Cat=&Board=OW&Number=60988
messages, login to http://www.jdelist.com/forums, click Control Panel, then
click Edit by "Subscribe / Unsubscribe from receiving board posts by email,
change message notifications, etc." and adjust your subscription
preferences. JDEList is not affiliated with JDEdwards®

Cleola,

Why go RAW at all?

RAW support was initially created to overcome:

1. Unix's inability to support large files in most earlier versions. Almost all of today's Unix OSs have added large file support.
2. With earlier hardware there was a 5-15% increase in performance. These days the hardware and OSs are much more efficient and the performance advantage of raw partitions has decreased. These days, most hardware and OSs do extensive caching and thus can actually cause diminished performance with raw.
3. Sharing between clustered systems. Again today's hardware/OSs can handle without using raw partitions.

You have already identified some of the disadvantages of raw partitions.
Some other drawbacks:

1. You cannot use a raw partition for multiple tablespaces. A raw partition is not the same as a file system where we can create many files.
2. As you said you have to create them with specific sizes. When the databases grow in size, raw partitions cannot be extended. You must add extra partitions to support the growing tablespace. There may be limitations on the total number of raw partitions you can use in the system.
3. You cannot use the standard file manipulation commands on the raw partitions, and therefore on the data files. You cannot use commands such as cpio or tar for backup purposes. Backup strategy becomes more complicated.
4. Administrators need to keep track of the raw volumes with their cryptic naming conventions like /dev/rdsk/c8t4d5s4.

Net, net for a very small performance increase (if any) you gain a lot of administrative headaches.

Back to your original question - how to size PRODDTAI? The answer of course is that it depends on how much data you store. If your business generates ungodly amounts of transactions that you must keep online for years then your index tablespaces will most likely be larger than the data. Otherwise it will be somewhat smaller (200GB is an ungodly amount to me). As far as sizing PRODDTAI - if you didn't use RAW you'd have a lot more flexibility - otherwise make it at least the same size as PRODDTAT - Indices take up proportionately more space as the volume of data grows.

Unfortunately going RAW was not my decision. The entire company uses RAW for all databases. The DBA's seem to think that this is the way to go so we are stuck with it.