XMLIndex support for Hash Partitioning is now also supported in Oracle 12c. In Oracle 11 there was already support for RANGE and LIST partitioning, but not yet for HASH partitioning. Some examples for the later two:
LIST Partitioning on XMLType
--
-- Create partitioned LIST partitioned XMLType table
--
DROP TABLE list_part_xml PURGE;
CREATE TABLE list_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') tablespace "USERS",
PARTITION law_firms VALUES ('Law Firm') tablespace "USERS"
);
--
-- Create LOCAL partitioned XMLIndex
--
CREATE INDEX list_part_xmlindex ON list_part_xml(object_value)
INDEXTYPE IS XDB.XMLINDEX
LOCAL
PARALLEL
PARAMETERS ('PATH TABLE xml_path_table_list
PIKEY INDEX pikey_ix_list
VALUE INDEX value_ix_list');
--
RANGE Partitioning on XMLType
or for RANGE parititioning on XML and an index example, this could done via…
--
-- Create partitioned RANGE partitioned XMLType table
--
DROP TABLE range_part_xml PURGE;
CREATE TABLE range_part_xml OF XMLType
XMLTYPE STORE AS SECUREFILE BINARY XML
VIRTUAL COLUMNS
(
RANGE_TYPE AS (XMLCast(XMLQuery('/RANGE/@TYPE'
PASSING OBJECT_VALUE RETURNING CONTENT) AS VARCHAR2(100)))
)
PARTITION BY RANGE (RANGE_TYPE)
(
PARTITION range_part_01 VALUES less than (100) tablespace "USERS"
,PARTITION range_part_02 VALUES less than (200) tablespace "USERS"
,PARTITION range_part_max VALUES less than (MAXVALUE)
);
--
-- Create LOCAL partitioned XMLIndex with extended syntax
--
CREATE INDEX range_part_xmlindex ON range_part_xml(object_value)
INDEXTYPE IS XDB.XMLINDEX
LOCAL
PARALLEL 4
PARAMETERS ('PATH TABLE xml_path_table_range (PARALLEL 10)
PIKEY INDEX pikey_ix_range
VALUE INDEX value_ix_range (PARALLEL 2 TABLESPACE USERS)');
--
HASH Partitioning on XMLType
As mentioned, now in Oracle 12c, HASH partitioning is also supported for XMLType Indexes, an example:
--
-- Create partitioned HASH partitioned XMLType table
--
DROP TABLE hash_part_xml PURGE;
CREATE TABLE hash_part_xml OF XMLType
XMLTYPE STORE AS SECUREFILE BINARY XML
VIRTUAL COLUMNS
(
HASH_ID AS (XMLCast(XMLQuery('/HASH/@ID'
PASSING OBJECT_VALUE RETURNING CONTENT) AS NUMBER(38)))
)
PARTITION BY HASH (HASH_ID)
(
PARTITION hash_section_01 tablespace "USERS"
,PARTITION hash_section_02 tablespace "USERS"
,PARTITION hash_section_03 tablespace "USERS"
);
--
-- Create LOCAL partitioned XMLIndex with even more extended syntax
--
CREATE INDEX hash_part_xmlindex ON hash_part_xml(object_value)
INDEXTYPE IS XDB.XMLINDEX
LOCAL
NOPARALLEL
PARAMETERS ('PATH TABLE xml_path_table_hash (TABLESPACE USERS)
PIKEY INDEX pikey_ix_hash (PARALLEL 4 TABLESPACE USERS)
PATH ID INDEX path_id_ix_hash (PARALLEL 2 TABLESPACE USERS)
ORDER KEY INDEX order_key_ix_hash (PARALLEL 2 TABLESPACE USERS)
VALUE INDEX value_ix_hash (PARALLEL TABLESPACE USERS)
ASYNC (SYNC ALWAYS) STALE (FALSE)');
--
For more information about XMLIndex usage etc, see the Oracle XMLDB Developers Guide for Oracle Database 12c
.