How big is an Oracle XMLType when stored as BINARY XML

Question

The Oracle documentation claims that it stores XMLType more compact as BINARY XML than as CLOB. But how do I find out how much space is taken by the binary xml?

CREATE TABLE t (x XMLTYPE) XMLTYPE x STORE AS BINARY XML;

SELECT vsize(x), dbms_lob.getlength(XMLTYPE.getclobval(x)) FROM t;

94 135254
94  63848
94  60188

So, vsize seems to be the size of some sort of pointer or LOB locator, and getclobval unpacks the binary XML into text. But what about the storage size of the binary XML itself?

Please help, the table size is 340GB, so it's worth looking into storage options...

Answer 1

Oracle Binary XML format corresponds to "Compact Schema Aware XML Format" abbreviated as CSX. Encoded data stored as BLOB field. Details about binary XML format available from Oracle documentation (here and here).

Real size of data field depends on LOB storage parameters of XMLType column. E.g. if storage in row option enabled then small documents stored directly with other data and vsize() returns appropriate values.

In reality Oracle creates underlying BLOB column with system name, which can be found by querying user_tab_cols view:

select table_name, column_name, data_type 
from user_tab_cols 
where 
  table_name = 'T' and hidden_column = 'YES'
  and
  column_id = (
      select column_id 
      from user_tab_cols 
      where table_name = 'T' and column_name = 'X'
  ) 

This query returns system hidden column name which looks like SYS_NC00002$.

After that it's possible to get size of fields with regular dbms_lob.getlength() call against hidden column:

select dbms_lob.getlength(SYS_NC00002$) from t