How big is an Oracle XMLType when stored as BINARY XML
oracle, oracle11gr2, sql, xml
Solution
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
Problem
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...