Results 1 to 2 of 2
  1. #1
    Join Date
    May 2010
    Posts
    56

    Unanswered: XMLIndexes not possible with Interval Partitioning??

    Hi,

    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..?
    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;

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    14762, 00000, "Domain index creation on interval partitioned tables is not permitted"
    // *Cause:  An attempt was made to create a domain index on an interval
    //          partitioned  table. 
    // *Action: Do not create a domain index on an interval partitioned table.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •