This discussion is archived
2 Replies Latest reply: Jun 24, 2013 10:05 AM by KnightOfBlueArmor RSS

Index type created as "TABLE OF XMLTYPE?"

KnightOfBlueArmor Newbie
Currently Being Moderated

I have a crazy question.

Is it possible to create a structured or unstructured index on a type in Oracle 11.2.0.3 created as:

CREATE TYPE mytype AS TABLE OF XMLTYPE?
  • 1. Re: Index type created as "TABLE OF XMLTYPE?"
    odie_63 Guru
    Currently Being Moderated

    You're right, crazy question

    An index, whatever its type, is designed to work on a relational or object instance column, not on a class definition.

     

    I don't know what you had in mind, but to give some sense out of your question you could imagine doing something like this :

    - create the type definition

    - create a table to hold instance of this type

    - create the index on the nested table column

     

    CREATE TYPE mytype AS TABLE OF XMLTYPE;
    CREATE TABLE mytable (myobj mytype)
      NESTED TABLE myobj STORE AS mytable$nt
      (
        XMLTYPE COLUMN column_value STORE AS SECUREFILE BINARY XML
      )
    ;
    CREATE INDEX mytable_uxi ON mytable$nt(column_value)
    INDEXTYPE IS xdb.xmlindex ;
    
    
  • 2. Re: Index type created as "TABLE OF XMLTYPE?"
    KnightOfBlueArmor Newbie
    Currently Being Moderated

    That's fine.  I was using the type to "simulate" JDBC batching for a stored procedure passing XMLTypes (since the JDBC driver doesn't optimize batching for CallableStatements).  I had a feeling that was what the answer was.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points