Oracle - is there any performance hit for a CLOB column that contains rows with less than 4K rows vs a Varchar column?

Question

If you need to store strings which are mostly, if not all, less than 4K bytes, is there any negative to using a CLOB datatype over a VARCHAR2 datatype?

From here

A CLOB may be stored in a seperate LOB segment, with a pointer to the LOB segment stored in the data block with the rest of the table's data. You can control where the LOB segment is stored with the LOB storage clause if the CREATE TABLE statement. By default, a LOB less than about 4000 bytes will be stored the same way as a VARCHAR2, that is, in-line with the rest of the data.

From here:

When you create a table with a CLOB column, there is an option called 'Enable Storage In Row' for the CLOB. This dictates whether the CLOB is stored in the same segment as the table, and hence alongside the rest of the table row, or in a separate segment by itself. In the second case, the table row would then contain a pointer to the location of the CLOB data. Generally it is more efficient to store the CLOB with the table row, but if the CLOB is longer than about 4000 characters, it can no longer be stored in the row, and is stored in the CLOB segment regardless of whether storage in row in enabled or not.

From these statements, it sounds to me like there is no penalty at all for declaring a column as a CLOB even though all the rows will contain less than 4K rows. Is this accurate?

Answer 1

There are significant impacts when you use LOB datatypes, for instance:

• Depends on how you read data, each row LOB access could mean additional network roundtrip even if the data is stored in row
• Optimizer can use many more stats on VARCHAR columns, histograms, much more precise length, bind peeking, etc.
• Some execution plan optimizations are not available with LOB columns, for instance materializing of factored subqueries
• Many SQL functions cannot be used with LOB.

Example of additional roundtrip:

HUSQVIK@hq_pdb_tcp> CREATE TABLE t_varchar AS SELECT CAST('data' AS VARCHAR2(4)) c1 FROM DUAL CONNECT BY LEVEL <= 10;

Table created.

HUSQVIK@hq_pdb_tcp> CREATE TABLE t_clob AS SELECT to_clob('data') c1 FROM DUAL CONNECT BY LEVEL <= 10;

Table created.

HUSQVIK@hq_pdb_tcp> SET AUTOT TRACE
HUSQVIK@hq_pdb_tcp> SELECT * FROM t_varchar;

10 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 4100862799

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |    10 |    50 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| T_VARCHAR |    10 |    50 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          5  recursive calls
          0  db block gets
          5  consistent gets
          5  physical reads
          0  redo size
        682  bytes sent via SQL*Net to client
        551  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         10  rows processed

HUSQVIK@hq_pdb_tcp> SELECT * FROM t_clob;

10 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3459655851

----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |    10 |   410 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| T_CLOB |    10 |   410 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          2  recursive calls
          0  db block gets
         23  consistent gets
          5  physical reads
          0  redo size
       3433  bytes sent via SQL*Net to client
       3231  bytes received via SQL*Net from client
         12  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         10  rows processed

Note also the increased number of consistent gets, as Oracle processes the same block containing the LOB data over and over again with each roundtrip.