Mohamed Houri’s Oracle Notes

June 7, 2018

12cR2 Partitioning: auto-list and the disk space threat

Filed under: Partitioning — hourim @ 5:55 pm

Oracle 12cR2 comes with the auto-list partitioning option; It allows for system named partitions to be created on the fly as data arrives:

SQL> select banner from v$version where rownum=1;

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

SQL> create table auto_list_part
  	( n1 number  
       ,n2 number 
       ,vc1 varchar2(30)
      )
     partition by list(n1)
     AUTOMATIC  
     (partition p1 values(1,2,3,4,5)
     ,partition p2 values(6,7,8,9,10)
     ,partition p3 values(11,12,13,14,15) 
      )
     /

You only need to add the AUTOMATIC clause to the old way of creating list partitioned tables to have this feature enabled.

There is, however, a subtlety to consider: the AUTOMATIC clause doesn’t work with the DEFAULT clause:

SQL> create table auto_list_part
  	( n1 number  
       ,n2 number 
       ,vc1 varchar2(30)
      )
     partition by list(n1)
     AUTOMATIC
     (partition p1 values(1,2,3,4,5)
     ,partition p2 values(6,7,8,9,10)
     ,partition p3 values(11,12,13,14,15) 
     ,partition p_default values (default)
      )
     /
create table auto_list_part
*
ERROR at line 1:
ORA-14851: DEFAULT [sub]partition cannot be specified for AUTOLIST [sub]partitioned objects.

This is perfectly coherent as you don’t want to ask Oracle creating automatic list partitions for each of your newly arrived partition key values and ask it in the same time to put them into the DEFAULT partition.

That’s said let’s insert into the above automatic list partitioned table, rows that will go both in the already pre-created partitions and rows that need to go into non-existent ones:

SQL> insert into auto_list_part
     select
          rownum
         ,mod(rownum,5)
         ,'v'
     from dual
     connect by level <= 20;

 20 rows created.

SQL> exec dbms_stats.gather_table_stats(user, 'auto_list_part');

PL/SQL procedure successfully completed.



SQL> @getPartTab
Enter value for table_name: auto_list_part

TABLE_NAME           PARTITION_NAME  PARTITION_POSITION   NUM_ROWS TABLESPACE_NAME
-------------------- --------------- ------------------ ---------- ---------------
AUTO_LIST_PART       P1                               1          5 USERS
AUTO_LIST_PART       P2                               2          5 USERS
AUTO_LIST_PART       P3                               3          5 USERS
AUTO_LIST_PART       SYS_P3054                        4          1 USERS
AUTO_LIST_PART       SYS_P3055                        5          1 USERS
AUTO_LIST_PART       SYS_P3056                        6          1 USERS
AUTO_LIST_PART       SYS_P3057                        7          1 USERS
AUTO_LIST_PART       SYS_P3058                        8          1 USERS
                                                        ----------
Total num_rows                                                  20

8 rows selected.

I inserted 20 rows of which 5 have been embedded into 5 new SYS-partitions created automatically to store not foreseen partition key values greater than 15. It is important to note as well that the AUTOMATIC clause creates one partition per partition key. This is why you should absolutely take care in basing your partition strategy upon a data load pattern that will insert a reasonable amount of rows per partition key.

Let’s now get the size of the partitioned table:

SQL> @SizeBySegNameMB
Enter value for segment_name: auto_list_part
Enter value for owner: c##mhouri

SEGMENT_TYPE       TABLESPACE_NAME  SEGMENT_NAME         PARTITION_NAME          MB
------------------ ---------------- -------------------- --------------- ----------
TABLE PARTITION    USERS            AUTO_LIST_PART       P1                       8
TABLE PARTITION    USERS            AUTO_LIST_PART       P2                       8
TABLE PARTITION    USERS            AUTO_LIST_PART       P3                       8
TABLE PARTITION    USERS            AUTO_LIST_PART       SYS_P3054                8
TABLE PARTITION    USERS            AUTO_LIST_PART       SYS_P3055                8
TABLE PARTITION    USERS            AUTO_LIST_PART       SYS_P3056                8
TABLE PARTITION    USERS            AUTO_LIST_PART       SYS_P3057                8
TABLE PARTITION    USERS            AUTO_LIST_PART       SYS_P3058                8
                                                                         ----------
Total Segment Size                                                               64

8 rows selected.

