reorganizing tables and indexes

[beksalur]

how can i reorganize the data of following tables and indexes into one big extend in Oracle.can i do it with export/import?

[Segments with > 100 extents] Exec Time 8 seconds

! TABLE BAAN.TTIBOM903600: 206 Extents

! TABLE BAAN.TTICPR210500: 270 Extents

! TABLE BAAN.TTDINV021600: 123 Extents

! TABLE BAAN.TTDINV021400: 320 Extents

! TABLE BAAN.TTDINV021500: 304 Extents

! TABLE BAAN.TTICRP002400: 184 Extents

! TABLE BAAN.TTICRP003400: 173 Extents

! TABLE BAAN.TTICRP001400: 171 Extents

! INDEX BAAN.TTIBOM903600$IDX1: 427 Extents

! INDEX BAAN.TTICPR210500$IDX1: 484 Extents

! INDEX BAAN.TTICPR210500$IDX2: 505 Extents

! INDEX BAAN.TTICPR210600$IDX2: 147 Extents

 

[walkerci]

beksalur,

There are two tasks here:

1. Calculate the smallest extent that will hold the entire object.

SELECT SEGMENT_NAME, SUM(BYTES)/(1024*1024) MB
FROM USER_EXTENTS
GROUP BY SEGMENT_NAME
HAVING COUNT(*) > 100;

2. ALTER TABLE / ALTER INDEX to "move" / rebuild the objects with the desired extent size. This will allow you to do the reorganization in place without shutting down the DBMS. You can "move" tables to the same tablespace that they are currently in with new initial and next extent values.

This assumes that you have enough free space in the tablespaces for a copy of the largest table or index.

Also, when you reorganize a table (move), all of the indexes on that table become invalid, so rebuild the indexes immediately after moving the tables.

Suggestions:

a. Try to use extent sizes that are a power of 2.
b. Consider using a 1-3 different extent sizes instead of a different size for each object.
c. You really don't need to have one extent per object.
I can understand wanting a minimal number. You should not see any measurable difference between 1 and 10 extents for example.

There is a lot of debate about whether the number of extents impacts performance. My experience with Data Warehousing indicates that there are measurable differences in performance with small numbers of extents versus hundreds of extents, particularly with large numbers of inserts, index builds, and table scans.
Conversely, I would not expect OLTP databases to benefit from a single extent vs a hundred or so, given the random I/O patterns.

Hope this helps,

Charles

 

[SantaMufasa]

Beksalur, here is an additional thought on reorganizing your objects: Consider re-creating your target tablespace with the create clause:
"...extent management local autoallocate;"
If you use that option, then Oracle takes care of extent management for you, using the optimal algorithm for extent allocation available.

I also echo Charles's observation (also given Oracle's Performance guru, Cary Milsap's multiple treatises regarding lack of performance impact from numbers of extents) that numbers of extents show not worry you.

Cheers,

Dave