Re: Reducing the number of table extents

jts_at_romulus.ncsc.mil (Jamie T. Sutton) writes:

>1. Why can't I create an extent larger than a datafile's size?

You probably should check out the Concepts manual, which describes what an extent is. Essentially, it is a _contiguous_ block of space which is set aside for storing data for a table. As such, it must be in just one datafile. (I suppose it would be technically possible for an extent to span datafiles, but that'd be a real bear to program across multiple system types, and limiting it to one datafile is probably a simplification.)

>2. How can I combine all of these extents into one extent or a
> few extents? Will I have to dump the whole table onto tape,
> disk, etc. and reload the table? Or allocate space for
> a temp. table, copy all the rows from the orig. table, drop
> the original table, and then reload the orig. table? Or is
> there some util. I can use to do this?

Well, there are several commercial DBA tools that claim to be able to do things like this, but I haven't tried any. Copying to a temporary table and then back is one strategy, providing you have LOTS of disk space to devote to the task. (If you are running in archivelog mode, both copies will generate astronomical amounts of log files.) If you do this, create a new tablespace for the temporary table, it makes dropping it afterwards easier.

Another strategy is to export the table and import it. If the export file would be larger than one filesystem can support you're probably limited to exports to tape.

There was a presentation at IOUW'94 which basically said that having a lot of extents doesn't present much of a performance problem, especially if the extents are all large. (This seems to contradict statements in the Oracle manuals, though.) The biggest problem I see is that if your table is growing you will eventually hit the limit on extents in a table. (This is operating system dependent, but is probably around 128.) However, on such a large table the default PCTFREE and PCTUSED values may not be optimal, which could be sufficient reason for the reorg.

---
Michael Nolan, Sysop for the DBMS RoundTable on GEnie
nolan_at_notes.tssi.com, dbms_at_genie.geis.com
(posted from nolan_at_helios.unl.edu)