As you can see the 8 partitions occupy 64MB of disk space as their initial extend is 8MB regardless of the tiny number of rows they contain:

SQL> select 
        initial_extent/power(1024,2) initial_extentMB
       ,next_extent/power(1024,2)    next_extentMB
       ,extents
     from
        sys_dba_segs
     where
        segment_name = 'AUTO_LIST_PART'
     and
        owner        = 'C##MHOURI'
     and rownum      = 1   -- show only for one partition 
   ; 
INITIAL_EXTENTMB NEXT_EXTENTMB    EXTENTS
---------------- ------------- ----------
               8             1          1

And now the question that motivated this blog post: what happens when a very big insert into an auto-list partitioned table fails and is rolled back (by the transaction itself or via an explicit user rollback):

SQL> insert into auto_list_part
     select
          rownum
         ,mod(rownum,5)
         ,'w'
     from dual
     connect by level <= 100;

100 rows created.

SQL> rollback;

Rollback complete.

SQL> @sizebySegnameMB
Enter value for segment_name: auto_list_part
Enter value for owner: c##mhouri

SEGMENT_TYPE       TABLESPACE_NAME  SEGMENT_NAME         PARTITION_NAME          MB
------------------ ---------------- -------------------- --------------- ----------
TABLE PARTITION    USERS            AUTO_LIST_PART       P1                       8
TABLE PARTITION    USERS            AUTO_LIST_PART       P2                       8
TABLE PARTITION    USERS            AUTO_LIST_PART       P3                       8
TABLE PARTITION    USERS            AUTO_LIST_PART       SYS_P3054                8
TABLE PARTITION    USERS            AUTO_LIST_PART       SYS_P3055                8
TABLE PARTITION    USERS            AUTO_LIST_PART       SYS_P3056                8
TABLE PARTITION    USERS            AUTO_LIST_PART       SYS_P3057                8
TABLE PARTITION    USERS            AUTO_LIST_PART       SYS_P3058                8
TABLE PARTITION    USERS            AUTO_LIST_PART       SYS_P3069                8
TABLE PARTITION    USERS            AUTO_LIST_PART       SYS_P3070                8
TABLE PARTITION    USERS            AUTO_LIST_PART       SYS_P3071                8
TABLE PARTITION    USERS            AUTO_LIST_PART       SYS_P3072                8
TABLE PARTITION    USERS            AUTO_LIST_PART       SYS_P3073                8
TABLE PARTITION    USERS            AUTO_LIST_PART       SYS_P3074                8
TABLE PARTITION    USERS            AUTO_LIST_PART       SYS_P3075                8
TABLE PARTITION    USERS            AUTO_LIST_PART       SYS_P3076                8
TABLE PARTITION    USERS            AUTO_LIST_PART       SYS_P3077                8
TABLE PARTITION    USERS            AUTO_LIST_PART       SYS_P3078                8
TABLE PARTITION    USERS            AUTO_LIST_PART       SYS_P3079                8
TABLE PARTITION    USERS            AUTO_LIST_PART       SYS_P3080                8
TABLE PARTITION    USERS            AUTO_LIST_PART       SYS_P3081                8
TABLE PARTITION    USERS            AUTO_LIST_PART       SYS_P3082                8
TABLE PARTITION    USERS            AUTO_LIST_PART       SYS_P3083                8
TABLE PARTITION    USERS            AUTO_LIST_PART       SYS_P3084                8
TABLE PARTITION    USERS            AUTO_LIST_PART       SYS_P3085                8
TABLE PARTITION    USERS            AUTO_LIST_PART       SYS_P3086                8
TABLE PARTITION    USERS            AUTO_LIST_PART       SYS_P3087                8
TABLE PARTITION    USERS            AUTO_LIST_PART       SYS_P3088                8
TABLE PARTITION    USERS            AUTO_LIST_PART       SYS_P3089                8
TABLE PARTITION    USERS            AUTO_LIST_PART       SYS_P3090                8
TABLE PARTITION    USERS            AUTO_LIST_PART       SYS_P3091                8
TABLE PARTITION    USERS            AUTO_LIST_PART       SYS_P3092                8
TABLE PARTITION    USERS            AUTO_LIST_PART       SYS_P3093                8
TABLE PARTITION    USERS            AUTO_LIST_PART       SYS_P3094                8
TABLE PARTITION    USERS            AUTO_LIST_PART       SYS_P3095                8
TABLE PARTITION    USERS            AUTO_LIST_PART       SYS_P3096                8
TABLE PARTITION    USERS            AUTO_LIST_PART       SYS_P3097                8
TABLE PARTITION    USERS            AUTO_LIST_PART       SYS_P3098                8
TABLE PARTITION    USERS            AUTO_LIST_PART       SYS_P3099                8
TABLE PARTITION    USERS            AUTO_LIST_PART       SYS_P3100                8
TABLE PARTITION    USERS            AUTO_LIST_PART       SYS_P3101                8
TABLE PARTITION    USERS            AUTO_LIST_PART       SYS_P3102                8
TABLE PARTITION    USERS            AUTO_LIST_PART       SYS_P3103                8
TABLE PARTITION    USERS            AUTO_LIST_PART       SYS_P3104                8
TABLE PARTITION    USERS            AUTO_LIST_PART       SYS_P3105                8
TABLE PARTITION    USERS            AUTO_LIST_PART       SYS_P3106                8
TABLE PARTITION    USERS            AUTO_LIST_PART       SYS_P3107                8
TABLE PARTITION    USERS            AUTO_LIST_PART       SYS_P3108                8
TABLE PARTITION    USERS            AUTO_LIST_PART       SYS_P3109                8
TABLE PARTITION    USERS            AUTO_LIST_PART       SYS_P3110                8
TABLE PARTITION    USERS            AUTO_LIST_PART       SYS_P3111                8
TABLE PARTITION    USERS            AUTO_LIST_PART       SYS_P3112                8
TABLE PARTITION    USERS            AUTO_LIST_PART       SYS_P3113                8
TABLE PARTITION    USERS            AUTO_LIST_PART       SYS_P3114                8
TABLE PARTITION    USERS            AUTO_LIST_PART       SYS_P3115                8
TABLE PARTITION    USERS            AUTO_LIST_PART       SYS_P3116                8
TABLE PARTITION    USERS            AUTO_LIST_PART       SYS_P3117                8
TABLE PARTITION    USERS            AUTO_LIST_PART       SYS_P3118                8
TABLE PARTITION    USERS            AUTO_LIST_PART       SYS_P3119                8
TABLE PARTITION    USERS            AUTO_LIST_PART       SYS_P3120                8
TABLE PARTITION    USERS            AUTO_LIST_PART       SYS_P3121                8
TABLE PARTITION    USERS            AUTO_LIST_PART       SYS_P3122                8
TABLE PARTITION    USERS            AUTO_LIST_PART       SYS_P3123                8
TABLE PARTITION    USERS            AUTO_LIST_PART       SYS_P3124                8
TABLE PARTITION    USERS            AUTO_LIST_PART       SYS_P3125                8
TABLE PARTITION    USERS            AUTO_LIST_PART       SYS_P3126                8
TABLE PARTITION    USERS            AUTO_LIST_PART       SYS_P3127                8
TABLE PARTITION    USERS            AUTO_LIST_PART       SYS_P3128                8
TABLE PARTITION    USERS            AUTO_LIST_PART       SYS_P3129                8
TABLE PARTITION    USERS            AUTO_LIST_PART       SYS_P3130                8
TABLE PARTITION    USERS            AUTO_LIST_PART       SYS_P3131                8
TABLE PARTITION    USERS            AUTO_LIST_PART       SYS_P3132                8
TABLE PARTITION    USERS            AUTO_LIST_PART       SYS_P3133                8
TABLE PARTITION    USERS            AUTO_LIST_PART       SYS_P3134                8
TABLE PARTITION    USERS            AUTO_LIST_PART       SYS_P3135                8
TABLE PARTITION    USERS            AUTO_LIST_PART       SYS_P3136                8
TABLE PARTITION    USERS            AUTO_LIST_PART       SYS_P3137                8
TABLE PARTITION    USERS            AUTO_LIST_PART       SYS_P3138                8
TABLE PARTITION    USERS            AUTO_LIST_PART       SYS_P3139                8
TABLE PARTITION    USERS            AUTO_LIST_PART       SYS_P3140                8
TABLE PARTITION    USERS            AUTO_LIST_PART       SYS_P3141                8
TABLE PARTITION    USERS            AUTO_LIST_PART       SYS_P3142                8
TABLE PARTITION    USERS            AUTO_LIST_PART       SYS_P3143                8
TABLE PARTITION    USERS            AUTO_LIST_PART       SYS_P3144                8
TABLE PARTITION    USERS            AUTO_LIST_PART       SYS_P3145                8
TABLE PARTITION    USERS            AUTO_LIST_PART       SYS_P3146                8
TABLE PARTITION    USERS            AUTO_LIST_PART       SYS_P3147                8
TABLE PARTITION    USERS            AUTO_LIST_PART       SYS_P3148                8
                                                                         ----------
