HOWTO: Partition Binary XML based on a Virtual Column

This one is long overdue. There is a partition example of binary xml on this website based on Range, Hash and List partitioning, but this is, seen from a XML view, a incorrect way to do it due to the fact that the partition column is a regular one and not a virtual column as described in the Oracle XMLDB Developers Guide for 11.2. The examples given in that ppost will partition the table on the ID column but does not partition the XML based on a value/element IN the XML document. The following will.

So here, a small example, of how it can be done based on a virtual column. Be aware that you should support these virtual columns with at least an index or XMLIndex structure for performance reasons.

CREATE TABLE binary_part_xml OF XMLType
  XMLTYPE STORE AS SECUREFILE BINARY XML
  VIRTUAL COLUMNS
  (
    LISTING_TYPE AS (XMLCast(XMLQuery('/LISTING/@TYPE'
         PASSING OBJECT_VALUE RETURNING CONTENT) AS VARCHAR2(100)))
  )
  PARTITION BY LIST (LISTING_TYPE)
  (
    PARTITION health VALUES ('Health'),
    PARTITION law_firms VALUES ('Law Firm')
  );