Kenneth Koenraadt wrote:
> On Wed, 06 Oct 2004 17:06:12 +1000, "Howard J. Rogers"
> <howardjr_at_dizwell.com> wrote:
>
>>Holger Baer wrote:
>>
>>> John Wood wrote:
>>>> We have Oracle 9i on Win2K environment. We have created some very big
>>>> tables in a user tablespace. As now we found the tables are not used
>>>> much.
>>>> It contains much less data. I would like to rebuild the table using
>>>> Exp/Imp. But when I export the tables, the export file would contain a
>>>> statement to create the table with big initial extent.
>>>>
>>>> If I export the tables with COMPRESS=N, then I think I can prevent the
>>>> tables from creating with large initial extent. I have enabled the
>>>> Local
>>>> Managed Tablespace. The question is: Is it okay to do so as the Local
>>>> Managed Tablespace would allocate 64K at a time ? The tables would
>>>> probably
>>>> involve about 200M to 500M of data. Will there be any performance
>>>> issue ?
>>>>
>>>> Thanks.
>>>>
>>>> JW.
>>>>
>>>>
>>>
>>> Not withstanding HJR's advice, why don't you just 'alter table move ...'
>>> ?
>>
>>
>>Good suggestion, btw. And it's a bit quibbling of me to mention that
>>there's only one one possible downer with it: moving a table requires
>>potentially twice the amount of Oracle-allocated disk space as the single
>>table uses. Whereas it would be possible to export to some disk that is
>>not otherwise used by Oracle.
>>
>>Oh, and OK... a second downer: moving a table causes a hell of a lot of
>>buffer cache activity. Export doesn't, in direct path mode anyway.
>>
>>Oh, and a third possible: moving a table requires that you *remember* to
>>rebuild all your indexes. Importing one does it for you, by default.
>>
>>Dull it may be, but export still has its uses!!
>>
>>:-)
>>HJR
>>
>>
>
> He could do the following :
>
> create table copy_of_foo as (select * from foo);
> -- could be done "nologging" to save (archive) logs.
> truncate table foo;
> -- preserves the indexes in usable state
> insert into foo (select * from copy_of_foo);
>
> All indexes OK, perfectly reorganised table.
>
> - Kenneth Koenraadt
Well, he *could* do lots of things!
Why not "insert /* +APPEND */ into foo select * from copy_foo"?
Point is, you've just performed two full tablescans, and at least one of
them has clobbered your buffer cache. And export is therefore at least
still in the running.
There are no cheap table re-organisations. Move isn't one; export/import
isn't one; CTAS isn't one. There are just degrees of pain.
Regards
HJR
Received on Fri Oct 08 2004 - 00:46:16 CDT
