RE: SAP and TSReorg

Sorry for the late reply; just catching up on 3000+ Oracle-L messages. :-)

I used TSReorg and LiveReorg at previous clients. We typically rebuilt indexes; our DBA had an algorithm he'd developed for determining which ones needed to be rebuilt. The only tables we really reorganized were ones where a significant reduction in data had taken place. In many cases, we didn't even do it then; the logic was that we'd just freed up 20G of white space in some tablespace by archiving last year's data, but we'd eat at least that much up this year -- why "consolidate" it when Oracle will use the existing white space just fine?

The one thing we really did a lot of reorging for (nighly in some cases) was to re-order data in a table. Specifically, at a large manufacturer we were running MRP in R/3 3.x with huge planning horizons, and doing availability checking on nearly every one of the over 400,000 parts in our BOM's. What this means is that if a part we're scheduled to build 16 months from now requires 7 of a specific flat washer, we're going to "reserve" them right now. So every work order that's released has to go look at the part reservations to make sure that, given the current inventory and the current parts reservations, we have enough to build whatever it is we're trying to release onto the shop floor.

Based on that, our "Reservation" table was CONSTANTLY being scanned by material number, but that's not very high up in the primary key. By re-building the table in the order of the index keyed on material number, we signifigantly improved performance on that table. This was back in the Oracle 7/8 days -- there are WAY nicer ways to do it these days. :-)

Feel free to contact me off list if you want any more details, etc.

Rich

--
Rich Holland        (913) 645-1950        SAP Technical Consultant
print unpack("u","92G5S\=\"!A;F]T:&5R(\'!E<FP\@:&%C:V5R\"\@\`\`");



> -----Original Message-----

> From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On

> Behalf Of dhotka_at_earthlink.net

> Sent: Monday, June 21, 2004 7:46 AM

> To: oracle-l_at_freelists.org

> Subject: SAP and TSReorg

> 

[...]


>

> I would like to hear from any of you who are using TSReorg to perform

> reorgs in a SAP/Oracle environment=2E  My client is asking for reorg

> strategies=2E=2E=2EI can advise them=2E=2E=2Ebut I'd certainly like to hea=

> r from anyone

> specifically using TSReorg in a SAP/Oracle database environment=2E

> 

> Thank you in advance=2E=2E=2E

> 

> Dan Hotka



----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------