Search My Oracle Blog

Custom Search

19 April, 2016

Partition Storage -- 1 : Default Partition Sizes in 12c

11g 11.2.0.2 introduced a change whereby the default Initial Extent of a Table Partition was 8MB.  However, this did not apply to Index Partitions which could still start with 64KB extents in an AutoAllocate Tablespace.

12cR1 now introduces a parameter to enable large Initial Extent for Index Partitions as well.

SQL> connect / as sysdba                          
Connected.
SQL> select banner from v$version where banner like 'Oracle Database%';

BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

SQL> show parameter deferred

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
deferred_segment_creation            boolean     TRUE
SQL> 
SQL> connect hemant/hemant
Connected.
SQL> create table my_part_tbl (id_column number(6), data_column varchar2(100))
  2  partition by range(id_column)
  3  (partition p_100 values less than (101),
  4   partition p_200 values less than (201),
  5   partition p_300 values less than (301),
  6   partition p_400 values less than (401),
  7   partition p_max values less than (maxvalue))
  8  /

Table created.

SQL> create index my_part_tbl_ndx on my_part_tbl(id_column) local;

Index created.

SQL> 
SQL> select segment_name, partition_name, segment_type, bytes/1024
  2  from user_segments
  3  where segment_name like 'MY_PART_%'
  4  order by 1,2;

no rows selected

SQL> 
SQL> insert into my_part_tbl values (51,'Fifty One');

1 row created.

SQL> insert into my_part_tbl values (151,'One Hundred Fifty One');

1 row created.

SQL> insert into my_part_tbl values (251, 'Two Hundred Fifty One');

1 row created.

SQL> select segment_name, partition_name, segment_type, bytes/1024
  2  from user_segments
  3  where segment_name like 'MY_PART_%'
  4  order by 1,2;

SEGMENT_NAME                   PARTITION_NA SEGMENT_TYPE       BYTES/1024
------------------------------ ------------ ------------------ ----------
MY_PART_TBL                    P_100        TABLE PARTITION          8192
MY_PART_TBL                    P_200        TABLE PARTITION          8192
MY_PART_TBL                    P_300        TABLE PARTITION          8192
MY_PART_TBL_NDX                P_100        INDEX PARTITION            64
MY_PART_TBL_NDX                P_200        INDEX PARTITION            64
MY_PART_TBL_NDX                P_300        INDEX PARTITION            64

6 rows selected.

SQL> 


I can enable large Index Partition Extent with a parameter (which can be set with ALTER SESSION)


SQL> alter session set "_index_partition_large_extents"=TRUE;

Session altered.

SQL> insert into my_part_tbl values (351,'Three Hundred Fifty One');

1 row created.

SQL> select segment_name, partition_name, segment_type, bytes/1024
  2  from user_segments
  3  where segment_name like 'MY_PART_%'
  4  order by 1,2;

SEGMENT_NAME                   PARTITION_NA SEGMENT_TYPE       BYTES/1024
------------------------------ ------------ ------------------ ----------
MY_PART_TBL                    P_100        TABLE PARTITION          8192
MY_PART_TBL                    P_200        TABLE PARTITION          8192
MY_PART_TBL                    P_300        TABLE PARTITION          8192
MY_PART_TBL                    P_400        TABLE PARTITION          8192
MY_PART_TBL_NDX                P_100        INDEX PARTITION            64
MY_PART_TBL_NDX                P_200        INDEX PARTITION            64
MY_PART_TBL_NDX                P_300        INDEX PARTITION            64
MY_PART_TBL_NDX                P_400        INDEX PARTITION          8192

8 rows selected.

SQL> 


However, I can rebuild the Index Partition Extent as well :

SQL> alter index my_part_tbl_ndx rebuild partition p_400 storage (initial 64K);

Index altered.

SQL> select segment_name, partition_name, segment_type, bytes/1024
  2  from user_segments
  3  where segment_name like 'MY_PART_%'
  4  order by 1,2;

SEGMENT_NAME                   PARTITION_NA SEGMENT_TYPE       BYTES/1024
------------------------------ ------------ ------------------ ----------
MY_PART_TBL                    P_100        TABLE PARTITION          8192
MY_PART_TBL                    P_200        TABLE PARTITION          8192
MY_PART_TBL                    P_300        TABLE PARTITION          8192
MY_PART_TBL                    P_400        TABLE PARTITION          8192
MY_PART_TBL_NDX                P_100        INDEX PARTITION            64
MY_PART_TBL_NDX                P_200        INDEX PARTITION            64
MY_PART_TBL_NDX                P_300        INDEX PARTITION            64
MY_PART_TBL_NDX                P_400        INDEX PARTITION            64

8 rows selected.

SQL> 


In the next post, we'll see more Extents for the Partitions.
,
,
,

1 comment:

Foued said...

Thanks Hemant for sharing this post.
Foued

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