0

Adding partitions to a table with a local XMLIndex

If you somehow stumbled across this post hoping to get a glimpse of how to add a new range partition to an existing range partitioned table containing a XMLType column with a XMLIndex created on it, prepare to be disappointed as I’ve encountered this very same scenario and have yet to come up with a solution. For those wishing to try it out quickly, here’s the steps needed to setup a quick and dirty test environment.

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE	11.2.0.2.0	Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

CREATE
  TABLE "STAFF"."ORDER"
  (
    "XML_ID" NUMBER(*,0) NOT NULL ENABLE,
    "INVOICE" "SYS"."XMLTYPE" NOT NULL ENABLE,
    "UPLOADED_DATE" DATE NOT NULL ENABLE,
    CONSTRAINT "ORDERS_PK_XML_ID_" PRIMARY KEY ("XML_ID") ENABLE
  )
  TABLESPACE "STAFF" XMLTYPE COLUMN "INVOICE" STORE AS SECUREFILE BINARY XML
  ALLOW NONSCHEMA DISALLOW ANYSCHEMA PARTITION BY RANGE
  (
    "UPLOADED_DATE"
  )
  (
    PARTITION "ORDERS_PART_20110701" VALUES LESS THAN (TO_DATE(
    ' 2011-07-02 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
    )
  ) ;

INSERT INTO "STAFF"."ORDER" (XML_ID, INVOICE, UPLOADED_DATE) VALUES ('1',
'<?xml version = ''1.0''?>
<INVOICE>
   <COMMON>
      <INVOICE_TYPE>VOICE</INVOICE_TYPE>
      <PRIORITY>3</PRIORITY>
   </COMMON>
   <SALE_RESULTS>
      <ENGINE>
         <SALE_TEAM_LIST>
            <TEAM_COUNT>3</TEAM_COUNT>
            <SALE_TEAM>
               <SALE_TEAM_ID>BUFFY</SALE_TEAM_ID>
               <ITEM_LIST>
                  <ITEM>
                     <ITEMSTATUS>RETURNED</ITEMSTATUS>
                     <ITEM_ID>1</ITEM_ID>
                     <PART_LIST>
                        <PART_COUNT>1</PART_COUNT>
                        <PART>
                           <PART_NAME>3</PART_NAME>
                           <PROFILE_ID>84</PROFILE_ID>
                           <PART_PRIORITY>2</PART_PRIORITY>
                           <RECORD_LIST>
                              <RECORD_COUNT>0</RECORD_COUNT>
                           </RECORD_LIST>
                        </PART>
                     </PART_LIST>
                  </ITEM>
				  <ITEM>
                     <ITEMSTATUS>RETURNED</ITEMSTATUS>
                     <ITEM_ID>2</ITEM_ID>
                     <PART_LIST>
                        <PART_COUNT>1</PART_COUNT>
                        <PART>
                           <PART_NAME>4</PART_NAME>
                           <PROFILE_ID>85</PROFILE_ID>
                           <PART_PRIORITY>2</PART_PRIORITY>
                           <RECORD_LIST>
                              <RECORD_COUNT>0</RECORD_COUNT>
                           </RECORD_LIST>
                        </PART>
                     </PART_LIST>
                  </ITEM>
               </ITEM_LIST>
            </SALE_TEAM>
            <SALE_TEAM>
               <SALE_TEAM_ID>XENA</SALE_TEAM_ID>
               <ITEM_LIST>
                  <ITEM>
                     <ITEMSTATUS>RETURNED</ITEMSTATUS>
                     <ITEM_ID>2</ITEM_ID>
                     <PART_LIST>
                        <PART_COUNT>2</PART_COUNT>
                        <PART>
                           <PART_NAME>3</PART_NAME>
                           <PROFILE_ID>31</PROFILE_ID>
                           <PART_PRIORITY>2</PART_PRIORITY>
                           <RECORD_LIST>
                              <RECORD_COUNT>2</RECORD_COUNT>
                              <RECORD>
                                 <RECORD_ID>1</RECORD_ID>
                                 <RECORD_DESC>abc108</RECORD_DESC>
                              </RECORD>
                              <RECORD>
                                 <RECORD_ID>2</RECORD_ID>
                                 <RECORD_DESC>abc634</RECORD_DESC>
                              </RECORD>
                           </RECORD_LIST>
                        </PART>
                     </PART_LIST>
                  </ITEM>
               </ITEM_LIST>
            </SALE_TEAM>
         </SALE_TEAM_LIST>
      </ENGINE>
   </SALE_RESULTS>
</INVOICE>', TO_DATE('20-JUN-11', 'DD-MON-RR'))

BEGIN
  DBMS_XMLINDEX.DROPPARAMETER('indexParam');
END;
/

BEGIN
  DBMS_XMLINDEX.REGISTERPARAMETER(
    'indexParam',
    'PATH TABLE       ORDER_PATH_TABLE
     PATH ID INDEX    ORDER_PATH_ID_IDX
     PIKEY INDEX      ORDER_PIKEY_IDX
     VALUE INDEX      ORDER_VALUE_IDX
     ORDER KEY INDEX  ORDER_ORDERKEY_IDX
     PATHS (INCLUDE (/INVOICE/COMMON/INVOICE_TYPE
                     /INVOICE/COMMON/PRIORITY
                     /INVOICE/SALE_RESULTS/*/SALE_TEAM_LIST/SALE_TEAM/SALE_TEAM_ID
                     /INVOICE/SALE_RESULTS/*/SALE_TEAM_LIST/SALE_TEAM/ITEM_LIST/ITEM/ITEM_ID
                     /INVOICE/SALE_RESULTS/*/SALE_TEAM_LIST/SALE_TEAM/ITEM_LIST/ITEM/PART_LIST/PART/PART_NAME
                     /INVOICE/SALE_RESULTS/*/SALE_TEAM_LIST/SALE_TEAM/ITEM_LIST/ITEM/ITEMSTATUS))
     GROUP metadata_group
      XMLTABLE ORDER_METADATA
        ''/INVOICE''
        COLUMNS
          INVOICE_TYPE         VARCHAR2(32) PATH ''COMMON/INVOICE_TYPE'',
          PRIORITY 			    VARCHAR2(32) PATH ''COMMON/PRIORITY'',
          SALE_TEAM_LIST         XMLTYPE      PATH ''SALE_RESULTS/*/SALE_TEAM_LIST'' VIRTUAL
      XMLTABLE ORDER_SALE_TEAM_LIST
        ''/SALE_TEAM_LIST''
        PASSING SALE_TEAM_LIST
        COLUMNS
          SALE_TEAM              XMLTYPE      PATH ''SALE_TEAM'' VIRTUAL
      XMLTABLE ORDER_SALE_TEAM_ID
        ''/SALE_TEAM''
        PASSING SALE_TEAM
        COLUMNS
          SALE_TEAM_ID           VARCHAR2(32) PATH ''SALE_TEAM_ID'',
          ITEM_LIST        XMLTYPE      PATH ''ITEM_LIST'' VIRTUAL
      XMLTABLE ORDER_ITEM_LIST
        ''/ITEM_LIST''
        PASSING ITEM_LIST
        COLUMNS
          ITEM             XMLTYPE      PATH ''ITEM'' VIRTUAL
      XMLTABLE ORDER_ITEM_ID_ITEMSTATUS
        ''/ITEM''
        PASSING ITEM
        COLUMNS
          ITEM_ID          VARCHAR2(32) PATH ''ITEM_ID'',
          ITEMSTATUS         VARCHAR2(32) PATH ''ITEMSTATUS''
    ');
END;
/

CREATE
  INDEX ORDER_INVOICE_IDX ON "ORDER"
  (
    "INVOICE"
  )
  INDEXTYPE IS "XDB"."XMLINDEX" LOCAL PARAMETERS
  (
    'PARAM indexParam'
  );

CREATE INDEX ORDER_INVOICE_TYPE_IDX ON ORDER_METADATA(INVOICE_TYPE) LOCAL;
CREATE INDEX ORDER_PRIORITY_IDX ON ORDER_METADATA(PRIORITY) LOCAL;
CREATE INDEX ORDER_ISSUE_ID_IDX ON ORDER_ITEM_ID_ITEMSTATUS(ITEM_ID) LOCAL;
CREATE INDEX ORDER_DOCSTATUS_IDX ON ORDER_ITEM_ID_ITEMSTATUS(ITEMSTATUS) LOCAL;
CREATE INDEX ORDER_TEAM_ID_IDX ON ORDER_SALE_TEAM_ID(SALE_TEAM_ID) LOCAL;

SELECT
  *
FROM
  (
    SELECT
      myquery.*,
      rownum rnum
    FROM
      (
        SELECT
          xml_id,
          invoice,
          uploaded_date
        FROM
          "ORDER",
          XMLTABLE('$INVOICE/INVOICE' PASSING "ORDER".INVOICE AS "INVOICE"
          COLUMNS SALE_TEAM_LIST XMLTYPE PATH 'SALE_RESULTS/*/SALE_TEAM_LIST')
          ORDER_METADATA,
          XMLTABLE('$SALE_TEAM_LIST/SALE_TEAM_LIST' PASSING
          ORDER_METADATA.SALE_TEAM_LIST AS "SALE_TEAM_LIST" COLUMNS SALE_TEAM
          XMLType PATH 'SALE_TEAM') ORDER_SALE_TEAM_LIST,
          XMLTABLE('$SALE_TEAM/SALE_TEAM' PASSING
          ORDER_SALE_TEAM_LIST.SALE_TEAM AS "SALE_TEAM" COLUMNS SALE_TEAM_ID
          VARCHAR2(32 ) PATH 'SALE_TEAM_ID', ITEM_LIST XMLTYPE PATH 'ITEM_LIST'
          ) ORDER_SALE_TEAM_ID,
          XMLTABLE('$ITEM_LIST/ITEM_LIST' PASSING ORDER_SALE_TEAM_ID.ITEM_LIST
                                                             AS "ITEM_LIST" COLUMNS ITEM XMLType PATH 'ITEM') ORDER_ITEM_LIST,
          XMLTable('$ITEM/ITEM' PASSING ORDER_ITEM_LIST.ITEM AS "ITEM" COLUMNS
          ITEM_ID VARCHAR2(32) PATH 'ITEM_ID', ITEMSTATUS VARCHAR2(32) PATH
          'ITEMSTATUS') ORDER_ITEM_ID_ITEMSTATUS
        WHERE
          UPLOADED_DATE   >= TO_DATE('01/07/2011', 'dd/MM/YYYY')
        AND UPLOADED_DATE <= to_date('04/07/2011', 'dd/MM/YYYY')
        AND ITEM_ID       IN('1','2','3','4')
        AND ITEMSTATUS    IN('DAMAGED','RETURNED','SOLD')
        AND SALE_TEAM_ID  IN('BUFFY','HERCULES','XENA','GANDALF')
      )
      myquery
    WHERE
      rownum <= 100
  )
WHERE
  rnum >= 1;

ALTER TABLE "ORDER" ADD PARTITION "ORDERS_PART_20110703" VALUES LESS THAN (TO_DATE('2011-07-04 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN');

SQL Error: ORA-29874: warning in the execution of ODCIINDEXALTER routine
ORA-29960: line 1, ORA-30968: invalid XPATH or NAMESPACE option for XML Index
29874. 00000 - "warning in the execution of ODCIINDEXALTER routine"
*Cause:  A warning was returned from the ODCIIndexAlter routine.
*Action: Check to see if the routine has been coded correctly
             Check the user defined warning log tables for greater details.

Apparently the support for partitioned, local domain indexes when it comes to XMLType and XMLIndexes isn’t as mature as that of Oracle Text.

1

Local Structured XMLIndexes

I ran into an interesting problem with XMLDB recently, which involves modifying an existing, local, unstructured XMLIndex to include structured XMLIndex components.  Basically, it seems that you can’t register the XMLTable components with a parameter and alter the index once the partitions have been created.

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0	Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

--my_table is a range partitioned table
CREATE myindex_idx ON my_table(xml_column)

INDEXTYPE IS XDB.XMLINDEX

LOCAL

PARAMETERS('PATH TABLE my_path_table PATHS (INCLUDE (/a/b/c/d/e/f))');

-- Success

BEGIN DBMS_XMLINDEX.REGISTERPARAMETER('my_param','ADD_GROUP GROUP my_group
	XMLTABLE TABLE_1 ''//ZZZ''
	COLUMNS
		ZZZ_COLLECTION XMLTYPE PATH ''YYY'' VIRTUAL
	XMLTABLE TABLE_2 ''/YYY'' PASSING ZZZ_COLLECTION
	COLUMNS
		MY_ID VARCHAR2(32) PATH ''MY_ID''');
END;/

-- Completed

ALTER INDEX myindex_idx PARAMETERS('PARAM my_param');

SQL Error: 	ORA-29874: warning in the execution of ODCIINDEXALTER routine
ORA-29960: 	line 1,
ORA-14701: 	partition-extended name or bind variable must be used for DMLs on tables partitioned by the System method29874.
00000 – 	“warning in the execution of ODCIINDEXALTER routine.
*Cause: 	A warning was returned from the ODCIIndexAlter routine.
*Action: 	Check to see if the routine has been coded correctly
			Check the user defined warning log tables for greater details.

After posting a comment about it on Marco Gralike’s excellent XMLDB blog, he kindly provided a solution to the problem.

An important to take note of is that the ODCIINDEXALTER documentation states that 1000 characters is the max length of the parameter string, which makes the above solution applicable only for parameter strings with a length of 1000 characters or less. If you are facing a boundary case where your parameter string is longer than 1000 characters (like me), here’s the alternative solution.


BEGIN
    DBMS_XMLINDEX.DROPPARAMETER(‘indexParam’);
END;
/

BEGIN
    DBMS_XMLINDEX.REGISTERPARAMETER(
	   ‘indexParam’,
	   ‘PATH TABLE          MY_PATH_TABLE
		PATH ID INDEX       MY_PATH_ID_IDX
		PIKEY INDEX         MY_PIKEY_IDX
		VALUE INDEX			MY_VALUE_IDX
		ORDER KEY INDEX     MY_ORDERKEY_IDX
		PATHS (INCLUDE (
			/a/b/c
			/a/b/d
			/a/e/f
			/a/e/g
			/a/h/i
			/a/j/*/k
			/a/m/*/n/o/p
			/a/m/*/n/o/q/r/s
			/a/m/*/n/o/q/r/t/u/v
			/a/m/*/n/o/q/r/w))
	GROUP group_1
		XMLTABLE MY_PARENT_LIST ”/a/1/*/2”
		COLUMNS
			MY_COLLECTION XMLTYPE    PATH ”TOP_PATH” VIRTUAL
		XMLTABLE MY_CHILD_LIST ”/TOP_PATH” PASSING MY_COLLECTION
		COLUMNS
			MY_ID VARCHAR2(32)       PATH ‘MY_ID”,
			ID_COLLECTION XMLTYPE    PATH ”ID_LIST/ID” VIRTUAL
		XMLTABLE MY_INDIVIDUAL_LIST ”/ID” PASSING ID_COLLECTION
		COLUMNS
			ANOTHER_ID VARCHAR2(32)  PATH ”ANOTHER_ID”,
			ID_STATUS VARCHAR2(32)   PATH ”ID_STATUS”
		GROUP group_2
		XMLTABLE MY_METADATA ”/a”
		COLUMNS
			data VARCHAR2(32) 		PATH ”c/data”,
			link VARCHAR2(32) 		PATH ”d/link”,
			purpose VARCHAR2(32) 	PATH ”d/purpose”,
			urgency VARCHAR2(32) 	PATH ”c/urgency”,
			case_no VARCHAR2(32) 	PATH ”s/case_no”,
			num_1 VARCHAR2(32) 		PATH ”c/normalised/num_1”,
			num_2 VARCHAR2(32) 		PATH ”c/normalised/num2”
END;
/

CREATE
INDEX MY_IDX ON TEST_TABLE
(
	“XML_DOC”
)
INDEXTYPE IS “XDB”.”XMLINDEX”
LOCAL
PARAMETERS
(‘PARAM indexParam’);

The interesting here is that I couldn’t get the above code to work initially when following the example in the Oracle documentation for REGISTERPARAMETER:

DBMS_XMLINDEX.REGISTERPARAMETER (
   ‘myIndexParam’,
   ‘PATH TABLE po_ptab
	PATH ID INDEX po_pidx
	ORDER KEY INDEX po_oidx
	VALUE INDEX po_vidx
	PATHS(NAMESPACE MAPPING(xmlns:p=”http://www.example.com/IPO”))
	GROUP MASTERGROUP XMLTABLE PO_TAB
	(”/p:PurchaseOrder”
		COLUMNS
			REFERENCE VARCHAR2(30) PATH ”p:Reference”,
			REQUESTOR VARCHAR2(30) PATH ”p:Requestor” )
	GROUP ITEMGROUP XMLTABLE ITEMGROUP_TAB
	(”/p:PurchaseOrder/p:LineItems/p:LineItem”
		COLUMNS
			LINENUMBER NUMBER(38) PATH [email protected]:ItemNumber”,
			QUANTITY NUMBER(38) PATH [email protected]:Quantity”,
			DESCRIPTION VARCHAR2(256) PATH ”p:Description” ));

as it kept giving me an incorrectly coded routine error.

After some trial and error, it turns out that I had to remove the brackets that enclose the text that come after the GROUP item_name XMLTABLE xmltable_name, as observed in the example I provided above. I am not sure if it is a typo in the documentation or something that has to do with an incorrect number of quotation marks because of the string etc, but it was an interesting discovery.