|
View previous topic :: View next topic
|
| Author |
Message |
vinaya kumar
New User
Joined: 06 May 2009
Posts: 4
Location: Bangalore
|
|
|
|
hi,
I am not much aware of how space is calculated during table space and table creation. I see below DDL format in our DB
CREATE TABLESPACE TSDEBPR IN CAPS002
NUMPARTS 7
(PART 1 USING STOGROUP CAPSG10
PRIQTY 21600
SECQTY 3600
ERASE NO
FREEPAGE 0
PCTFREE 10
TRACKMOD YES
COMPRESS NO
,PART 2 USING STOGROUP CAPSG10
PRIQTY 48
SECQTY 96
ERASE NO
FREEPAGE 0
PCTFREE 10
TRACKMOD YES
COMPRESS NO
and so on
now if I want to create a table space where table having a Lrecl of 120 and 7 000 000 records can be inserted and it should handle such amount of data. how should I calculate the value for primary and secondary quantity.
secondly when and where Partitioned TS should be defined, how to make the decision and how to set the space value for the same?
Please suggest. |
|
| Back to top |
|
 |
Raghu navaikulam
Active User
Joined: 27 Sep 2008
Posts: 193
Location: chennai
|
|
|
|
Hi vinaya kumar
| Quote: |
| now if I want to create a table space where table having a Lrecl of 120 |
What is Lrecl in table and how do you calculated this to 120?
Regards
Raghu |
|
| Back to top |
|
|
vinaya kumar
New User
Joined: 06 May 2009
Posts: 4
Location: Bangalore
|
|
|
|
Raghu,
I calculate based on column length. My concern is how to calculate space now for creating TS for 7000000 record capacity.
Please suggest. |
|
| Back to top |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008
Posts: 1020
Location: India
|
|
|
|
Hi vinay,
Row Length = 120
Quantity = 7000000
I calculated for a segmented tablespace.
For partition tablespace, you must calculate for each partitions
| Code: |
PRIQTY SECQTY ERASE FRPAGE %FR COMP TRKMOD
1245216 124560 NO 3 10 NO YES |
Now, how did i do this ?
I have an in-house tool called SPACE CALCULATOR.
Get familiar with the tools used in your shop.
Thank You,
Sushanth |
|
| Back to top |
|
|
Anuj Dhawan
Superior Member
Joined: 22 Apr 2006
Posts: 6250
Location: Mumbai, India
|
|
|
|
You need to compute how many rows will fit on each page. The "Administration Guide - Planning" has detailed information about how to do this. See the section "Space requirements for user table data" for detailed information.
A quick estimate for a 4k page size is: (1+freespace percentage)*num rows/(int(4028/(rowsize+10)))
Don't forget to plan for space for indexes.
Rows with varchar data in them will, as a general rule, take up less space than the maximum. This will (usually) allow more rows per page than the basic calculation indicates. Use the average length of the varchar column when computing row size.
At the other end of the scale, if the row size is very small, then you can hit the limit of maximum rows on a page.
There are also some other considerations that come into play when the row size is a large percentage of the page size. These relate the data manager's ability to find space for a new row to be inserted, getting in contact with your site-DBA might help further. |
|
| Back to top |
|
|
Anuj Dhawan
Superior Member
Joined: 22 Apr 2006
Posts: 6250
Location: Mumbai, India
|
|
|
|
| sushanth bobby wrote: |
| Get familiar with the tools used in your shop. |
Not necessarily every shop has such a tool (SPACE CALCULATOR) . . .  and who knows what actually happens in the back-end . . . |
|
| Back to top |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008
Posts: 1020
Location: India
|
|
|
|
Anuj,
Since this might be a usual scenario when a new tablespace is created. Every shop will be having their own strategy to deal with it.
Thank You,
Sushanth |
|
| Back to top |
|
|
|
|
