Re: When should I use COMPRESS=Y on export?

Hi Carmen,

Imagine you have an EMP table that comes in extent sizes of 100K. When it was created, it acquired an initial extent of 100K. As you inserted new records, it acquired a second and then a third extent. You now have a 300K-big EMP table, made up of 3 100K extents.

Now say you start losing employees -so you start deleting records left, right and centre out of EMP. This doesn't make EMP get any smaller, of course, because it still has its three extents. What it *does* mean is that most of those extents are now empty of real data.

Now, if you do an export where COMPRESS=Y, then on subsequent import, what will happen is that a new EMP table will be created *with a 300K Initial extent*. In other words, the initial extents of all segments are set to be equal in size to the *total* size of all extents current acquired by a segment. Next extent sizes are not affected, by the way. You end up with a 300K EMP table, in one extent -so you've saved yourself precisely zero space. (If you'd used compress=n, by the way, you probably *would* have saved space: import would have created as many extents are needed to store the data about to be imported... and in our case, given all the deleted data, we might only have needed an initial 100K. You therefore have the slightly counter-intuitive situation where COMPRESS=Y will NOT save you any space, but COMPRESS=N actually will!)

Now this behaviour might be a good thing or a bad thing. It all depends. Remember we did all those deletes on the EMP table, so most of its 3 extents were sitting there empty? Well, now we've just created a single 300K extent, most of which is empty. If we were never going to do fresh inserts into EMP, this would be a waste of space. So, compression is bad. On the other hand, if we know we are about to do lots of fresh inserts into the table, having all that space pre-allocated to the segment will make the inserts go faster. So, compression is good. If EMP is sitting there with a 300K initial extent, and we do inserts such that a new NEXT extent is required, that new extent will revert to being 100K. So we have odd-sized extents in the tablespace, which will one day lead to fragmentation. So compression is bad. Except that this is an Oracle 8 or 8i database, with MINIMUM EXTENT or locally managed tablespaces, so the extent sizes that export wants to create are totally ignored. So compression is OK again.

You get the idea. It's a tool to be used when appropriate. The books will tell you to watch out for it when a table has been subject to lots of deletes, because of the '300K most of it empty space' issue. But that might be just what you want, so my advice is to understand what it does, and to use it accordingly.

Where it is extremely useful is in fixing up the problem of a segment having hundreds or thousands of extents because you didn't manage to size it correctly in the first place: what you would really like is 1000 10K extents to turn into a single 10M extent -and that's exactly what compress will do for you. And in that sense, it can fix up problems of past dynamic extension gone haywire.

You are correct in saying that using compress=y might mean the thing is totally unimportable -in that last example, if I need to create a single 10M extent, and I have only got 3M in one part of the tablespace, 4M in another, and a final 3M at the end, then I won't be able to create a single 10M extent, and the import will fail. That's what tablespace fragmentation is all about, and if you plan your tablespaces properly, and use consistent extent sizes, it won't happen. Failure to import is, in other words, a symptom of a much deeper problem, not an issue with using COMPRESS=Y per se.

In any event, the issue is totally redundant for you because you have already re-created your tables, and are simply pulling in the data, using ignore=y. The extent szes you get and so on are determined by what you've done to the tables already in the database, so compress=y does nothing at all. I might add that if you are using export and import to fix up chaining, I can't think why you simply don't drop the tables in the database and re-create them via an import -at which point the COMPRESS option *will* become significant.

As for compatibility, the rule is that the higher version executable can NOT be applied to a lower version database, but that a lower version executable CAN be applied to a higher version database. Hence, an 8.0 version of export can be used against an 8i database, and an 8.0-compatible export dump file will be produced (tough luck if the database uses any of the new 8i features, of course). But if you use 8i export to grab stuff out of the 8.0 database, you'll be stuffed. Probably.

Regards
HJR

--
---------------------------------------------------------------------------
Opinions expressed are my own, and not those of Oracle Corporation
Oracle DBA Resources:               http://www.geocities.com/howardjr2000
---------------------------------------------------------------------------

"Carmen Iannacone" <carmen_at_fabgear.com> wrote in message
news:3a09e6f3.1306661950_at_news.flashcom.net...


> I'm trying to migrate data from one database to another.

>

> I know that I have some chaining going on, so I need

> to recreate the tables via a script, and then import with

> IGNORE=Y. The question I have is whether the exports

> that I'm doing should be done with COMPRESS=Y.

>

> I'll be honest, I'm a little confused about that option; I

> thought I read somewhere that it may lead to a file that's

> not importable.

>

> But, I want to improve my physical layout and reduce

> some dynamic extension.

>

> Is this the time to use COMPRESS=Y, or should I

> in fact ALWAYS be using COMPRESS=Y?

>

> Also, if you've read this far, how can I guarantee that

> the versions of EXP and IMP are compatible. They're

> both in the 8.* family...

>

> Thanks for any help!

>

> Carmen

> (carmen_at_fabgear.com)