| calculating space for new table? [message #124469] |
Sat, 18 June 2005 21:33  |
confident
Messages: 28
Registered: March 2005
|
Junior Member |
|
|
Hi friends,
rightnow i am in tablespace sizing of new project, can anybody tell,by looking from below table structure, for 1 million records, how much its occupies the space in tablespace..
CREATE TABLE ASC_OT_TPL
(
SDC_INST_N NUMBER(10)
ENTIY_TY CHAR(5),
TBL_NM VARCHAR2(20),
ANA_TIMESTAMP DATE
)
and db_block_size=8k
how i do calculate the sizing... any formula,pls tell me....
|
|
|
|
| Re: calculating space for new table? [message #124625 is a reply to message #124469] |
Mon, 20 June 2005 09:30  |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
There are scripts and things out there to estimate size. Also I think the docs say something about it. Depends on the amount of nulls though and on size of your data.
Can also load a sample of it, like 100K rows, and then multiply it out to get a rough estimate, but an estimate is all it will be. Or just load all your data and see.
|
|
|
|
| Re: calculating space for new table? [message #124636 is a reply to message #124625] |
Mon, 20 June 2005 12:49 |
William Robertson
Messages: 1643
Registered: August 2003
Location: London, UK
|
Senior Member |
|
|
There used to be a formula in the documentation a few years ago, but I can't find it now. Maybe they took it out because it always gave the wrong results (it did for me anyway). I agree with smartin - just generate a few thousand rows and see what it comes to.
Note that the PCTFREE will make a big difference and should be thought about carefully, as will the number of null values (can all of the columns really be null?) and the length of the VARCHAR2 values. If it's a staging table for bulk loads and you define it PCTFREE 0 COMPRESS it will come out a lot smaller than a regular application table. If it's an index organized table you don't have to allow space for the index.
CHAR is essentially an obsolete datatype and should never be used for anything.
btw it's up to you of course, but it's good practice to use clear, easily understood names for tables and columns. What is ASC_OT_PL? Aren't there some real words that would suggest what it's for? Is SDC_INST_N the primary key? What on Earth is TBL_NM? Surely it can't be an abbreviation for TABLE_NAME. You have 30 characters to play with! People are going to have to remember that NUMBER, TABLE and NAME become N, TBL and NM but TIMESTAMP is written in full (and it isn't actually a TIMESTAMP). Remembering all of that is going to be MORE effort than typing the whole word in the first place IMHO.
[Updated on: Mon, 20 June 2005 12:51] Report message to a moderator |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
