Re: Table Storage Estimates

1. Fragmentation depends on how the application use / manipulate the data. The important parameter is PCTFREE and PCTUSED;
   - Frequent inserts & deletes will not cause fragmentation if PCTFREE 0 and
   PCTUSED high enough: I assumes your calculation is correct that a row (max size) fit to about 1 database block and initial insert is about that size.
   - Frequent updates will cause fragmentation if initial insert is less than
   half size of database block and updated later on.
2. Of course, addidng datafiles will consume more space, because it is allocated to those datafiles (which may still partly empty). Contention happens when there is possibility of many users accessing the same database resources. For example, if 2 tables will be updated frequently by many different users, separate it on different hard disk or configure RAID properly. A common pratice is to separate tables and their indexes. (I ask others to complete this with others common practices. Speak up, Guys!). If your row size is more than the size of a database block, it will have row chaining. To avoid it, set larger database block (I am sorry, you need to recreate the database).
3. I think you can avoid the column BODY in your query; SELECT COUNT(mandatory_column) FROM table_name; Or, if you do not have a mandatory column, try SELECT COUNT(NVL(any_column,'0')) FROM table_name;

HTH

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Wednesday, February 28, 2001 7:30 AM

Hi,

I have a table which has a structure similar to the following.

MID                                 NOT NULL VARCHAR2(24)
NAME                                               VARCHAR2(512)
PRIORITY                                           NUMBER(38)
PERSISTENT                                         CHAR(1)
EXP_DATE                                             DATE
BODY                                                LONG RAW

I estimated the table storage for this table using the formula given in the Oracle documentation. It comes to around 1 rows per block.

It is estimated that the number of rows for this table will not exceed 200.

My questions are

1. Doesnt this lead to fragmentation.?
2. I have added three datafiles to this tablespace as of now and it seems like this might require more space!!!!!!!!. How do I effectively redesign this table to avoid contention and chaining?
3. Moreover, initially when there were 105 rows inserted, I issued a count(*) and it took about 5 minutes? How do I reduce the time taken to execute the query?

I would highly appreciate if someone comes forward to help me on this.

I am using Oracle 8.1.6 on Solaris

Sanjay

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Yustiono
  INET: tekait11_at_bni.co.id

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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).