Skip to Main Content
  • Questions
  • Error: "Specified partition does not exist" when creating multi-group XML Index on partitioned table

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, DIEGO.

Asked: February 05, 2018 - 1:59 pm UTC

Last updated: February 06, 2018 - 4:31 pm UTC

Version: 12c

Viewed 1000+ times

You Asked

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.


and Chris said...

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.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Tried code but it does not create the Index Group

DIEGO OTERO, February 05, 2018 - 5:31 pm UTC

Hi

I've tried the new syntax. However it does not create the index group.
I does some change to the XML Code, but does not physically create the index group.

For instance, when I create the first group of the 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;
/>

A new pseudo-table is created:

<SELECT * FROM user_tables WHERE TABLE_NAME = 'IX_TIME_SLOT_XML_P'

TABLE_NAME TABLESPACE_NAME CLUSTER_NAME IOT_NAME STATUS PCT_FREE PCT_USED INI_TRANS MAX_TRANS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE FREELISTS FREELIST_GROUPS LOGGING BACKED_UP NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN AVG_SPACE_FREELIST_BLOCKS NUM_FREELIST_BLOCKS DEGREE INSTANCES CACHE TABLE_LOCK SAMPLE_SIZE LAST_ANALYZED PARTITIONED IOT_TYPE TEMPORARY SECONDARY NESTED BUFFER_POOL FLASH_CACHE CELL_FLASH_CACHE ROW_MOVEMENT GLOBAL_STATS USER_STATS DURATION SKIP_CORRUPT MONITORING CLUSTER_OWNER DEPENDENCIES COMPRESSION COMPRESS_FOR DROPPED READ_ONLY SEGMENT_CREATED RESULT_CACHE CLUSTERING ACTIVITY_TRACKING DML_TIMESTAMP HAS_IDENTITY CONTAINER_DATA INMEMORY INMEMORY_PRIORITY INMEMORY_DISTRIBUTE INMEMORY_COMPRESSION INMEMORY_DUPLICATE
-------------------------------------------------------------------------------------------------------------------------------- ------------------------------ -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- -------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ------- --------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ------------------------- ---------------------- ---------- ---------- ----- ---------- ---------------------- ------------------------- ----------- ------------ --------- --------- ------ ----------- ----------- ---------------- ------------ ------------ ---------- --------------- ------------ ---------- -------------------------------------------------------------------------------------------------------------------------------- ------------ ----------- ------------------------------ ------- --------- --------------- ------------ ---------- ----------------------- ------------------------- ------------ -------------- -------- ----------------- ------------------- -------------------- ------------------
IX_TIME_SLOT_XML_P VALID N 1347 1826 0 0 0 81 0 0 1 1 N ENABLED 1347 04/02/2018 YES N Y NO DEFAULT DEFAULT DEFAULT DISABLED YES NO DISABLED YES DISABLED NO NO N/A DEFAULT NO NO NO
/>

However when I create the additional group with the new syntax:

<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');

ALTER INDEX IX_DAILY_XML_P correcto.
/>

The pseudo-table for the new group is not created:

<SELECT * FROM user_tables WHERE TABLE_NAME = 'IX_ACTIVITY_WORK_SKILLS_XML_P'

No results.
/>

Chris Saxon
February 06, 2018 - 4:31 pm UTC

What happens if you drop and re-create the index instead of altering it?

More to Explore

Design

New to good database design? Check out Chris Saxon's full fundamentals class.