Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: storage clause

RE: storage clause

From: <netmadcap_at_netzero.com>
Date: Thu, 20 Feb 2003 21:53:39 -0800
Message-ID: <F001.00553E43.20030220215339@fatcity.com>


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_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 services

---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: 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).
--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: DENNIS WILLIAMS
  INET: DWILLIAMS_at_LIFETOUCH.COM
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: 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).

--

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 services

---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: 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).
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
>>>>

   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 services

---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: 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).

--

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 services

---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: 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 - 23:53:39 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US