DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: TABLE STORAGE PLANNING --OVERHEAD?

  1. #1
    Join Date
    Feb 2001
    Posts
    4
    Hello,

    I am trying to plan my storage needs for a database. If I expect to have about 30 bytes per row (My rows will be a timestamp(7 bytes + 1 for length), a number (approx 6 or 7 bytes) and a varchar2 (approx 15 bytes), do I need to consider any other overhead (i.e rowid is not stored in the table, but do I need to consider it for space requirements?)
    I will also have to consider indexing requirements right?

    If a row is 30 bytes, and i will be writing about 5 million rows per day i get
    30 bytes * 5 million = 150 million bytes div by 1024 and 1000 = 146 MB per day

    However, when I actually try to implement this scenario in oracle, I end up using about 30% more storage
    I analyzed my row to make sure each was 30 bytes (using the analyze table command and the dba_tab_columns table-avg row len).

    Any suggestions appreciated.

    Jon

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    What is your PCTFREE for that table?
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  3. #3
    Join Date
    Feb 2001
    Posts
    4

    PCT FREE

    If there are 30 bytes in a row and block size is 8k and pct_free is 10, can I say that each row gets 5 blocks and in essence my 30 byte row takes up 40 k?

  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    approximately 8K block should fit around 250 rows of 30bytes each with pct_free 10 so around 155MB is used / day. There arent any updates at all?

  5. #5
    Join Date
    Feb 2001
    Posts
    4

    rowid and pct_free question

    Does rowid require 10 bytes of storage ---in memory (is it correct to say it is dynamic?) ?

    Is it ok to use pct_Free of zero if I expect to have no updates at all?
    (if not what is the lowest I should use for pct free---I am getting information from
    a plc and I do not forsee any update)

    Finally, I have an integer value 229 that I put in a table and then I got the avg_col_len and it said
    3. Is this in bytes? I thought a number like 229 should take 7 bytes. The date field said avg_row_len was 7...but I have heard that it is really 8...one byte for length...is there some
    kind of length storage for numbers too?

    Thanks in advance,

    Jon

  6. #6
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    ROWID
    Rowids require 18 bytes of space. But rowids are not stored in table - they are stored only in the indexes as pointers to actual table rows. So they have no influence on your table storage.

    PCTFREE=0
    If you are sure there will not be any updates at all, this is perfectly accaptable setting for table (it is different story with indexes...)

    Storage occupied by number 229
    It takes 3 bytes to store this number in Oracle. What makes you think it will use 7 bytes? Oracle stores numbers very efficiently, read about it in Concepts manual. For example, the number 1,000,000,000 will ocupy only 2 bytes of space.

    Storage occupied by dates
    Daytes always occupies 7 bytes of storage. It is fixed length, so no need for extra byte for length.

    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width