{"id":654,"date":"2014-05-23T11:29:30","date_gmt":"2014-05-23T09:29:30","guid":{"rendered":"http:\/\/www.ludovicocaldara.net\/dba\/?p=654"},"modified":"2020-08-18T16:41:42","modified_gmt":"2020-08-18T14:41:42","slug":"extended-data-types-storage","status":"publish","type":"post","link":"https:\/\/www.ludovicocaldara.net\/dba\/extended-data-types-storage\/","title":{"rendered":"Where are Extended Data Types stored? (12c)"},"content":{"rendered":"

Oracle has introduced Extended Data Types in 12c. If you still don’t know what they are and how enable them, refer to the awesome oracle-base blog<\/a> or the official documentation<\/a>.<\/p>\n

Here, I’ll show where they are stored, and when.<\/p>\n

Tables created with extended varchar2 (length >4000)<\/strong><\/p>\n

If the table is created with a varchar2 length above 4000, a LOBSEGMENT and a LOBINDEX segments are also created automatically. But actually, the rows inserted may be short enough to be stored inline.<\/p>\n

SQL>  create table ludovico.t32k2 (a number, b varchar2(20000));\r\n\r\nTable created.\r\n\r\nSQL>  DECLARE\r\n  j number;\r\nBEGIN\r\n  FOR j IN 1..1000\r\n  LOOP\r\n    INSERT INTO ludovico.t32k2 values (j, RPAD('LONGSTRING',100,'*') );\r\n  END LOOP;\r\nEND;\r\n\/   2    3    4    5    6    7    8    9\r\n\r\nPL\/SQL procedure successfully completed.\r\n\r\nSQL> select segment_name, segment_type, bytes, blocks from dba_segments where owner='LUDOVICO';\r\n\r\nSEGMENT_NAME                   SEGMENT_TYPE            BYTES     BLOCKS\r\n------------------------------ ------------------ ---------- ----------\r\nT32K2                          TABLE                  196608         24\r\nSYS_IL0000096543C00002$$       LOBINDEX                65536          8\r\nSYS_LOB0000096543C00002$$      LOBSEGMENT             131072         16<\/pre>\n
 <\/p>\n
As you can see from the previous output, the LOB segments are almost empty after 1000 rows inserted, whereas the table has 24 blocks. Not enough to be a proof. Let’s try to update the rows with a column size of 1000:<\/p>\n
SQL> update ludovico.t32k2 set b=RPAD('LONGSTRING',1000,'*');\r\n\r\n1000 rows updated.\r\n\r\nSQL> commit;\r\n\r\nCommit complete.\r\n\r\nSQL> select segment_name, segment_type, bytes, blocks from dba_segments where owner='LUDOVICO';\r\n\r\nSEGMENT_NAME                   SEGMENT_TYPE            BYTES     BLOCKS\r\n------------------------------ ------------------ ---------- ----------\r\nT32K2                          TABLE                 2097152        256\r\nSYS_IL0000096537C00002$$       LOBINDEX                65536          8\r\nSYS_LOB0000096537C00002$$      LOBSEGMENT             131072         16<\/pre>\n
 <\/p>\n
A-ah! The row size is increasing but the blocks are actually allocated in the table segment, this prove that the rows are stored inline!<\/p>\n
Where’s the break point? When the rows will be migrated to the out-of-line storage?<\/p>\n
SQL>  update ludovico.t32k2 set b=RPAD('LONGSTRING',3950,'*');\r\n\r\n1000 rows updated.\r\n\r\nSQL>  commit;\r\n\r\nCommit complete.\r\n\r\nSQL> select segment_name, segment_type, bytes, blocks from dba_segments where owner='LUDOVICO';\r\n\r\nSEGMENT_NAME                  SEGMENT_TYPE            BYTES     BLOCKS\r\n----------------------------- ------------------ ---------- ----------\r\nSYS_LOB0000096549C00002$$     LOBSEGMENT             131072         16\r\nSYS_IL0000096549C00002$$      LOBINDEX                65536          8\r\nT32K2                         TABLE                 8388608       1024\r\n\r\nSQL>  update ludovico.t32k2 set b=RPAD('LONGSTRING',3960,'*');\r\n\r\n1000 rows updated.\r\n\r\nSQL> commit;\r\n\r\nCommit complete.\r\n\r\nSQL> select segment_name, segment_type, bytes, blocks from dba_segments where owner='LUDOVICO';\r\n\r\nSEGMENT_NAME                  SEGMENT_TYPE            BYTES     BLOCKS\r\n----------------------------- ------------------ ---------- ----------\r\nSYS_LOB0000096549C00002$$     LOBSEGMENT             131072         16\r\nSYS_IL0000096549C00002$$      LOBINDEX                65536          8\r\nT32K2                         TABLE                 8388608       1024\r\n\r\nSQL>  update ludovico.t32k2 set b=RPAD('LONGSTRING',3970,'*');\r\n\r\n1000 rows updated.\r\n\r\nSQL>  commit;\r\n\r\nCommit complete.\r\n\r\nSQL> select segment_name, segment_type, bytes, blocks from dba_segments where owner='LUDOVICO';\r\n\r\nSEGMENT_NAME                 SEGMENT_TYPE            BYTES     BLOCKS\r\n---------------------------- ------------------ ---------- ----------\r\nSYS_LOB0000096549C00002$$    LOBSEGMENT           10682368       1304 <<<<<<<<< HERE!\r\nSYS_IL0000096549C00002$$     LOBINDEX                65536          8\r\nT32K2                        TABLE                 8388608       1024\r\n\r\nSQL><\/pre>\n
 <\/p>\n
Actually, it’s somewhere between 3960 and 3970 bytes, but it may depend on other factors (I havn’t tested it deeply).<\/p>\n
Lesson learned: when you design your table with extended types, you should plan how many rows you expect with a size below 4000, because they can make your row access slower and chained rows higher than expected.<\/p>\n
 <\/p>\n
Tables created with standard varchar2 and altered afterward<\/strong><\/p>\n
Let’s try almost the same excercise, but this time starting with a table created with a small length (lower than 4000) and altered afterward to make use of the extended size.<\/p>\n
 SQL> create table ludovico.t32k (a number, b varchar2(500));\r\n\r\nTable created.\r\n\r\nSQL>\r\n\r\nSQL> DECLARE  \r\n2  j number;  \r\n3 BEGIN  \r\n4   FOR j IN 1..1000  \r\n5  LOOP  \r\n6    INSERT INTO ludovico.t32k values (j, RPAD('LONGSTRING',100,'*') );  \r\n7  END LOOP;  \r\n8 END;  \r\n9 \/  \r\n  \r\nPL\/SQL procedure successfully completed.  \r\n  \r\nSQL> select count(*) from ludovico.t32k;  \r\n  \r\n  COUNT(*)  \r\n----------  \r\n      1000  \r\n\r\nSQL>  exec dbms_stats.gather_table_stats('LUDOVICO','T32K');  \r\n  \r\nPL\/SQL procedure successfully completed.  \r\n\r\nSQL> select TABLE_NAME, OBJECT_TYPE, NUM_ROWS, BLOCKS, \r\n2 EMPTY_BLOCKS, AVG_ROW_LEN from dba_tab_statistics \r\n3 where owner='LUDOVICO';\r\n\r\nTABLE_NAME OBJECT_TYPE    NUM_ROWS     BLOCKS EMPTY_BLOCKS AVG_ROW_LEN\r\n---------- ------------ ---------- ---------- ------------ -----------\r\nT32K       TABLE              1000         24            0         105\r\n<\/pre>\n
So far, so good. Let’s try to extend the column:<\/p>\n
SQL>  alter table ludovico.t32k  modify (b varchar2(20000));\r\n\r\nTable altered.\r\n\r\nSQL> select segment_name, segment_type, bytes, blocks from dba_segments where owner='LUDOVICO';\r\n\r\nSEGMENT_NAME    SEGMENT_TYPE            BYTES     BLOCKS\r\n--------------- ------------------ ---------- ----------\r\nT32K            TABLE                  196608         24<\/pre>\n
The LOB segments have not been created. Meaning that the rows are still stored inline. Let’s try then to update all the rows to a size above 4000:<\/p>\n
SQL> update ludovico.t32k set b=RPAD('LONGSTRING',15000 ,'*') ;\r\n\r\n1000 rows updated.\r\n\r\nSQL> commit;\r\n\r\nCommit complete.\r\n\r\nSQL>  select segment_name, segment_type, bytes, blocks from dba_segments where owner='LUDOVICO';\r\n\r\nSEGMENT_NAME    SEGMENT_TYPE            BYTES     BLOCKS\r\n--------------- ------------------ ---------- ----------\r\nT32K            TABLE                16777216       2048<\/pre>\n
The blocks have grown in the table segment, the rows are still inline!!<\/p>\n
SQL> analyze table ludovico.t32k list chained rows into chained_rows;  \r\n  \r\nTable analyzed.  \r\n  \r\nSQL> select count(*) from chained_rows;  \r\n  \r\n  COUNT(*)  \r\n----------  \r\n      1000<\/pre>\n
This lead to a huge amount of chained rows!! In this example, 2 blocks per row, but it can be as high as 5 blocks for a single column varchar2(32767) with db_block_size=8192.<\/p>\n
So we need to rebuild the table, right?<\/p>\n
SQL> alter table  ludovico.t32k move;\r\n\r\nTable altered.\r\n\r\nSQL> select segment_name, segment_type, bytes, blocks from dba_segments where owner='LUDOVICO';\r\n\r\nSEGMENT_NAME    SEGMENT_TYPE            BYTES     BLOCKS\r\n--------------- ------------------ ---------- ----------\r\nT32K            TABLE                17825792       2176<\/pre>\n
WRONG!<\/strong> Even after a rebuild the extended varchars are stored INLINE if they have been created as standard varchars. Actually you need to recreate the table and migrate with dbms_redefinition.<\/p>\n
Migrating to extended datatypes instead of converting your application to use secure files\u00a0 can be a disaster for the physical layout of your tables, thus for the performance of your application. Be careful!<\/p>\n
 <\/p>\n
Update<\/strong>:<\/p>\n