12c big strings – quasi-lobs

One of the new features of 12c is the ability to create varchar2 columns up to 32k in size.
This feature isn’t enabled by default.
You have to put the db into upgrade mode, set max_string_size to EXTENDED and restart the db.

However, once you do that, then the big strings are just like normal varchar2 columns

SQL> CREATE TABLE test_table (big_col VARCHAR2(32767));

Table created.

Or are they? Where does all that data go?


SQL> SELECT table_name,column_name,segment_name,in_row FROM user_lobs;

TABLE_NAME COLUMN_NAME SEGMENT_NAME IN_ROW
--------------- --------------- ------------------------------ ----------
TEST_TABLE BIG_COL SYS_LOB0000092677C00001$$ YES

The big varchar2 columns act like mini-clobs and are written to LOB segments.
So that got me to thinking…
I can create an index on a varchar2 column but I can’t on a clob column.
I’ll get ORA-02327: cannot create index on expression with datatype LOB

So what about these hybrid things?

SQL> CREATE INDEX test_index ON test_table(big_col);
CREATE INDEX test_index ON test_table(big_col)
*
ERROR at line 1:
ORA-01450: maximum key length (6398) exceeded

ah, now that’s different. That’s an old error message but not one I encounter very often.

So, one last test. What about a varchar2 that’s bigger than the old limits but not quite so huge to run into the max key length?

SQL> alter table test_table modify (big_col varchar2(5555));

Index created.

So, now I have data stored as if it were a clob but I can index it because we call it a varchar2.
It’s not likely I’d be using a big column like these as an indexed field, but it’s interesting how the new structures behave.