Search My Oracle Blog

Custom Search

09 March, 2013

Segment Size of a Partition (11.2.0.2 and above)

In 11.2.0.2, Oracle introduced a change to the default size of Partition Segment.
According to Support Note "Initial Extent Size of a Partition Changed To 8MB From 64KB After Upgrade to 11.2.0.2 [ID 1295484.1]",  the INITIAL Extent of a Partition is now 8MB.  Furthermore, Deferred Segment Creation also applies to Partitions.

Thus, when you initially create a Partitioned Table, (and DEFERRED_SEGMENT_CREATION is left at the default value of TRUE), the Partitions are segmentless.  As soon as you populate a Partition, it is created as a Segment with an INITIAL of 8MB and NEXT of 1MB.

Here is a simple demo :


SQL*Plus: Release 11.2.0.2.0 Production on Sat Mar 9 16:45:49 2013

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Enter user-name: hemant/hemant
ERROR:
ORA-28002: the password will expire within 7 days



Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> 
SQL> create tablespace PARTITION_TEST ;      

Tablespace created.

SQL> 
SQL> select extent_management, allocation_type, segment_space_management,
  2  initial_extent, next_extent, pct_increase
  3  from dba_tablespaces
  4  where tablespace_name = 'PARTITION_TEST'
  5  /

EXTENT_MAN ALLOCATIO SEGMEN INITIAL_EXTENT NEXT_EXTENT PCT_INCREASE
---------- --------- ------ -------------- ----------- ------------
LOCAL      SYSTEM    AUTO            65536

SQL> 
SQL> l
  1  create table a_partitioned_table
  2  (id   number not null,
  3  data_column varchar2(20)
  4  )
  5  partition by range (id)
  6  (
  7  partition P_ID_100 values less than ('101') tablespace PARTITION_TEST,
  8  partition P_ID_200 values less than ('201') tablespace PARTITION_TEST,
  9  partition P_MAX values less than (MAXVALUE) tablespace PARTITION_TEST
 10* )
SQL> /

Table created.

SQL> 
SQL> set pages600
SQL> set long 32767
SQL> select dbms_metadata.get_ddl('TABLE','A_PARTITIONED_TABLE','HEMANT') from dual;

