Extent Numbers (Was Re: Interview question)

I have a related question, although this is mostly with my experience with Oracle 6.0.3.

Back when we were using Oracle 6.0, we would run into problems of running out of space and needing to optimize. Since Oracle 6 didn't have abilities to shrink things, and to be honest, we didn't know what we were doing, we would export the entire dataspace, delete the entire database, recreate the entire table and re-import. That seemed to be the only way to clean up the database.

We found out at that time that "Compress" was a VERY BAD THING. If you took one of our heavily used tables (approximatly 40% of our database) and compressed the table, it went from something like 25-60 extents, depending on system, to 2. Our ability to process data was cut in a quarter because database access with the table was just SO SLOW..... From that point on, Compress has been a four letter word around here.

We never did find out what compress did that killed our database I/O, but it was definitely something drastic. In my opinion, that means that having few extents is not a good thing (with the exception of some fixed-size tables that we set the size handle all the data.)

Has that changed in the last 11 years? I hear people on both sides of the "you want to have few extents" but no real "answers". Do you want to compress when you export/import?

Terry Steyaert
steyaert_at_my-deja.com

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Tue Jul 25 2000 - 00:00:00 CDT