Oddity with NEXT_EXTENT Behavior

From: Ed Stevens <Ed.Stevens_at_nmm.nissan-usa.com>
Date: Tue, 22 Feb 2000 16:46:06 GMT
Message-ID: <88uegc$5nm$1@nnrp1.deja.com>

A co-worker and I had some disagreements on how INITIAL, NEXT, and PCTINCREASE interacted in allocating space for a table. So I set up a test and the results are puzzling to both. Anyone want to explain this?

Running 8.0.5 on NT 4.0, my test script looks like this:

create table my_test (lname char(10)) storage (initial 100k

           next 101k
           pctincrease 50);

select extent_id, bytes from dba_extents where segment_name = 'MY_TEST'; select next_extent from dba_tables where table_name = 'MY_TEST';

alter table my_test allocate extent;
select extent_id, bytes from dba_extents where segment_name = 'MY_TEST'; select next_extent from dba_tables where table_name = 'MY_TEST';

(repeat the ALTER/SELECT/SELECT several times)

Immediately after the CREATE TABLE I expected extent 0 to be 102400, and NEXT_EXTENT to be 106496 (101k rounded to the next 4k boundary). This is exactly what happened; so far so good.

Now, after succeeding ALTER … ALLOCATE EXTENT commands, I expected each succeeding value of NEXT_EXTENT to be the previous value multiplied by 1.5 and (if necessary) rounded up to the next 4k boundary. Thus the progression of NEXT_EXTENT should be

106496
159744
241664
364544
548864
823296
1236992

Which is exactly what happened. However, I also expected the number of BYTES in each additional extent to be the value of NEXT_EXTENT when the extent was allocated. However, the actual values (select extent_id, bytes from dba_extents . . .) were:

EXTENT_ID BYTES
--------- ---------

        0    102400
        1    122880  (expected 106496)
        2    159744
        3    245760  (expected 241664)
        4    364544
        5    548864
        6    823296
        7   1236992
        8   1236992
        9   1236992

You can see, extents 1 and 3 were not as expected, based on the values of NEXT_EXTENT when they were allocated. Any explanations?

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Tue Feb 22 2000 - 10:46:06 CST