{ Abhilash Kumar Bhattaram : Follow on LinkedIn }
series : 23ai - CLOB to JSON Migration Part 1
This is a part 1 series of my CLOB to JSON migration data
To create a test lab for Oracle Database 23ai , please use my blog reference here.
CLOB to JSON - Lets understand the basics
To start with lets see what Oracle Documentation says for the datatypes here.
As per Oracle Documentation -
CLOB - A character large object containing single-byte or multibyte characters. Both fixed-width and variable-width character sets are supported, both using the database character set. Maximum size is (4 gigabytes - 1) * (database block size).
JSON - Maximum size is 32 megabytes.
The documentation is self explanatory for CLOB , however for JSON this is poor documentation by Oracle but however JSON offers much more flexibility and efficient storage mechanism ( which we will see in our tests )
For now we are going to see how to move CLOB datatypes to JSON
JSON - Migration Presequisites
A no brainer is that only JSON type of data can be moved to JSON , from 21c onwards there is a check cosntraint "IS JSON" to ensure the data that is being inserted or updated is a JSON
CLOB to JSON - A sample test case
I have created a sample table called sh.promo_clob with 328448 records and the METADATA_CLOB is a CLOB column with JSON data.
FREE> desc sh.promo_clob
Name Null? Type
----------------------------------------- -------- ----------------------------
KEY VARCHAR2(1024)
METADATA_CLOB CLOB
FREE> select * from sh.promo_clob where rownum < 11;
KEY |METADATA_CLOB
----------------------------------------|--------------------------------------------------------------------------------------------------------------------------------------------
{"ROWID":"AAAR6lAAAAAAKiZAFh"} |{"SH":{"COSTS":{"PROD_ID":124,"TIME_ID":"1999-02-24T00:00:00","PROMO_ID":999,"CHANNEL_ID":2,"UNIT_COST":16.1,"UNIT_PRICE":19.1}}}
{"ROWID":"AAAR6lAAAAAAKiaADM"} |{"SH":{"COSTS":{"PROD_ID":40,"TIME_ID":"1999-02-24T00:00:00","PROMO_ID":999,"CHANNEL_ID":3,"UNIT_COST":42,"UNIT_PRICE":51.49}}}
{"ROWID":"AAAR6lAAAAAAKiaAEY"} |{"SH":{"COSTS":{"PROD_ID":117,"TIME_ID":"1999-02-24T00:00:00","PROMO_ID":999,"CHANNEL_ID":3,"UNIT_COST":8.03,"UNIT_PRICE":10.65}}}
{"ROWID":"AAAR6lAAAAAAKiaAE1"} |{"SH":{"COSTS":{"PROD_ID":121,"TIME_ID":"1999-02-24T00:00:00","PROMO_ID":999,"CHANNEL_ID":2,"UNIT_COST":9.47,"UNIT_PRICE":11.34}}}
{"ROWID":"AAAR6lAAAAAAKiaAFh"} |{"SH":{"COSTS":{"PROD_ID":132,"TIME_ID":"1999-02-24T00:00:00","PROMO_ID":999,"CHANNEL_ID":4,"UNIT_COST":19.66,"UNIT_PRICE":23.38}}}
{"ROWID":"AAAR6lAAAAAAKibABN"} |{"SH":{"COSTS":{"PROD_ID":21,"TIME_ID":"1999-02-24T00:00:00","PROMO_ID":999,"CHANNEL_ID":4,"UNIT_COST":767.98,"UNIT_PRICE":1067.73}}}
{"ROWID":"AAAR6lAAAAAAKibACo"} |{"SH":{"COSTS":{"PROD_ID":42,"TIME_ID":"1999-02-24T00:00:00","PROMO_ID":999,"CHANNEL_ID":3,"UNIT_COST":42,"UNIT_PRICE":51.49}}}
{"ROWID":"AAAR6lAAAAAAKibADx"} |{"SH":{"COSTS":{"PROD_ID":119,"TIME_ID":"1999-02-24T00:00:00","PROMO_ID":999,"CHANNEL_ID":3,"UNIT_COST":6.53,"UNIT_PRICE":8.63}}}
{"ROWID":"AAAR6lAAAAAAKibAEj"} |{"SH":{"COSTS":{"PROD_ID":134,"TIME_ID":"1999-02-24T00:00:00","PROMO_ID":999,"CHANNEL_ID":3,"UNIT_COST":19.6,"UNIT_PRICE":26.67}}}
{"ROWID":"AAAR6lAAAAAAKibAF3"} |{"SH":{"COSTS":{"PROD_ID":131,"TIME_ID":"1999-02-24T00:00:00","PROMO_ID":999,"CHANNEL_ID":3,"UNIT_COST":16.95,"UNIT_PRICE":26.11}}}
10 rows selected.
Elapsed: 00:00:00.01
FREE> select count(*) from sh.promo_clob;
COUNT(*)
--------------------
328448
Elapsed: 00:00:00.13
FREE>Now my goal is to move these CLOB data to a new table with JSON datatype
CLOB to JSON - Migration Checks
One of the first steps is to perform migration checks as in Oracle Documentation here
For this test case we move teh convertiable checks to a new table called promo_clob_precheck , currently there is no option to have this table anywhere else othere than the user where it is run from in my case SYS user.
You can see below the convertible checks run well ( unless there is some error in the JSON data in the METADATA_CLOB column )
FREE> execute dbms_json.json_type_convertible_check(owner => 'sh',tablename => 'promo_clob',columnname => 'metadata_clob',statustablename => 'promo_clob_precheck');
PL/SQL procedure successfully completed.
Elapsed: 00:00:04.48
FREE>
select count(*) from promo_clob_precheck;
col STAMP for a30
col SCHEMA_NAME for a10
col TABLE_NAME for a20
col COLUMN_NAME for a20
col ERROR_ROW_ID for a20
col ERROR_CODE for a20
col status for a40
select stamp,schema_name,table_name,column_name,error_row_id,error_code,status from promo_clob_precheck;
STAMP |SCHEMA_NAM|TABLE_NAME |COLUMN_NAME |ERROR_ROW_ID |ERROR_CODE |STATUS
------------------------------|----------|--------------------|--------------------|--------------------|--------------------|----------------------------------------
03-MAY-25 10.48.06.260477 AM |SH |PROMO_CLOB |METADATA_CLOB | | |0% completed (Errors found:0)
03-MAY-25 10.48.06.307502 AM |SH |PROMO_CLOB |METADATA_CLOB | | |1% completed (Errors found:0)
03-MAY-25 10.48.06.351311 AM |SH |PROMO_CLOB |METADATA_CLOB | | |2% completed (Errors found:0)
03-MAY-25 10.48.06.394004 AM |SH |PROMO_CLOB |METADATA_CLOB | | |3% completed (Errors found:0)
03-MAY-25 10.48.06.439763 AM |SH |PROMO_CLOB |METADATA_CLOB | | |4% completed (Errors found:0)
03-MAY-25 10.48.06.486644 AM |SH |PROMO_CLOB |METADATA_CLOB | | |5% completed (Errors found:0)
03-MAY-25 10.48.06.530144 AM |SH |PROMO_CLOB |METADATA_CLOB | | |6% completed (Errors found:0)
03-MAY-25 10.48.06.573801 AM |SH |PROMO_CLOB |METADATA_CLOB | | |7% completed (Errors found:0)
03-MAY-25 10.48.06.617875 AM |SH |PROMO_CLOB |METADATA_CLOB | | |8% completed (Errors found:0)
03-MAY-25 10.48.06.660484 AM |SH |PROMO_CLOB |METADATA_CLOB | | |9% completed (Errors found:0)
03-MAY-25 10.48.06.703783 AM |SH |PROMO_CLOB |METADATA_CLOB | | |10% completed (Errors found:0)
03-MAY-25 10.48.06.754133 AM |SH |PROMO_CLOB |METADATA_CLOB | | |11% completed (Errors found:0)
03-MAY-25 10.48.06.799384 AM |SH |PROMO_CLOB |METADATA_CLOB | | |12% completed (Errors found:0)
03-MAY-25 10.48.06.841552 AM |SH |PROMO_CLOB |METADATA_CLOB | | |13% completed (Errors found:0)
03-MAY-25 10.48.06.889098 AM |SH |PROMO_CLOB |METADATA_CLOB | | |14% completed (Errors found:0)
03-MAY-25 10.48.06.931115 AM |SH |PROMO_CLOB |METADATA_CLOB | | |15% completed (Errors found:0)
03-MAY-25 10.48.06.973731 AM |SH |PROMO_CLOB |METADATA_CLOB | | |16% completed (Errors found:0)
03-MAY-25 10.48.07.015419 AM |SH |PROMO_CLOB |METADATA_CLOB | | |17% completed (Errors found:0)
03-MAY-25 10.48.07.059959 AM |SH |PROMO_CLOB |METADATA_CLOB | | |18% completed (Errors found:0)
03-MAY-25 10.48.07.116673 AM |SH |PROMO_CLOB |METADATA_CLOB | | |19% completed (Errors found:0)
03-MAY-25 10.48.07.160152 AM |SH |PROMO_CLOB |METADATA_CLOB | | |20% completed (Errors found:0)
03-MAY-25 10.48.07.211449 AM |SH |PROMO_CLOB |METADATA_CLOB | | |21% completed (Errors found:0)
03-MAY-25 10.48.07.262148 AM |SH |PROMO_CLOB |METADATA_CLOB | | |22% completed (Errors found:0)
03-MAY-25 10.48.07.305123 AM |SH |PROMO_CLOB |METADATA_CLOB | | |23% completed (Errors found:0)
03-MAY-25 10.48.07.349143 AM |SH |PROMO_CLOB |METADATA_CLOB | | |24% completed (Errors found:0)
03-MAY-25 10.48.07.391138 AM |SH |PROMO_CLOB |METADATA_CLOB | | |25% completed (Errors found:0)
03-MAY-25 10.48.07.433517 AM |SH |PROMO_CLOB |METADATA_CLOB | | |26% completed (Errors found:0)
03-MAY-25 10.48.07.476528 AM |SH |PROMO_CLOB |METADATA_CLOB | | |27% completed (Errors found:0)
03-MAY-25 10.48.07.520354 AM |SH |PROMO_CLOB |METADATA_CLOB | | |28% completed (Errors found:0)
03-MAY-25 10.48.07.562501 AM |SH |PROMO_CLOB |METADATA_CLOB | | |29% completed (Errors found:0)
03-MAY-25 10.48.07.605587 AM |SH |PROMO_CLOB |METADATA_CLOB | | |30% completed (Errors found:0)
03-MAY-25 10.48.07.647365 AM |SH |PROMO_CLOB |METADATA_CLOB | | |31% completed (Errors found:0)
03-MAY-25 10.48.07.690368 AM |SH |PROMO_CLOB |METADATA_CLOB | | |32% completed (Errors found:0)
03-MAY-25 10.48.07.732681 AM |SH |PROMO_CLOB |METADATA_CLOB | | |33% completed (Errors found:0)
03-MAY-25 10.48.07.776653 AM |SH |PROMO_CLOB |METADATA_CLOB | | |34% completed (Errors found:0)
03-MAY-25 10.48.07.818934 AM |SH |PROMO_CLOB |METADATA_CLOB | | |35% completed (Errors found:0)
03-MAY-25 10.48.07.861417 AM |SH |PROMO_CLOB |METADATA_CLOB | | |36% completed (Errors found:0)
03-MAY-25 10.48.07.903779 AM |SH |PROMO_CLOB |METADATA_CLOB | | |37% completed (Errors found:0)
03-MAY-25 10.48.07.946544 AM |SH |PROMO_CLOB |METADATA_CLOB | | |38% completed (Errors found:0)
03-MAY-25 10.48.07.989828 AM |SH |PROMO_CLOB |METADATA_CLOB | | |39% completed (Errors found:0)
03-MAY-25 10.48.08.036402 AM |SH |PROMO_CLOB |METADATA_CLOB | | |40% completed (Errors found:0)
03-MAY-25 10.48.08.079111 AM |SH |PROMO_CLOB |METADATA_CLOB | | |41% completed (Errors found:0)
03-MAY-25 10.48.08.126670 AM |SH |PROMO_CLOB |METADATA_CLOB | | |42% completed (Errors found:0)
03-MAY-25 10.48.08.170018 AM |SH |PROMO_CLOB |METADATA_CLOB | | |43% completed (Errors found:0)
03-MAY-25 10.48.08.216606 AM |SH |PROMO_CLOB |METADATA_CLOB | | |44% completed (Errors found:0)
03-MAY-25 10.48.08.261997 AM |SH |PROMO_CLOB |METADATA_CLOB | | |45% completed (Errors found:0)
03-MAY-25 10.48.08.307163 AM |SH |PROMO_CLOB |METADATA_CLOB | | |46% completed (Errors found:0)
03-MAY-25 10.48.08.352638 AM |SH |PROMO_CLOB |METADATA_CLOB | | |47% completed (Errors found:0)
03-MAY-25 10.48.08.399591 AM |SH |PROMO_CLOB |METADATA_CLOB | | |48% completed (Errors found:0)
03-MAY-25 10.48.08.443348 AM |SH |PROMO_CLOB |METADATA_CLOB | | |49% completed (Errors found:0)
03-MAY-25 10.48.08.486323 AM |SH |PROMO_CLOB |METADATA_CLOB | | |50% completed (Errors found:0)
03-MAY-25 10.48.08.529550 AM |SH |PROMO_CLOB |METADATA_CLOB | | |51% completed (Errors found:0)
03-MAY-25 10.48.08.572365 AM |SH |PROMO_CLOB |METADATA_CLOB | | |52% completed (Errors found:0)
03-MAY-25 10.48.08.615850 AM |SH |PROMO_CLOB |METADATA_CLOB | | |53% completed (Errors found:0)
03-MAY-25 10.48.08.661587 AM |SH |PROMO_CLOB |METADATA_CLOB | | |54% completed (Errors found:0)
03-MAY-25 10.48.08.708066 AM |SH |PROMO_CLOB |METADATA_CLOB | | |55% completed (Errors found:0)
03-MAY-25 10.48.08.753466 AM |SH |PROMO_CLOB |METADATA_CLOB | | |56% completed (Errors found:0)
03-MAY-25 10.48.08.805426 AM |SH |PROMO_CLOB |METADATA_CLOB | | |57% completed (Errors found:0)
03-MAY-25 10.48.08.848151 AM |SH |PROMO_CLOB |METADATA_CLOB | | |58% completed (Errors found:0)
03-MAY-25 10.48.08.891655 AM |SH |PROMO_CLOB |METADATA_CLOB | | |59% completed (Errors found:0)
03-MAY-25 10.48.08.937255 AM |SH |PROMO_CLOB |METADATA_CLOB | | |60% completed (Errors found:0)
03-MAY-25 10.48.08.982800 AM |SH |PROMO_CLOB |METADATA_CLOB | | |61% completed (Errors found:0)
03-MAY-25 10.48.09.026234 AM |SH |PROMO_CLOB |METADATA_CLOB | | |62% completed (Errors found:0)
03-MAY-25 10.48.09.069713 AM |SH |PROMO_CLOB |METADATA_CLOB | | |63% completed (Errors found:0)
03-MAY-25 10.48.09.111791 AM |SH |PROMO_CLOB |METADATA_CLOB | | |64% completed (Errors found:0)
03-MAY-25 10.48.09.155519 AM |SH |PROMO_CLOB |METADATA_CLOB | | |65% completed (Errors found:0)
03-MAY-25 10.48.09.198991 AM |SH |PROMO_CLOB |METADATA_CLOB | | |66% completed (Errors found:0)
03-MAY-25 10.48.09.242264 AM |SH |PROMO_CLOB |METADATA_CLOB | | |67% completed (Errors found:0)
03-MAY-25 10.48.09.287862 AM |SH |PROMO_CLOB |METADATA_CLOB | | |68% completed (Errors found:0)
03-MAY-25 10.48.09.330028 AM |SH |PROMO_CLOB |METADATA_CLOB | | |69% completed (Errors found:0)
03-MAY-25 10.48.09.373333 AM |SH |PROMO_CLOB |METADATA_CLOB | | |70% completed (Errors found:0)
03-MAY-25 10.48.09.416109 AM |SH |PROMO_CLOB |METADATA_CLOB | | |71% completed (Errors found:0)
03-MAY-25 10.48.09.461845 AM |SH |PROMO_CLOB |METADATA_CLOB | | |72% completed (Errors found:0)
03-MAY-25 10.48.09.506791 AM |SH |PROMO_CLOB |METADATA_CLOB | | |73% completed (Errors found:0)
03-MAY-25 10.48.09.550985 AM |SH |PROMO_CLOB |METADATA_CLOB | | |74% completed (Errors found:0)
03-MAY-25 10.48.09.597508 AM |SH |PROMO_CLOB |METADATA_CLOB | | |75% completed (Errors found:0)
03-MAY-25 10.48.09.640999 AM |SH |PROMO_CLOB |METADATA_CLOB | | |76% completed (Errors found:0)
03-MAY-25 10.48.09.687355 AM |SH |PROMO_CLOB |METADATA_CLOB | | |77% completed (Errors found:0)
03-MAY-25 10.48.09.731617 AM |SH |PROMO_CLOB |METADATA_CLOB | | |78% completed (Errors found:0)
03-MAY-25 10.48.09.778332 AM |SH |PROMO_CLOB |METADATA_CLOB | | |79% completed (Errors found:0)
03-MAY-25 10.48.09.822408 AM |SH |PROMO_CLOB |METADATA_CLOB | | |80% completed (Errors found:0)
03-MAY-25 10.48.09.875172 AM |SH |PROMO_CLOB |METADATA_CLOB | | |81% completed (Errors found:0)
03-MAY-25 10.48.09.918338 AM |SH |PROMO_CLOB |METADATA_CLOB | | |82% completed (Errors found:0)
03-MAY-25 10.48.09.963666 AM |SH |PROMO_CLOB |METADATA_CLOB | | |83% completed (Errors found:0)
03-MAY-25 10.48.10.009306 AM |SH |PROMO_CLOB |METADATA_CLOB | | |84% completed (Errors found:0)
03-MAY-25 10.48.10.052176 AM |SH |PROMO_CLOB |METADATA_CLOB | | |85% completed (Errors found:0)
03-MAY-25 10.48.10.094481 AM |SH |PROMO_CLOB |METADATA_CLOB | | |86% completed (Errors found:0)
03-MAY-25 10.48.10.137072 AM |SH |PROMO_CLOB |METADATA_CLOB | | |87% completed (Errors found:0)
03-MAY-25 10.48.10.185312 AM |SH |PROMO_CLOB |METADATA_CLOB | | |88% completed (Errors found:0)
03-MAY-25 10.48.10.228431 AM |SH |PROMO_CLOB |METADATA_CLOB | | |89% completed (Errors found:0)
03-MAY-25 10.48.10.274141 AM |SH |PROMO_CLOB |METADATA_CLOB | | |90% completed (Errors found:0)
03-MAY-25 10.48.10.316675 AM |SH |PROMO_CLOB |METADATA_CLOB | | |91% completed (Errors found:0)
03-MAY-25 10.48.10.359071 AM |SH |PROMO_CLOB |METADATA_CLOB | | |92% completed (Errors found:0)
03-MAY-25 10.48.10.402353 AM |SH |PROMO_CLOB |METADATA_CLOB | | |93% completed (Errors found:0)
03-MAY-25 10.48.10.450096 AM |SH |PROMO_CLOB |METADATA_CLOB | | |94% completed (Errors found:0)
03-MAY-25 10.48.10.494636 AM |SH |PROMO_CLOB |METADATA_CLOB | | |95% completed (Errors found:0)
03-MAY-25 10.48.10.540461 AM |SH |PROMO_CLOB |METADATA_CLOB | | |96% completed (Errors found:0)
03-MAY-25 10.48.10.583454 AM |SH |PROMO_CLOB |METADATA_CLOB | | |97% completed (Errors found:0)
03-MAY-25 10.48.10.625822 AM |SH |PROMO_CLOB |METADATA_CLOB | | |98% completed (Errors found:0)
03-MAY-25 10.48.10.673662 AM |SH |PROMO_CLOB |METADATA_CLOB | | |99% completed (Errors found:0)
03-MAY-25 10.48.10.675092 AM |SH |PROMO_CLOB |METADATA_CLOB | | |Process completed (Errors found: 0)
101 rows selected.
Elapsed: 00:00:00.01
FREE>Post convertible checks we perform a check to see if the rowid matches promo_clob_precheck it would show if there are any errors.
FREE>
SELECT METADATA_CLOB FROM SH.PROMO_CLOB
WHERE ROWID IN (SELECT pt.ERROR_ROW_ID
FROM PROMO_CLOB_PRECHECK pt
WHERE pt.schema_name = 'SH'
AND pt.table_name = 'PROMO_CLOB'
AND pt.column_name = 'METADATA_CLOB'); 2 3 4 5 6
no rows selected
Elapsed: 00:00:00.00This concludes our precheck phase.
CLOB to JSON - The Migration
For this migration we use a CTAS where JSON column is automatically created by using the JSON() conversion
################ Performing JSON Migration
FREE> select count(*) from SH.PROMO_CLOB;
COUNT(*)
--------------------
328448
Elapsed: 00:00:00.02
FREE>
FREE> CREATE TABLE SH.PROMO_JSON AS (SELECT key, JSON(METADATA_CLOB) METADATA_JSON FROM SH.PROMO_CLOB);
Table created.
Elapsed: 00:00:00.05
FREE> select count(*) from SH.PROMO_JSON;
COUNT(*)
--------------------
328448 ---------> count matches !!!CLOB to JSON - Understanding both the tables , columns and the segments used
Lets quickly check all the information about the first table SH.PROMO_CLOB we have used
FREE> @tab_ind SH PROMO_CLOB
Elapsed: 00:00:00.00
### Table Columns
OWNER |TABLE_NAME |COLUMN_NAME |DATA_TYPE | DATA_LENGTH|LAST_ANALYZED |HISTOGRAM
------------------|-----------------------------------|----------------------------------------|----------|--------------------|-----------------|---------------
SH |PROMO_CLOB |KEY |VARCHAR2 | 1024|03-MAY-2025 11:30|NONE
SH |PROMO_CLOB |METADATA_CLOB |CLOB | 4000|03-MAY-2025 11:30|NONE
Elapsed: 00:00:00.01
### Index Tablespaces
TABLE_OWNER |TABLE_NAME |INDEX_OWNER |INDEX_NAME |TABLESPACE_NAME | NUM_ROWS|STATUS |INDEX_TYPE
--------------------|-----------------------------------|--------------------|-----------------------------------|--------------------|--------------------|---------------|----------
SH |PROMO_CLOB |SH |SYS_IL0000074402C00002$$ |USERS | |VALID |LOB
Elapsed: 00:00:00.00
### Indexed Columns
no rows selected
Elapsed: 00:00:00.00
### Index Created Timestamp
OWNER |OBJECT_NAME |SUBOBJECT_NAME |OBJECT_TYPE |LAST_DDL_TIME |CREATED
------------------|-----------------------------------|------------------------------|-----------------------|-----------------|-----------------
SH |SYS_IL0000074402C00002$$ | |INDEX |03-MAY-2025 10:22|03-MAY-2025 10:22
Elapsed: 00:00:00.03
### Table Segments Sizes
MB|TABLESPACE_NAME |OWNER |SEGMENT_NAME |SEGMENT_TYPE | PARTITIONS
--------------------|-------------------------|-------------------------|----------------------------------------|--------------------|--------------------
125|USERS |SH |PROMO_CLOB |TABLE |
--------------------| | | | |
125| | | | |
Elapsed: 00:00:00.02
### Index Segments Sizes
MB|TABLESPACE_NAME |OWNER |SEGMENT_NAME |SEGMENT_TYPE | PARTITIONS
--------------------|-------------------------|-------------------------|----------------------------------------|--------------------|--------------------
0|USERS |SH |SYS_IL0000074402C00002$$ |LOBINDEX |
--------------------| | | | |
0| | | | |
Elapsed: 00:00:00.02
FREE> @seg1 SH PROMO_CLOB
### Data Segments/Partitions for "SH"."PROMO_CLOB"
MB|TABLESPACE_NAME |OWNER |SEGMENT_NAME |SEGMENT_TYPE | PARTITIONS
------------|-------------------------|-------------------------|----------------------------------------|--------------------|--------------------
125|USERS |SH |PROMO_CLOB |TABLE |
------------| | | | |
125| | | | |
Elapsed: 00:00:00.00
### LOB Partitions for "SH"."PROMO_CLOB"
no rows selected
Elapsed: 00:00:00.01
### LOB Segment Space for "SH"."PROMO_CLOB"
MB|TABLESPACE_NAME |OWNER |SEGMENT_NAME |SEGMENT_TYPE | PARTITIONS
------------|-------------------------|-------------------------|----------------------------------------|--------------------|--------------------
0|USERS |SH |SYS_LOB0000074402C00002$$ |LOBSEGMENT |
------------| | | | |
0| | | | |
Elapsed: 00:00:00.96
### LOB Columns for "SH"."PROMO_CLOB"
OWNER |TABLE_NAME |SEGMENT_NAME |COLUMN_NAME | COUNT(*)
------------------------------|------------------------------|------------------------------|------------------------------|--------------------
SH |PROMO_CLOB |SYS_LOB0000074402C00002$$ |METADATA_CLOB | 1
Elapsed: 00:00:00.78
FREE> select count(*) from SH.PROMO_CLOB;
COUNT(*)
--------------------
328448
Elapsed: 00:00:00.01
FREE>
FREE> col key for a40
FREE> col metadata_json for a140
FREE> select * from sh.promo_json where rownum < 11;
KEY |METADATA_JSON
----------------------------------------|--------------------------------------------------------------------------------------------------------------------------------------------
{"ROWID":"AAAR6lAAAAAAKiZAFh"} |{"SH":{"COSTS":{"PROD_ID":124,"TIME_ID":"1999-02-24T00:00:00","PROMO_ID":999,"CHANNEL_ID":2,"UNIT_COST":16.1,"UNIT_PRICE":19.1}}}
{"ROWID":"AAAR6lAAAAAAKiaADM"} |{"SH":{"COSTS":{"PROD_ID":40,"TIME_ID":"1999-02-24T00:00:00","PROMO_ID":999,"CHANNEL_ID":3,"UNIT_COST":42,"UNIT_PRICE":51.49}}}
{"ROWID":"AAAR6lAAAAAAKiaAEY"} |{"SH":{"COSTS":{"PROD_ID":117,"TIME_ID":"1999-02-24T00:00:00","PROMO_ID":999,"CHANNEL_ID":3,"UNIT_COST":8.03,"UNIT_PRICE":10.65}}}
{"ROWID":"AAAR6lAAAAAAKiaAE1"} |{"SH":{"COSTS":{"PROD_ID":121,"TIME_ID":"1999-02-24T00:00:00","PROMO_ID":999,"CHANNEL_ID":2,"UNIT_COST":9.47,"UNIT_PRICE":11.34}}}
{"ROWID":"AAAR6lAAAAAAKiaAFh"} |{"SH":{"COSTS":{"PROD_ID":132,"TIME_ID":"1999-02-24T00:00:00","PROMO_ID":999,"CHANNEL_ID":4,"UNIT_COST":19.66,"UNIT_PRICE":23.38}}}
{"ROWID":"AAAR6lAAAAAAKibABN"} |{"SH":{"COSTS":{"PROD_ID":21,"TIME_ID":"1999-02-24T00:00:00","PROMO_ID":999,"CHANNEL_ID":4,"UNIT_COST":767.98,"UNIT_PRICE":1067.73}}}
{"ROWID":"AAAR6lAAAAAAKibACo"} |{"SH":{"COSTS":{"PROD_ID":42,"TIME_ID":"1999-02-24T00:00:00","PROMO_ID":999,"CHANNEL_ID":3,"UNIT_COST":42,"UNIT_PRICE":51.49}}}
{"ROWID":"AAAR6lAAAAAAKibADx"} |{"SH":{"COSTS":{"PROD_ID":119,"TIME_ID":"1999-02-24T00:00:00","PROMO_ID":999,"CHANNEL_ID":3,"UNIT_COST":6.53,"UNIT_PRICE":8.63}}}
{"ROWID":"AAAR6lAAAAAAKibAEj"} |{"SH":{"COSTS":{"PROD_ID":134,"TIME_ID":"1999-02-24T00:00:00","PROMO_ID":999,"CHANNEL_ID":3,"UNIT_COST":19.6,"UNIT_PRICE":26.67}}}
{"ROWID":"AAAR6lAAAAAAKibAF3"} |{"SH":{"COSTS":{"PROD_ID":131,"TIME_ID":"1999-02-24T00:00:00","PROMO_ID":999,"CHANNEL_ID":3,"UNIT_COST":16.95,"UNIT_PRICE":26.11}}}
10 rows selected.
Elapsed: 00:00:00.01
FREE>Now lets do the same from the SH.PROMO_JSON
FREE> @tab_ind SH PROMO_JSON
Elapsed: 00:00:00.01
### Table Columns
OWNER |TABLE_NAME |COLUMN_NAME |DATA_TYPE | DATA_LENGTH|LAST_ANALYZED |HISTOGRAM
------------------------------|-----------------------------------|----------------------------------------|----------|--------------------|-----------------|---------------
SH |PROMO_JSON |KEY |VARCHAR2 | 1024|03-MAY-2025 11:25|NONE
SH |PROMO_JSON |METADATA_JSON |JSON | 8200|03-MAY-2025 11:25|NONE
Elapsed: 00:00:00.04
### Index Tablespaces
TABLE_OWNER |TABLE_NAME |INDEX_OWNER |INDEX_NAME |TABLESPACE_NAME | NUM_ROWS|STATUS |INDEX_TYPE
--------------------|-----------------------------------|--------------------|-----------------------------------|--------------------|--------------------|---------------|----------
SH |PROMO_JSON |SH |SYS_IL0000074410C00002$$ |USERS | |VALID |LOB
Elapsed: 00:00:00.01
### Indexed Columns
no rows selected
Elapsed: 00:00:00.01
### Index Created Timestamp
OWNER |OBJECT_NAME |SUBOBJECT_NAME |OBJECT_TYPE |LAST_DDL_TIME |CREATED
------------------------------|-----------------------------------|------------------------------|-----------------------|-----------------|-----------------
SH |SYS_IL0000074410C00002$$ | |INDEX |03-MAY-2025 11:25|03-MAY-2025 11:25
Elapsed: 00:00:00.20
### Table Segments Sizes
MB|TABLESPACE_NAME |OWNER |SEGMENT_NAME |SEGMENT_TYPE | PARTITIONS
------------|-------------------------|-------------------------|----------------------------------------|--------------------|--------------------
88|USERS |SH |PROMO_JSON |TABLE |
------------| | | | |
88| | | | |
Elapsed: 00:00:00.44
### Index Segments Sizes
MB|TABLESPACE_NAME |OWNER |SEGMENT_NAME |SEGMENT_TYPE | PARTITIONS
------------|-------------------------|-------------------------|----------------------------------------|--------------------|--------------------
0|USERS |SH |SYS_IL0000074410C00002$$ |LOBINDEX |
------------| | | | |
0| | | | |
Elapsed: 00:00:00.24
FREE> select count(*) from SH.PROMO_JSON;
COUNT(*)
--------------------
328448
Elapsed: 00:00:00.10
FREE>JSON vs CLOB - Table Comparison
Though the test data used in this case is relatively small I can come up with the following colclusions with this.
- There were no JSON convertible errors in this test case , which is the first landmark to achieve , in actual test these needs to be fixed.
- The count 328448 matches for both tables for SH.PROMO_CLOB and SH.PROMO_JSON
- Note that JSON data type is also a large object but with better storage management.
- The table space for PROMO_CLOB is 125 MB [ 128,000 / 1024 = 125 ] whereas the the PROMO_JSON is 88 MB [ 90,112 / 1024 = 88 ]
There is defenitely better JSON storage management within the tables than CLOB making it promising for storing large API data.
My next series 2 woudl be with more realistic and larger JSON data.