Tom, if I create a table with the following characteristics in its partitions:
COMPRESS BASIC
PCTFREE 0
STORAGE (
INITIAL 7748954362 --7.2 GB
NEXT 1048576 --1 MB
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0),The INITIAL that calculated gave me 7 GB but with the COMPRESS should I set a lower INITIAL?
I think it allocates 7GB but actually uses less because it is compressed. Is that so?
They are big tables so I want to occupy as little as possible on disk and not generate many extents
You're on 12c, so by default when you create the table it won't allocate anything:
set feed on
create table t#init128k (
x int
) storage ( initial 128K ) ;
create table t#init1M (
x int
) storage ( initial 1M ) ;
create table t#init1G (
x int
) storage ( initial 1G ) ;
select segment_name , bytes / 1024 size_in_kb
from user_segments
where segment_name like 'T#%';
0 rows selected
This is due to deferred segment creation.
As soon as you insert some data, Oracle Database will create the segment. At this point it will allocate the space as you say:
insert into t#init128K values (1);
insert into t#init1M values (1);
insert into t#init1G values (1);
select segment_name , bytes / 1024 size_in_kb
from user_segments
where segment_name like 'T#%';
SEGMENT_NAME SIZE_IN_KB
T#INIT128K 128
T#INIT1G 1,048,576
T#INIT1M 1,024
So at this point we've got three segments which only have a couple of bytes in. i.e. they're mostly empty. How much Oracle Database uses depends upon how many rows you insert and how big they are. And if you're using compress and how compressible they are.
Unless you're going to load 7Gb immediately after creating the table there's no point allocating that much because the table will be that big "one day".
There's no reason to "not generate many extents". Having lots isn't going to make any performance difference:
https://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:730289259844