Oracle Database 12c: XMLIndex Support for Hash Partitioning

Read Time:2 Minute, 20 Second

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
.

About Post Author

Marco Gralike

Marco Gralike, working for AMIS Services BV as a Principal Oracle Database Consultant in the Netherlands, has experience as a DBA since 1994 (Oracle 6). Marco is also eager and skillful in other fields, like Operating System Administration and Application Servers, mainly to find working, performing solutions. Marco has been specializing in Oracle XMLDB, since 2003, focusing on his old love, database administration and performance.He is an Oracle XMLDB enthusiast ever since. He is also a dedicated contributor of the Oracle User Group community, helping people with their steep XMLDB learning curve. To this purpose, Marco also devoted his personal blog site to XMLDB and other Oracle issues. Marco is a member of the OakTable network and an Oracle ACE Director (specialization Oracle XMLDB).

info@amis.nl

http://blog.gralike.com