Total Segment Size                                                              704

88 rows selected.

As you might have already pointed it out, it doesn’t matter whether I commit or rollback, Oracle will create on the fly, as much partitions as the distinct partition key values I have tried to insert. The immediate consequence is that I ended up with an almost empty partitioned table having 704MB worth of disk space.

If you want to shrink this ”wasted” space then you can move those partitions to use a less bigger initial extent:

SQL> alter table auto_list_part move partition SYS_P3148 
          storage(initial 65536 next 1M);

Table altered.
SQL> @sizeBySegNameMB
Enter value for segment_name: auto_list_part
Enter value for owner: c##mhouri

SEGMENT_TYPE       TABLESPACE_NAME SEGMENT_NAME         PARTITION_NAME          MB
------------------ --------------- -------------------- --------------- ----------
TABLE PARTITION    USERS           AUTO_LIST_PART       P1                       8
TABLE PARTITION    USERS           AUTO_LIST_PART       P2                       8
TABLE PARTITION    USERS           AUTO_LIST_PART       P3                       8
TABLE PARTITION    USERS           AUTO_LIST_PART       SYS_P3054                8
TABLE PARTITION    USERS           AUTO_LIST_PART       SYS_P3055                8
../..
TABLE PARTITION    USERS           AUTO_LIST_PART       SYS_P3148              ,06
                                                                        ----------
