Re: decreasing initial extent

"Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> schreef in bericht news:3e241820$0$240$ed9e5944_at_reading.news.pipex.net...
> "Peter van Rijn" <p.vanrijnREMOVE_at_THISzhew.nl> wrote in message
> news:v28247a4eclb76_at_corp.supernews.com...
> > Niall, this will also work if the table is not empty:
> >
> > SQL> create table a(a1 number)
> > storage(initial 1m);
> >
> > Table created.
> >
> > SQL>begin
> > 2 for i in 1..1000 loop
> > 3 insert into a values(i);
> > 4 end loop;
> > 5 end;
> > /
> >
> > PL/SQL procedure successfully completed.
> >
> > SQL> select table_name, initial_extent from user_tables where
> > table_name='A';
> >
> > TABLE_NAME INITIAL_EXTENT
> > ------------------------------ --------------
> > A 1048576
> >
> >
> > SQL> alter table a move
> > storage(initial 500k);
> >
> > Table altered.
> >
> > SQL>
> > select table_name, initial_extent from user_tables where table_name='A';
> >
> > TABLE_NAME INITIAL_EXTENT
> > ------------------------------ --------------
> > A 516096
> >
> >
> > regards,
> > Peter
> >
>
> What I was on about was the compress parameter of the export command.
>
> COMPRESS
> Default: y
>
> Specifies how Export and Import manage the initial extent for table data.
>
> The default, COMPRESS=y, causes Export to flag table data for
consolidation
> into one initial extent upon Import. If extent sizes are large (for
example,
> because of the PCTINCREASE parameter), the allocated space will be larger
> than the space required to hold the data.
>
> So if you have 2gb of data in the table compress should specify that
import
> gets a create table with an initial extent of 2gb.
>
>

Yes, that is true of course. I was always a bit amazed about the COMPRESS default being yes. In the 'old days' when we were supposed to have as few extents as possible their could be some justification in this default, but these days it's absolutely nonsense.

But I in 9iR2 the default still is YES. Perhaps we should file an enhancement request for 10i!!

Peter Received on Tue Jan 14 2003 - 08:14:41 CST