DBMS_METADATA.GET_DDL('TABLE','A_PARTITIONED_TABLE','HEMANT')
--------------------------------------------------------------------------------

  CREATE TABLE "HEMANT"."A_PARTITIONED_TABLE"
   (    "ID" NUMBER NOT NULL ENABLE,
        "DATA_COLUMN" VARCHAR2(20)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  STORAGE(
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"
  PARTITION BY RANGE ("ID")
 (PARTITION "P_ID_100"  VALUES LESS THAN ('101') SEGMENT CREATION DEFERRED
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "PARTITION_TEST" ,
 PARTITION "P_ID_200"  VALUES LESS THAN ('201') SEGMENT CREATION DEFERRED
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "PARTITION_TEST" ,
 PARTITION "P_MAX"  VALUES LESS THAN (MAXVALUE) SEGMENT CREATION DEFERRED
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "PARTITION_TEST" )


SQL> 

Note how the two Partitions are created with "SEGMENT CREATION DEFERRED" and there is no Storage allocated to them yet. Let's look for the Segments and Extents.

SQL> select segment_name, partition_name, blocks, bytes/1024
  2  from dba_segments
  3  where tablespace_name = 'PARTITION_TEST';

no rows selected

SQL> select segment_name, partition_name, extent_id, file_id, block_id, blocks
  2  from dba_extents
  3  where tablespace_name = 'PARTITION_TEST';

no rows selected

SQL> select table_name, partition_name from user_tab_partitions;

TABLE_NAME                     PARTITION_NAME
------------------------------ ------------------------------
A_PARTITIONED_TABLE            P_MAX
A_PARTITIONED_TABLE            P_ID_200
A_PARTITIONED_TABLE            P_ID_100

SQL> 

So, although the three Partitions exist, no Segments and Extents yet exist. Now, let's populate one Partition -- just inserting a single row will suffice
.
SQL> insert into a_partitioned_table values (150,'HundredFifty');

1 row created.

SQL> commit;

Commit complete.

SQL> 

Now, let's look for the Segment and Extent. Note that Partition P_ID_200 would have the row with ID=150.

SQL> select segment_name, partition_name, blocks, bytes/1024
  2  from dba_segments
  3  where tablespace_name = 'PARTITION_TEST';

SEGMENT_NAME
--------------------------------------------------------------------------------
PARTITION_NAME                     BLOCKS BYTES/1024
------------------------------ ---------- ----------
A_PARTITIONED_TABLE
P_ID_200                             1024       8192


SQL> select segment_name, partition_name, extent_id, file_id, block_id, blocks
  2  from dba_extents
  3  where tablespace_name = 'PARTITION_TEST';

SEGMENT_NAME
--------------------------------------------------------------------------------
PARTITION_NAME                  EXTENT_ID    FILE_ID   BLOCK_ID     BLOCKS
------------------------------ ---------- ---------- ---------- ----------
A_PARTITIONED_TABLE
P_ID_200                                0          6        128       1024


SQL> 

So, Partition P_ID_200 now has an 8MB Segment with an Initial Extent of 8MB. Let's view the DDL for the Table.

SQL> select dbms_metadata.get_ddl('TABLE','A_PARTITIONED_TABLE','HEMANT') from dual;

DBMS_METADATA.GET_DDL('TABLE','A_PARTITIONED_TABLE','HEMANT')
--------------------------------------------------------------------------------

  CREATE TABLE "HEMANT"."A_PARTITIONED_TABLE"
   (    "ID" NUMBER NOT NULL ENABLE,
        "DATA_COLUMN" VARCHAR2(20)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  STORAGE(
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"
  PARTITION BY RANGE ("ID")
 (PARTITION "P_ID_100"  VALUES LESS THAN ('101') SEGMENT CREATION DEFERRED
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "PARTITION_TEST" ,
 PARTITION "P_ID_200"  VALUES LESS THAN ('201') SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 8388608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
FAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "PARTITION_TEST" ,
 PARTITION "P_MAX"  VALUES LESS THAN (MAXVALUE) SEGMENT CREATION DEFERRED
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "PARTITION_TEST" )


SQL> 

Note how Partitions P_ID_100 and P_MAX are unchanged but Partition P_ID_200 now has SEGMENT CREATION IMMEDIATE and STORAGE (INITIAL 8M NEXT 1M) !

What if I were to split the Partitions to create new Partitions ?

SQL> alter table A_PARTITIONED_TABLE split partition P_ID_100 at ('51')
  2  into (partition P_ID_50, partition P_ID_100);

Table altered.

SQL> alter table A_PARTITIONED_TABLE split partition P_ID_200 at ('151')
  2  into (partition P_ID_150, partition P_ID_200);

Table altered.

SQL> 
SQL> select dbms_metadata.get_ddl('TABLE','A_PARTITIONED_TABLE','HEMANT') from dual;

DBMS_METADATA.GET_DDL('TABLE','A_PARTITIONED_TABLE','HEMANT')
--------------------------------------------------------------------------------

  CREATE TABLE "HEMANT"."A_PARTITIONED_TABLE"
   (    "ID" NUMBER NOT NULL ENABLE,
        "DATA_COLUMN" VARCHAR2(20)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
  STORAGE(
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"
  PARTITION BY RANGE ("ID")
 (PARTITION "P_ID_50"  VALUES LESS THAN ('51') SEGMENT CREATION DEFERRED
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "PARTITION_TEST" ,
 PARTITION "P_ID_100"  VALUES LESS THAN ('101') SEGMENT CREATION DEFERRED
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "PARTITION_TEST" ,
 PARTITION "P_ID_150"  VALUES LESS THAN ('151') SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 8388608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
FAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "PARTITION_TEST" ,
 PARTITION "P_ID_200"  VALUES LESS THAN ('201') SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 8388608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
FAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "PARTITION_TEST" ,
 PARTITION "P_MAX"  VALUES LESS THAN (MAXVALUE) SEGMENT CREATION DEFERRED
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "PARTITION_TEST" )


SQL> 
SQL> select segment_name, partition_name, blocks, bytes/1024
  2  from dba_segments
  3  where tablespace_name = 'PARTITION_TEST';

SEGMENT_NAME
--------------------------------------------------------------------------------
PARTITION_NAME                     BLOCKS BYTES/1024
------------------------------ ---------- ----------
A_PARTITIONED_TABLE
P_ID_200                             1024       8192

A_PARTITIONED_TABLE
P_ID_150                             1024       8192


SQL> select segment_name, partition_name, extent_id, file_id, block_id, blocks
  2  from dba_extents
  3  where tablespace_name = 'PARTITION_TEST';

SEGMENT_NAME
--------------------------------------------------------------------------------
PARTITION_NAME                  EXTENT_ID    FILE_ID   BLOCK_ID     BLOCKS
------------------------------ ---------- ---------- ---------- ----------
A_PARTITIONED_TABLE
P_ID_150                                0          6        128       1024

A_PARTITIONED_TABLE
P_ID_200                                0          6       1152       1024


SQL> 
So, only the two Partitions that have been created out of P_ID_200 have been materialized as Segments and Extents while the two Partitions split out of P_ID_100 are still Segment-less (without Storage allocated).

So remember : the default behaviour of Segment creation for Partitions has been changed since 11.2.0.2
Having said that, the note that I mentinoed at the beginning does document how this default behaviour can be overridden.
You need this information when you are creating a table with, say, 125 partitions but have only 1 to a few thousand rows in each partition and have to explain why the table is taking up 1GB of space.
Also note that once a Partition has had an 8MB extent allocated to it, any new Partitions that you create out of splitting it will also hae 8MB extents allocated --even if they are empty !

.
.
.

No comments:

Aggregated by orafaq.com

Aggregated by orafaq.com
This blog is being aggregated by orafaq.com

Top 50 Oracle SQL Blogs 2016

Top 50 Oracle SQL Blogs 2016
Top 50 Oracle SQL Blogs 2016