Hi

The session which does 'reorganize data' do they perform the same function as the command called 'coalesce' in oracle8i ?
Pls advice
Thank you and have a nice day

Yours in service
dhnish

Simply put, no.

Gilbert Guymer
Database Administrator
Lufkin Industries, Inc.

Hi

But the Baan guys are saying 'reorganize' would actualy reclaim space , for example after doing purge for a particular table (deletion of many records) .

If thats so, in Oracle 'coalesce' also can do the same , right?
Pls advice. I'am confused - whether after doing 'purge' should i do 'reorganize' from Baan OR should i do 'coalesce' from Oracle?

Pls advice. You assistance is much appreciated
Thank you and have a nice day

Yours in service
dhnish

We had done re-organize earlier to reclaim space but without success. Re-organize merely deletes table and rebuilds table . But at the database level oracle does not reclaim any space.

This happens in case of Baan's native database bisam.

Hitesh,

Did you reclaim space at all? Did you do it in oracle? Please let me know as we are looking to reclaim some space too.

Just to finish this topic:

The Baan session "Reorganize tables" does nothing else, then to dump the tables, recreate them and load them again.

So why does the session exist?

To reorganize a table this way, made sense, when you were using tbase. Tbase is not actually deleting entries in a table, but marks them as deleted. So with tbase reorganizing by dumping and loading actually wins space.

With Oracle the above does not happen. With Oracle you might win space, by choosing different storage parameters in ora_storage.

But there are only two possible cases:

a) Either you have a very small DB (below 100GB), then you are better off, just buying some disks.

b) Or you DB is big, then doing a reorganize with Baan tools (bdbpre/bdbpost) takes way to long. In this way, you would use exp/imp.

To make it short: If you use oracle, then forget this session.

Enjoy Baan anyway,

regards

Markus

Just a little bit more ...

oracle's "coalesce" simply merges adjacent chunks of free space to make a larger chunk of free space.

If you were to look at a map of your tablespace you might see something like this:

block 1 <extent of tfgld410> 1Meg
block 2 <free> 16k
block 3 <free> 16k
block 4 <extent of tfgld410> 1Meg
block 5 <free> 16k

if you run coalesce on this tablespace you'd see
block 1 <extent of tfgld410> 1Meg
block 2 <free> 32k
block 3 <extent of tfgld410> 1Meg
block 4 <free> 16k

No space was created, you just now have larger chunks of freespace available. (useful for those initial and next extents)

Also - If your tablespace's default pctincrease is greater than zero, Oracle automatically will coalesce your tablespace when the smon process runs.

Dave

Originally posted by skosana
Hitesh,

Did you reclaim space at all? Did you do it in oracle? Please let me know as we are looking to reclaim some space too.

No, We never reclaimed space. We opted for more space buying new hard disks at that time.

Hi

So it seems the process is more like 'defragment' on Windows platform.
Thus is would be better to use imp/exp from database level to reclaim space. Also changing the ora_storage parameter should be considered.
Thank you very much guys.

Yours in service
dhnish

All you are going to accomplish with a reorg is the freeing up of fragmented free space in Oracle, which may or may not amount to much. You will also eliminate row chaining across blocks too. Current Oracle space management involves using the same extent sizes, and paying attention to pctused and pctfree to reduce or eliminate row chaining. You will still need to add space, but you will have reduced the wasting of free space because the database doesn't have to look for the right sized extent or use one that is close only to cast off the remainder that nobody else can use. To find out if you are going to get anthing back, check the amount of free space that is unused and if the size of the extents still available are usable by any of the next extent sizes of the tables. DBA_FREE_SPACE is a good place to start.


Gilbert Guymer
Database Administrator
Lufkin Industries, Inc.

1. Baan reorg and oracle import/export does the same.
2. It is not always possible that every re-org will result in more free space in the tablespace.

The explanation below is for Oracle:

The tablespace size is fixed once it is created. It will grow only when you add the new datafile or increase the size of the datafile. The tablespace size decreases if the datafile is deleted/detached from the tablespace.

The free space within the tablespace reduces when a new extent is added/allocated to an object (a table) and it grows when the extent is de-allocated from the object.

Allocation or de-allocation of the extents does not happen for every record added or removed from the table. It happens for say for about 50000 rows (it depends on PCT_INCREASE and the previous extent size).

Lets take an example:

Table: tdsls400
Its INITIAL (EXTENT size) is say 10 K and %increase is say 10%.
Lest say it has allocated 5 extents (10K, 11K,12.1K, 13.3K, 14.6K) (10% increase form the previous one). A total of 10 + 11 + 12.1 + 13.3 + 14.6 = 61K

When you do a re-org from baan, the process exports the data, drops the table and recreates the table with optimal table storage parameters. It may create a table with 40K as initial and 10% increase. Then as the data is filled into the table it may run out of space and the system allocates a new extent of 40 + 10% = 44K to the table. Now the total space occupied by the table is 40 + 44 K = 84 K which is > than initial space occupied by the same table.

So it is not always possible that every re-org will result in more free space in the tablespace.

Or it may happen that the table is created table with initial extent say 50 K with % increase of 10. The entire data may fit into this 50 K and it may not need additional extent. So the new tables size is 50 K and resulted in more free space in the table space.

But, in either case, since the data is spread on less number of extents it will be faster.

Reorg from baan is same as export/import from oracle.