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 or the official documentation.
Here, I’ll show where they are stored, and when.
Tables created with extended varchar2 (length >4000)
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
SQL> create table ludovico.t32k2 (a number, b varchar2(20000)); Table created. SQL> DECLARE j number; BEGIN FOR j IN 1..1000 LOOP INSERT INTO ludovico.t32k2 values (j, RPAD('LONGSTRING',100,'*') ); END LOOP; END; / 2 3 4 5 6 7 8 9 PL/SQL procedure successfully completed. SQL> select segment_name, segment_type, bytes, blocks from dba_segments where owner='LUDOVICO'; SEGMENT_NAME SEGMENT_TYPE BYTES BLOCKS ------------------------------ ------------------ ---------- ---------- T32K2 TABLE 196608 24 SYS_IL0000096543C00002$$ LOBINDEX 65536 8 SYS_LOB0000096543C00002$$ LOBSEGMENT 131072 16 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
SQL> update ludovico.t32k2 set b=RPAD('LONGSTRING',1000,'*'); 1000 rows updated. SQL> commit; Commit complete. SQL> select segment_name, segment_type, bytes, blocks from dba_segments where owner='LUDOVICO'; SEGMENT_NAME SEGMENT_TYPE BYTES BLOCKS ------------------------------ ------------------ ---------- ---------- T32K2 TABLE 2097152 256 SYS_IL0000096537C00002$$ LOBINDEX 65536 8 SYS_LOB0000096537C00002$$ LOBSEGMENT 131072 16 |
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!
Where’s the break point? When the rows will be migrated to the out-of-line storage?
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 |
SQL> update ludovico.t32k2 set b=RPAD('LONGSTRING',3950,'*'); 1000 rows updated. SQL> commit; Commit complete. SQL> select segment_name, segment_type, bytes, blocks from dba_segments where owner='LUDOVICO'; SEGMENT_NAME SEGMENT_TYPE BYTES BLOCKS ----------------------------- ------------------ ---------- ---------- SYS_LOB0000096549C00002$$ LOBSEGMENT 131072 16 SYS_IL0000096549C00002$$ LOBINDEX 65536 8 T32K2 TABLE 8388608 1024 SQL> update ludovico.t32k2 set b=RPAD('LONGSTRING',3960,'*'); 1000 rows updated. SQL> commit; Commit complete. SQL> select segment_name, segment_type, bytes, blocks from dba_segments where owner='LUDOVICO'; SEGMENT_NAME SEGMENT_TYPE BYTES BLOCKS ----------------------------- ------------------ ---------- ---------- SYS_LOB0000096549C00002$$ LOBSEGMENT 131072 16 SYS_IL0000096549C00002$$ LOBINDEX 65536 8 T32K2 TABLE 8388608 1024 SQL> update ludovico.t32k2 set b=RPAD('LONGSTRING',3970,'*'); 1000 rows updated. SQL> commit; Commit complete. SQL> select segment_name, segment_type, bytes, blocks from dba_segments where owner='LUDOVICO'; SEGMENT_NAME SEGMENT_TYPE BYTES BLOCKS ---------------------------- ------------------ ---------- ---------- SYS_LOB0000096549C00002$$ LOBSEGMENT 10682368 1304 <<<<<<<<< HERE! SYS_IL0000096549C00002$$ LOBINDEX 65536 8 T32K2 TABLE 8388608 1024 SQL> |
Actually, it’s somewhere between 3960 and 3970 bytes, but it may depend on other factors (I havn’t tested it deeply).
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.
Tables created with standard varchar2 and altered afterward
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 |
SQL> create table ludovico.t32k (a number, b varchar2(500)); Table created. SQL> SQL> DECLARE 2 j number; 3 BEGIN 4 FOR j IN 1..1000 5 LOOP 6 INSERT INTO ludovico.t32k values (j, RPAD('LONGSTRING',100,'*') ); 7 END LOOP; 8 END; 9 / PL/SQL procedure successfully completed. SQL> select count(*) from ludovico.t32k; COUNT(*) ---------- 1000 SQL> exec dbms_stats.gather_table_stats('LUDOVICO','T32K'); PL/SQL procedure successfully completed. SQL> select TABLE_NAME, OBJECT_TYPE, NUM_ROWS, BLOCKS, 2 EMPTY_BLOCKS, AVG_ROW_LEN from dba_tab_statistics 3 where owner='LUDOVICO'; TABLE_NAME OBJECT_TYPE NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_ROW_LEN ---------- ------------ ---------- ---------- ------------ ----------- T32K TABLE 1000 24 0 105 |
So far, so good. Let’s try to extend the column:
|
1 2 3 4 5 6 7 8 9 |
SQL> alter table ludovico.t32k modify (b varchar2(20000)); Table altered. SQL> select segment_name, segment_type, bytes, blocks from dba_segments where owner='LUDOVICO'; SEGMENT_NAME SEGMENT_TYPE BYTES BLOCKS --------------- ------------------ ---------- ---------- T32K TABLE 196608 24 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
SQL> update ludovico.t32k set b=RPAD('LONGSTRING',15000 ,'*') ; 1000 rows updated. SQL> commit; Commit complete. SQL> select segment_name, segment_type, bytes, blocks from dba_segments where owner='LUDOVICO'; SEGMENT_NAME SEGMENT_TYPE BYTES BLOCKS --------------- ------------------ ---------- ---------- T32K TABLE 16777216 2048 |
The blocks have grown in the table segment, the rows are still inline!!
|
1 2 3 4 5 6 7 8 9 |
SQL> analyze table ludovico.t32k list chained rows into chained_rows; Table analyzed. SQL> select count(*) from chained_rows; COUNT(*) ---------- 1000 |
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.
So we need to rebuild the table, right?
|
1 2 3 4 5 6 7 8 9 |
SQL> alter table ludovico.t32k move; Table altered. SQL> select segment_name, segment_type, bytes, blocks from dba_segments where owner='LUDOVICO'; SEGMENT_NAME SEGMENT_TYPE BYTES BLOCKS --------------- ------------------ ---------- ---------- T32K TABLE 17825792 2176 |
WRONG! 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.
Migrating to extended datatypes instead of converting your application to use secure files can be a disaster for the physical layout of your tables, thus for the performance of your application. Be careful!
Update:
- the limit is actually 3964 and it’s an internal mechanism of lobs since longtime: http://www.juliandyke.com/Presentations/LOBInternals.ppt
- there is a hidden parameter: _scalar_type_lob_storage_threshold that controls the threshold of the varchar size specified in the definition before creating a segment for the lobs (default 4000). see this post: http://orasql.org/2013/07/13/oracle-12c-extended-varchars/
- using extended datatypes instead of clobs may save tons of roundtrips when retrieving many rows: http://www.dbi-services.com/index.php/blog/entry/12c-extended-datatypes-better-than-clob
Many thanks to Sayan Malakshinov (@xtner) and Franck Pachot (@FranckPachot) for the information 🙂
Latest posts by Ludovico (see all)
- New views in Oracle Data Guard 23c - January 3, 2024
- New in Data Guard 21c and 23c: Automatic preparation of the primary - December 22, 2023
- Does FLASHBACK QUERY work across incarnations or after a Data Guard failover? - December 13, 2023
In the comments to my post we also talked about reducing overhead of extra sql*net roundtrips for clobs. 🙂
There is special parameter for extended varchars: http://orasql.org/2013/07/13/oracle-12c-extended-varchars/
Pingback: Where are Extended Data Types stored? (12c) - Ludovico Caldara - Blogs - triBLOG