Total Segment Size                                                          696,06

you can also prevent such a disk space explosion due to the future automatically created list partitions by un-setting the following parameter (make sure you have read this before):

SQL> alter system set "_partition_large_extents"=false;

SQL> insert into auto_list_part values (9999, 3, 'z');

SQL>@sizebysegNameMB
Enter value for segment_name: auto_list_part
Enter value for owner: c##mhouri

SEGMENT_TYPE       TABLESPACE_NAME SEGMENT_NAME         PARTITION_NAME          MB
------------------ --------------- -------------------- --------------- ----------
TABLE PARTITION    USERS           AUTO_LIST_PART       P1                       8
TABLE PARTITION    USERS           AUTO_LIST_PART       P2                       8
TABLE PARTITION    USERS           AUTO_LIST_PART       P3                       8
TABLE PARTITION    USERS           AUTO_LIST_PART       SYS_P3054                8
../..
TABLE PARTITION    USERS           AUTO_LIST_PART       SYS_P3148              ,06
TABLE PARTITION    USERS           AUTO_LIST_PART       SYS_P3149              ,06
                                                                        ----------
Total Segment Size                                                          696,12

Bottom Line
While the new 12cR2 auto-list partitioning option releases you from foreseeing in advance the creation of new partitions as data arrives, it puts you under two threats (1) a roll backed insert can leave you with a bunch of empty partitions having potentially 8MB of disk space each increasing the total space occupied by your partitioned table (2) since this automatic feature creates one partition per partition key you should make sure to have many rows per partition key; otherwise the one row per partition doesn’t make sense.

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Blog at WordPress.com.

Tony's Oracle Tips

Tony Hasler's light hearted approach to learning about Oracle

Richard Foote's Oracle Blog

Focusing Specifically On Oracle Indexes, Database Administration and Some Great Music

Hatem Mahmoud Oracle's blog

Just another Oracle blog : Database topics and techniques

Mohamed Houri’s Oracle Notes

Qui se conçoit bien s’énonce clairement

Oracle Diagnostician

Performance troubleshooting as exact science

Raheel's Blog

Things I have learnt as Oracle DBA

Coskan's Approach to Oracle

What I learned about Oracle

So Many Oracle Manuals, So Little Time

“Books to the ceiling, Books to the sky, My pile of books is a mile high. How I love them! How I need them! I'll have a long beard by the time I read them”—Lobel, Arnold. Whiskers and Rhymes. William Morrow & Co, 1988.

EU Careers info

Your career in the European Union

Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

Oracle Scratchpad

Just another Oracle weblog

OraStory

Dominic Brooks on Oracle Performance, Tuning, Data Quality & Sensible Design ... (Now with added Sets Appeal)

%d bloggers like this: