Why does a 20MB JSON Array creates a 2GB Table-CodePudding

in Oracle 19C DB i have a json with the following format

"rows":[[1, "sometext", "some other text", timestamp, ...],
[..],
...]

with 43 Items (most of them strings from 1 to 150 characters long) and 1.2 Mio Itemsets. It is saved in a blob column (JSON_BLOB ~ 20 MB) of a table (JSON_BLOB_TABLE ) with the "is json" constraint.

When i create a table like:

CREATE TABLE MY_JSON_TABLE AS (
select j.*
from JSON_BLOB_TABLE d, 
     json_table ( 
        d.JSON_BLOB, '$' columns ( 
            nested path '$.rows[*]' columns ( 
                col1 NUMBER PATH '$[0]',
                col2 VARCHAR2(8) PATH '$[1]',
                ...
            )
        )
     ) j 
)

the size of this table by

SELECT BYTES/1024/1024 MB FROM USER_SEGMENTS WHERE SEGMENT_NAME = 'MY_JSON_TABLE';

is more than 2000 MB. How is that possible? The amount of rows in this table is still 1.2 Mio, no columns were added. Am i missing an important table configuration here to save space?

CodePudding user response：

You made two significant errors in your observation:

First you confuse the size of the data and the size of the table.

The latter is always higher due to overhead and free space

Here simple example - the table has 1000 rows with approx. 1000 bytes per row

create table tab as
select rownum id, lpad('x',999,'y') txt
from dual connect by level <= 1000;

But it allocates 2 MB

select BYTES/1024/1024 MB  from user_segments where segment_name = 'TAB';
        MB
----------
         2

But the size of the data in the table is as expected close to 1MB (checking the table statistics)

select NUM_ROWS, AVG_ROW_LEN,
(NUM_ROWS * AVG_ROW_LEN) /1024/1024 MB
from user_tables where table_name = 'TAB';

  NUM_ROWS AVG_ROW_LEN         MB
---------- ----------- ----------
      1000        1004 ,957489014

The second problem which is more serious is your estimatimation of the JSON column size.

create table tab_json as
select 1 id,
JSON_ARRAYAGG(JSON_OBJECT (id, txt RETURNING BLOB) ORDER BY id RETURNING BLOB)  js
from tab;

select BYTES/1024/1024 MB  from user_segments where segment_name = 'TAB_JSON';

        MB
----------
,0625

The table with JSON column (stored as BLOB) containing all the data of 1 MB is only 62 KB large! How is this possible!!

Very simple - the JSON is stored as BLOB i.e. not in the table, but in LOB segment. You must find the LOB segment in USER_LOBS and check it's size

select SEGMENT_NAME from user_lobs where table_name = 'TAB_JSON';

SEGMENT_NAME                                                                                                                    
------------------------- 
SYS_LOB0000073542C00002$$

select BYTES/1024/1024 MB  from user_segments where segment_name in (
select SEGMENT_NAME from user_lobs where table_name = 'TAB_JSON');
        MB
----------
    2,1875

Now if you compare the whole size of the JSON table (i.e. table segments LOB segments) you see there is no big surprise but the results are comparable.