Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: table reorganizations

RE: table reorganizations

From: Bobak, Mark <>
Date: Wed, 07 Jan 2004 11:59:34 -0800
Message-ID: <>


When I first saw your posting, I considered if it was intended as bait....;-)
You'll have to forgive me for thinking so, as these are popular topics of contention.
Now, for the forthright (and hopefully bait-free) answer:
Please define what you mean by "re-organization".  If you're on at least 8i and locally-managed tablespaces, your tables should never need to be re-organized.  Dozens or even hundreds of extents are not likely to be a problem.  The short answer:  In general, don't worry about re-organizing your tables.  Use locally managed tablespaces with uniform extent sizes.  Pick a few different uniform extent sizes.  When a table really grows large, move to a tablespace with a larger uniform size.  See the "How to Stop Defragmenting and Start Living" paper, available on MetaLink.  (If you don't have MetaLink access, I think you can find it via Google search as well.)
Full table scans:  Don't worry about which tables have full table scans going on.  Worry instead about your critical business processes, and how they are performing.  Do you see one that's not performing up to par?  Investigate that.  You may (or may not) find slow performance caused by an inappropriate full table scan.  It could also be caused by inefficient index range scan, where replacing it with a full table scan is appropriate.  But, the point is, by concentrating on the slowest of the critical business processes, you're most likely to find yourself focusing on the most important pieces of your system which simultaneously have the most room for improvement.
Hope that helps,

Mark J. Bobak
Oracle DBA
ProQuest Company
Ann Arbor, MI
"Imagination was given to man to compensate him for what he is not, and a sense of humor was provided to console him for what he is."  --Unknown

-----Original Message-----
From: Shrake, Jolene []
Sent: Wednesday, January 07, 2004 2:39 PM
To: Multiple recipients of list ORACLE-L
Subject: table reorganizations

What SQL statement do you use to identify tables that need reorganization?
How do you identify tables that are used in full table scans?  How often do you run this query?
Please see the official ORACLE-L FAQ:
Author: Bobak, Mark

Fat City Network Services    -- 858-538-5051
San Diego, California        -- Mailing list and web hosting services
To REMOVE yourself from this mailing list, send an E-Mail message
to: (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Wed Jan 07 2004 - 13:59:34 CST

Original text of this message