当存储为BINARY XML时，Oracle XMLType有多大 [英] How big is an Oracle XMLType when stored as BINARY XML

问题描述

Oracle文档声称它将XMLType存储为BINARY XML比存储CLOB更紧凑.但是，如何找出二进制xml占用了多少空间?

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

因此，vsize似乎是某种指针或LOB定位器的大小，并且getclobval将二进制XML解压缩为文本.但是二进制XML本身的存储大小如何?

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?

请帮助，表大小为340GB，因此值得研究存储选项...

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

推荐答案

Oracle二进制XML格式对应于缩写为CSX的紧凑型模式感知XML格式".编码数据存储为BLOB字段.可从Oracle文档中获取有关二进制XML格式的详细信息(此处和此处).

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).

数据字段的实际大小取决于XMLType列的LOB存储参数.例如.如果启用了storage in row选项，则小文档直接与其他数据一起存储，而vsize()返回适当的值.

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.

实际上，Oracle使用系统名称创建了底层的BLOB列，可以通过查询user_tab_cols视图来找到该列:

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'
  ) 

此查询返回的系统隐藏列名称类似于SYS_NC00002$.

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

此后，可以通过对隐藏列进行常规的dbms_lob.getlength()调用来获取字段的大小:

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

这篇关于当存储为BINARY XML时，Oracle XMLType有多大的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！