Hi TOM
We are trying to create a multi-group XML Index on a partitioned table.
Table has no sub-partitions as they are not yet supported by XML Indexes, but we need the XML Index to have more than one group.
This is the table:
CREATE TABLE REPLICA_OFSC.R_OFSC_DAILY_EXTRACT_XML_P
( COM_COMUNIDAD_ID INTEGER,
FECHA_FOTO_ID DATE,
ARCHIVO_NOMBRE VARCHAR(250),
ARCHIVO_TIPO VARCHAR(250),
CONTENIDO_XML XMLTYPE
)
PARTITION BY RANGE (FECHA_FOTO_ID) INTERVAL (INTERVAL '1' DAY)
(PARTITION VALUES LESS THAN ( DATE '2017-11-01'));
INSERT INTO R_OFSC_DAILY_EXTRACT_XML_P SELECT * FROM R_OFSC_DAILY_EXTRACT_XML;
COMMIT;
The table is loaded with xml files downloaded from a cloud application as follows:
COM_COMUNIDAD_ID = country id
FECHA_FOTO_ID = date
ARCHIVO_NOMBRE = name of xml file
ARCHIVO_TIPO = type of xml file (inventory, activities, etc).
CONTENIDO_XML = content of the xml file loaded
We can succesfully create the XML Index:
BEGIN
DBMS_XMLINDEX.registerParameter('IX_TIME_SLOT_XML_P',
'
XMLTABLE IX_TIME_SLOT_XML_P ''time_slots/time_slot''
COLUMNS
"ID_DE_TIME_SLOT" VARCHAR(1000) PATH ''Field[@name="ID de Time Slot"]'',
"NOMBRE_DE_TIME_SLOT" VARCHAR(1000) PATH ''Field[@name="Nombre de Time Slot"]'',
"LABEL_DE_TIME_SLOT" VARCHAR(1000) PATH ''Field[@name="Label de Time Slot"]'',
"ESTADO_DE_TIME_SLOT" VARCHAR(1000) PATH ''Field[@name="Estado de Time Slot"]'',
"HORA_DE_INICIO" VARCHAR(1000) PATH ''Field[@name="Hora de Inicio"]'',
"HORA_DE_FIN" VARCHAR(1000) PATH ''Field[@name="Hora de Fin"]''
');
END;
CREATE INDEX IX_DAILY_XML_P ON REPLICA_OFSC.R_OFSC_DAILY_EXTRACT_XML_P(CONTENIDO_XML) INDEXTYPE IS XDB.XMLIndex
PARAMETERS ('PARAM IX_TIME_SLOT_XML_P') LOCAL;
However when trying to add a group to that index we get the error:
BEGIN
DBMS_XMLINDEX.registerParameter('IX_ACTIVITY_WORK_SKILLS_XML_P',
'
ADD_GROUP GROUP IX_ACTIVITY_WORK_SKILLS_XML_P
XMLTABLE IX_ACTIVITY_WORK_SKILLS_XML_P ''appt_work_skills/appt_work_skill''
COLUMNS
"ID_DE_ACTIVIDAD" VARCHAR(1000) PATH ''Field[@name="ID de Actividad"]'',
"ID_DE_HABILIDAD_DE_TRABAJO_DE_" VARCHAR(1000) PATH ''Field[@name="ID de Habilidad de Trabajo de la Actividad"]'',
"NIVEL_REQUERIDO_DE_HABILIDAD" VARCHAR(1000) PATH ''Field[@name="Nivel Requerido de Habilidad"]'',
"NIVEL_PREFERIDO_DE_HABILIDAD" VARCHAR(1000) PATH ''Field[@name="Nivel Preferido de Habilidad"]''
');
END;
ALTER INDEX IX_DAILY_XML_P PARAMETERS('PARAM IX_ACTIVITY_WORK_SKILLS_XML_P');
Error starting at line 50 in command:
ALTER INDEX IX_DAILY_XML_P PARAMETERS('PARAM IX_ACTIVITY_WORK_SKILLS_XML_P')
Error report:
SQL Error: ORA-29874: warning in the execution of ODCIINDEXALTER routine
ORA-29960: line 1, ORA-02149: Specified partition does not exist
29874. 00000 - "warning in the execution of ODCIINDEXALTER routine"
*Cause: A waring 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.
The syntax for registering group IX_ACTIVITY_WORK_SKILLS_XML_P is wrong. You don't ADD_GROUP, just state what it is:
CREATE TABLE R_OFSC_DAILY_EXTRACT_XML_P
( COM_COMUNIDAD_ID INTEGER,
FECHA_FOTO_ID DATE,
ARCHIVO_NOMBRE VARCHAR(250),
ARCHIVO_TIPO VARCHAR(250),
CONTENIDO_XML XMLTYPE
)
PARTITION BY RANGE (FECHA_FOTO_ID) INTERVAL (INTERVAL '1' DAY)
(PARTITION VALUES LESS THAN ( DATE '2017-11-01'));
BEGIN
DBMS_XMLINDEX.registerParameter('IX_TIME_SLOT_XML_P',
' XMLTABLE IX_TIME_SLOT_XML_P ''time_slots/time_slot''
COLUMNS
"ID_DE_TIME_SLOT" VARCHAR(1000) PATH ''Field[@name="ID de Time Slot"]'',
"NOMBRE_DE_TIME_SLOT" VARCHAR(1000) PATH ''Field[@name="Nombre de Time Slot"]'',
"LABEL_DE_TIME_SLOT" VARCHAR(1000) PATH ''Field[@name="Label de Time Slot"]'',
"ESTADO_DE_TIME_SLOT" VARCHAR(1000) PATH ''Field[@name="Estado de Time Slot"]'',
"HORA_DE_INICIO" VARCHAR(1000) PATH ''Field[@name="Hora de Inicio"]'',
"HORA_DE_FIN" VARCHAR(1000) PATH ''Field[@name="Hora de Fin"]''
');
END;
/
CREATE INDEX IX_DAILY_XML_P ON R_OFSC_DAILY_EXTRACT_XML_P(CONTENIDO_XML)
INDEXTYPE IS XDB.XMLIndex PARAMETERS ('PARAM IX_TIME_SLOT_XML_P') LOCAL;
BEGIN
DBMS_XMLINDEX.registerParameter('IX_ACTIVITY_WORK_SKILLS_XML_P',
' GROUP IX_ACTIVITY_WORK_SKILLS_XML_P
XMLTABLE IX_ACTIVITY_WORK_SKILLS_XML_P ''appt_work_skills/appt_work_skill''
COLUMNS
"ID_DE_ACTIVIDAD" VARCHAR(1000) PATH ''Field[@name="ID de Actividad"]'',
"ID_DE_HABILIDAD_DE_TRABAJO_DE_" VARCHAR(1000) PATH ''Field[@name="ID de Habilidad de Trabajo de la Actividad"]'',
"NIVEL_REQUERIDO_DE_HABILIDAD" VARCHAR(1000) PATH ''Field[@name="Nivel Requerido de Habilidad"]'',
"NIVEL_PREFERIDO_DE_HABILIDAD" VARCHAR(1000) PATH ''Field[@name="Nivel Preferido de Habilidad"]''
');
END;
/
ALTER INDEX IX_DAILY_XML_P PARAMETERS('PARAM IX_ACTIVITY_WORK_SKILLS_XML_P');
Index IX_DAILY_XML_P altered.