When is reorg required

Active 2 years, 10 months ago. Viewed 2k times. Improve this question. David Copping David Copping 11 2 2 bronze badges. Add a comment. Active Oldest Votes. Improve this answer. And consider storing your LOBs in the long tablespace, rather than interleaved with table data in the main tablespace.

Greg Marino Greg Marino 11 2 2 bronze badges. See pic. A load utility void of a pre- processing step that sorts data before loading. No Account? Sign up. By signing in, you agree to our Terms of Use and Privacy Policy. Already have an account? Sign in. By signing up, you agree to our Terms of Use and Privacy Policy. Enter the email address associated with your account. We'll send a magic link to your inbox.

Email Address. All Sign in options. Enter a Email Address. Choose your interests Get the latest news, expert insights and market research, sent straight to your inbox. Newsletter Topics Select minimum 1 topic. Data Management. Tags: IBM Db2. The data is replaced each night. Alexander Ashkenazi Posted November 15, 0 Comments. Anonymous Posted November 15, 0 Comments. Alex explained everything very clearly. Regards, Angelo. John Agnew Posted November 15, 0 Comments.

The following is from the 9. In my gut it seemed like overkill to do reorgs here and everyone that has posted validates this. Alexander Ashkenazi Posted November 16, 0 Comments. Beard, This discussion could serve as a good illustration of how bringing elements being just slightly above being trivial, can derail a conversation. You can start the Reorg wizard in context to an object by selecting an entire tablespace or by selecting a specific database object.

In order to launch the Reorg Wizard, the database and node preferred credentials must be set. The Reorg Wizard leads you through the process of reorganizing objects within a database.

As part of the process you will be able to select the objects targeted for reorganization, optionally change the storage setting and location of these selected objects, and then perform the reorganization immediately or schedule it to be performed at later time. If you need assistance while using the wizard, select the Help button from any of the pages to detailed information. The reorganization option allows you to chose the type of reorganization to perform. There are three reorganization options: reorganize specific schema objects, reorganize an entire tablespace, or repair migrated rows.

Note that this step will only appear when you launch the Reorg Wizard without first selecting an object to reorganize. A list of available objects is displayed. The wizard allows you to select the objects you wish to reorganize. When reorganizing schema objects, it is possible to change the object location and sizing information for the individual selected objects not only to correct existing space usage problems, but also to prevent future problems.

One way to enhance availability and manageability is to allow users full access to the database during a data reorganization operation. Depending on the server version, Oracle supports online reorganizations for various types of objects. Oracle 9i improves on this with new features that support online creation and rebuilding of even more types of indexes as well as support for online reorganization for some types of tables.

Even though online reorganizations provide higher availability, they are slower. Then the table space gets more and more fragmented. Over time such small fragments will affect the performance of the database, unless a REORG is executed. But this is normally the job of DBAs who are constantly monitoring the status of the database. After a table space reorganization the fragments are moved around and clustered, however the database still utilizes the old statistic to query the data.