| Row chaining and Local Tablespace [message #132128] |
Wed, 10 August 2005 22:51  |
rkl1
Messages: 97
Registered: June 2005
|
Member |
|
|
Hi Guys:
Is row chaining is still a problem under the locally managed tablespace (LMT). I was told or now assumed that, once the tablespace is under LMT, then no need to worry about fragmentation or row chaining.
And again, when we make a table script, is there a need to put the percent free, percent used for tables (under LMT).If I don't, then it puts percent free 10% and percent used 40%. Is this the default. Is there a way to change it under LMT so I dont have to specifically mention every time I create a new table.
thanks.
|
|
|
|
| Re: Row chaining and Local Tablespace [message #132217 is a reply to message #132128] |
Thu, 11 August 2005 07:00  |
 |
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
|
Senior Member
Account Moderator |
|
|
LMT with uniform extents will completely eliminate fragmentation.
The pctfree, pctused can be left to default ( unless you need a specific case).
And there is really no need to specicy storage clauses inisde table DDL. Just leave it to default the tablespace settings.
[Updated on: Thu, 11 August 2005 07:01] Report message to a moderator |
|
|
|
|
|
| Re: Row chaining and Local Tablespace [message #132654 is a reply to message #132128] |
Mon, 15 August 2005 09:14 |
alliejane
Messages: 59
Registered: July 2005
Location: Glasgow
|
Member |
|
|
Also, row migration can also still happen with a LMT, it is not the case that you should just leave the settings to the defaults of 10% pctfree and 40% pctused.
PCTFREE
Take the example of a standard 8k block size, we load 500,000 rows into the table, and can fit 200 rows per block. That makes 2500 blocks.
If our row never get's updated then 10% of 2500 8k blocks is just fresh air!! now I know that only adds up to about 1.9M but we could have had 256 less blocks in the table, that makes index and especially full tablescans more efficient.
On the opposite scale, this could be a heavily updated row, which means that in practice many many rows may not be in their original blocks, that in turn means that index lookups will be less efficient.
This is not a trivial matter especially with large volumes of data and you shouldn't be complacent about it.
Now the good news, if your in Oracle9i and you use Locally Managed Tablespaces, you can use Automatic Segment Space Management (ASSM) this puts the management of how full or empty blocks are over to Oracle, no need to set PCTFREE (PCTUSED can still be set).
Post back if you want more clarification.
Allie
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
