| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: storage clause
It sounds like you need to start at the basics. I'll try to keep this high
level and ignore some of the complexities.
Each field takes up a certain amount of space - varchar2(20) takes roughly 20 bytes if a 20-character string is placed in it, less if a shorter string is placed in it. Date fields consume 8 bytes, if I remember correctly. If you add up the sizes of each field you can get an approximate size to store a single row.
Having a wild guess (I forget the rule for number fields) your EMP table example is somewhere around 60 bytes per record. Storing 100 records will require about 6KB, storing 1,000,000 records will require about 60MB. There are tools such as TOAD (www.quest.com) that include features to help determine record and table sizing.
When Oracle stores records, it places them in blocks and keeps a certain amount of free space so it can easily update a record if required. You'll rarely store 60MB of data in 60MB of physical space. I guess the easiest rule is to be generous. Maybe allow double what you originally estimate.
Another really good way is to create the table you need, put some sample data in it and look at how big the table is (plenty of tools / queries to help out here). You can then scale the figure up based on how many records you expect to have. This is why understanding growth is important... If your table has 1000 rows today but will grow at 100 rows today then you might want to calculate space for 5000 rows so the table has enough space to last a year or so. Unfortunately growth can sometimes be very hard to calculate and understand - you need to know the application well.
Thankfully Oracle does a lot to simplify things. It introduces tablespaces, datafiles, and extents. Grab a pencil and draw along... Basically a datafile is a physical chunk of space on a disk. A datafile is allocated to a single tablespace, but a tablespace can contain many datafiles. A table is assigned to one, and only one, tablespace at creation. Table sizes are defined by extent sizes (the clause "initial 10m next 5m" means make the first extent 10MB and then make each next extent required 5MB). Yes, Oracle will simply grab another extent from the tablespace when required, assuming their is enough space left and you haven't reached maxextents (set this figure high). It will always grab extents from it's allocated tablespace, you have little to no control over (and should need to care) which datafile this may actually come from.
Typically if a table is growing rapidly and the tablespace is filling up there are two options... Datafiles can either be defined as fixed size (in this case you would add another datafile to the tablespace) or autoextending (where the datafile increases in size whilst there is physical space on the drive).
Hopefully this gives you a start to how Oracle uses the storage clause. I've skipped a lot of details, like the impact of PCTINCREASE so take my description with a grain of salt - realising that it's correct at a high level but ignores many complexities. Then there are locally managed tablespaces, which I believe remove almost all of the issues and handle things for you - Unfortunately I've not worked in an environment using them yet.
Hopefully I've helped more than confused you.
Regards,
Mark.
<netmadcap_at_net
zero.com> To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
Sent by: cc:
root_at_fatcity.c Subject: RE: storage clause
om
21/02/2003
10:43
Please respond
to ORACLE-L
Dennis,
sorry for being not clear. in fact, i dont even know the proper approach to take if i have to resize the table. ok, so 1st input required is how many rows will the table have ? 2nd is the growth pattern ?
now is there a certain approach to it ? say like, if the table is having 100,000 rows and then depending on the growth pattern, the table can be sized using a certain set of rules ?
to be brief, what r the rules/steps that u all follow during defining a table ? what points r considered & how do those play in sizing the table ?
sorry if i m asking very basic :(
Thanks !
ps : in the meanwhile, i m reading abt LMT !!!
-----Original Message-----
WILLIAMS
Sent: Thursday, February 20, 2003 3:46 PM
To: Multiple recipients of list ORACLE-L
Mad Cap
First of all, you didn't say how many rows you were storing in it. Second, what is the growth pattern - static, steady growth, fill and empty?
Study up on Locally Managed Tablespaces with Uniform Extents (LMT). Then pretty much the only decision is whether to use 128k, 4m, or 128m extent size.
Dennis Williams
DBA, 40%OCP, 100% DBA
Lifetouch, Inc.
dwilliams_at_lifetouch.com
-----Original Message-----
Sent: Thursday, February 20, 2003 3:19 PM
To: Multiple recipients of list ORACLE-L
Hi Gurus ...
can somebody help in sizing the table/index using the storage clause ? what r the key values to check and how to calculate the table size & the related storage parameters ? how does it change from an OLTP appln to a Datawarehouse ?
lets say for the table "emp" :
Name Null? Type
------------ -------- ------------
EMPNO NOT NULL NUMBER(4) ENAME VARCHAR2(10) JOB VARCHAR2(9) MGR NUMBER(4) HIREDATE DATE SAL NUMBER(7,2) COMM NUMBER(7,2) DEPTNO NUMBER(2)
TIA !
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: <netmadcap_at_netzero.com
INET: netmadcap_at_netzero.com
Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting servicesto: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting servicesto: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: <netmadcap_at_netzero.com
INET: netmadcap_at_netzero.com
Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting servicesto: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Privileged/Confidential information may be contained in this message.
If you are not the addressee indicated in this message
(or responsible for delivery of the message to such person),
you may not copy or deliver this message to anyone.
In such case, you should destroy this message and kindly notify the sender
by reply e-mail or by telephone on (61 3) 9612-6999.
Please advise immediately if you or your employer does not consent to
Internet e-mail for messages of this kind.
Opinions, conclusions and other information in this message
that do not relate to the official business of
Transurban City Link Ltd
shall be understood as neither given nor endorsed by it.
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Mark Richard
INET: mrichard_at_transurban.com.au
Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting servicesto: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Thu Feb 20 2003 - 19:08:47 CST
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
 |
 |