Mark,
yes, u helped a lot than confusing :)
atleast, i got a direction & hints to start with. i guess, i have to keep
practising to understand this well.
others, once again sorry for such rudimentary doubts !
-sam
-----Original Message-----
Richard
Sent: Thursday, February 20, 2003 7:09 PM
To: Multiple recipients of list ORACLE-L
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@net
zero.com> To: Multiple recipients of list
ORACLE-L <[EMAIL PROTECTED]>
Sent by: cc:
[EMAIL PROTECTED] 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.
[EMAIL PROTECTED]
-----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: <[EMAIL PROTECTED]
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: DENNIS WILLIAMS
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: <[EMAIL PROTECTED]
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (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).
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
>>>>
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: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: <[EMAIL PROTECTED]
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (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).
