Oracle 10.2.0.5 table BALDAT reorg

[Former Member]

Hello,

I have a doubt regarding a table reorg of BALDAT. Since this table has long raw I can't make it with TABLE.MOVE.

So I make an export-import using brtools.

The point is, for a 700GB of table that will be reduced to 0,2GB after the reorg (exp-imp) is lasting 6 hours. Is this normal??

For a 200MB of information, taking so much time to export it?

Regards,

Filipe Vasconcelos

[Former Member]

Why do an export/Import.

Use brtools and do a longtolob conversion, this will enable your to do an online reorg...

Mark

[volker_borowski2]

Why do an export/Import.
> 
> Use brtools and do a longtolob conversion, this will enable your to do an online reorg...
> 
> Mark

I would do the same. This will not save you the FTS I think, so it will take some time.

If your metal has enough horse powers, you can go parallel, either in the simple exp select

or by using expdp or in the long2lob.

What might help singlethreaded, is to do a coalesce of the PK first (works online and can use PQ).

Calc stats on the table and PK then.

Then do the reorg by LONG2LOB (non PQ), and give option to order by PK in brspace.

The hope is, that it wil do the order by using the index and not going FTS.

(Not to say for sure it will, depends on how attractive the stats of the coalsced index are compared to the table)

If it does choose the index, 200MB should be fine, even if read indexwise and singlethreaded (Watch in ST04).

If it goes FTS and SORT to temp, you it will take as long as before (because of FTS on 700GB).

You might try to first "shrink space" and second "rebuild" the index then before

(can also be done using parallel execution).

This is more costy than the coalesce, but will significantly shrink the index.

So with new stats afterwards it might turn over the FTS when a sort is requested.

700 GB is 91+ mio blocks.

200 MB is 25.000 blocks (expect the index to be smaller)

So as long as you have less than 50 mio rows left, with fresh stats AND a physically small index,

that is known to the CBO there should be a chance to turn over the plan.

Good luck

Volker

[Former Member]

Hello,

It seems that you didn't understand my question. The table was 700GB but I have deleted most of the table entries. Now I want to perform a reorg, to reduce table size. My question is... why does it take 6hours for an export-import of a few records?

regards,

Filipe Vasconcelos

[former_member188883]

Hi ,

When an export is performed, internally a sql select statement runs for fecthing the data and converting into a dump file.

Since your table was fragmented over 700 GB, fetching these records will take time due to large size.

This is the reason it takes more time.

Alternatively you can go with R3load option to export such large tables. In this you can use table splitting option.

You can split such large tables into multiple chunks of smaller size. You can run these jobs in parallel and get the export created in lesser time.

Hope this is useful.

Regards,

Deepak Kori

[former_member188883]

Hi,

How to do table reorg of table BALDAT:

===============================

BALDAT contains application log data which keeps on growing if not controlled through regular housekeeping.

You can schedule the application log deletion program using tcode SLG1. Select all the check boxes and specify timeframe for deleting the application log files.

Select small intervals and schedule the job in background.

Once the old entries are deleted, size of table BALDAT will automatically get reduced.

You may not be required to perform exp/import.

Incase you go with export/import after following above steps, time taken will be pretty less since we have already deleted many of the old entries from the baldat.

Hope this is useful.

Regards,

Deepak Kori

[Former Member]

Hi,

>> The point is, for a 700GB of table that will be reduced to 0,2GB after the reorg (exp-imp) is lasting 6 hours. Is this normal??

For a 200MB of information, taking so much time to export it?

Could it be archived previously? Did you checked previous free space on the table? It looks like you have large freespace on the table. After the reorg, the allocated space is decreased.

You can check the history of the table, on DB02

Best regards,

Orkun Gedik