1 Reply Latest reply: Nov 7, 2012 5:23 AM by odie_63 RSS

    XMLIndexes on an Interval Partitioned Table??

    user8941550
      Hi,

      We are using:
      Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit

      I try to create an XMLIndex on an interval Partitioned Table.
      But I get the error:
      ORA-14762: Domain index creation on interval partitioned tables is not permitted


      So does that mean I will have to create a Range Partitioned instead..i.e. basically remove the Interval part..? In our DB we are having several tables which are Interval Partitioned in Production, so we want to avoid that somehow.
      Please suggest.

      CREATE TABLE INT_PART_TABLE
      (
      DB_ID VARCHAR2(10 BYTE),
      xML_mESSAGE SYS.XMLTYPE,
      LOAD_TIMESTAMP TIMESTAMP(6)
      )
      XMLTYPE xML_mESSAGE STORE AS BINARY XML (

      ENABLE STORAGE IN ROW
      CHUNK 16384
      RETENTION
      NOCACHE
      LOGGING)
      ALLOW NONSCHEMA
      DISALLOW ANYSCHEMA

      PCTUSED 0
      PCTFREE 10
      INITRANS 1
      MAXTRANS 255
      STORAGE (
      BUFFER_POOL DEFAULT
      )
      PARTITION BY RANGE (LOAD_TIMESTAMP)
      INTERVAL( NUMTODSINTERVAL(1,'DAY'))
      (
      PARTITION P_FIRST VALUES LESS THAN (TIMESTAMP' 2012-01-01 00:00:00')
      LOGGING
      NOCOMPRESS

      PCTFREE 10
      INITRANS 1
      MAXTRANS 255
      STORAGE (
      INITIAL 512K
      NEXT 512K
      MINEXTENTS 1
      MAXEXTENTS UNLIMITED
      PCTINCREASE 0
      BUFFER_POOL DEFAULT
      ),
      PARTITION VALUES LESS THAN (TIMESTAMP' 2012-09-07 00:00:00')
      LOGGING
      NOCOMPRESS

      PCTFREE 10
      INITRANS 1
      MAXTRANS 255
      STORAGE (
      INITIAL 512K
      NEXT 512K
      MINEXTENTS 1
      MAXEXTENTS UNLIMITED
      PCTINCREASE 0
      BUFFER_POOL DEFAULT
      ),
      PARTITION VALUES LESS THAN (TIMESTAMP' 2012-11-07 00:00:00')
      LOGGING
      NOCOMPRESS

      PCTFREE 10
      INITRANS 1
      MAXTRANS 255
      STORAGE (
      INITIAL 512K
      NEXT 512K
      MINEXTENTS 1
      MAXEXTENTS UNLIMITED
      PCTINCREASE 0
      BUFFER_POOL DEFAULT
      ),
      PARTITION VALUES LESS THAN (TIMESTAMP' 2012-11-08 00:00:00')
      LOGGING
      NOCOMPRESS

      PCTFREE 10
      INITRANS 1
      MAXTRANS 255
      STORAGE (
      INITIAL 512K
      NEXT 512K
      MINEXTENTS 1
      MAXEXTENTS UNLIMITED
      PCTINCREASE 0
      BUFFER_POOL DEFAULT
      )
      )
      NOCOMPRESS
      NOCACHE
      NOPARALLEL
      ROWDEPENDENCIES
      MONITORING
      ENABLE ROW MOVEMENT;



      BEGIN
      DBMS_XMLINDEX.dropparameter('Indx_Par');
      END;

      BEGIN
      DBMS_XMLINDEX.REGISTERPARAMETER(
      'Indx_Par',
      'PATH TABLE Table1
      PATHS (INCLUDE ( /abc:field1/xyz:field2
      /abc:field1/def:field2
      )
      NAMESPACE MAPPING ( xmlns:abc="ABCD"
      xmlns:def="DEFG"
      xmlns:xyz="XYZA"
      ))
      ');
      end;


      create index INDX_XPATHS on "INT_PART_TABLE" (XML_MESSAGE) indextype is xdb.xmlindex
      parameters ('PARAM Indx_Par') local;