XML Database (MOSC)

MOSC Banner

Getting original definition of function based index on XML column

edited Jan 17, 2011 2:38AM in XML Database (MOSC) 1 commentAnswered
We are using Oracle 10.2.0.1 and 10.2.0.2
I am using a table with one of its columns of XMLType based on an XML schema.
I have a function based index that uses the column.

As an example;

CREATE TABLE TEST_MO3
(
  TEST_ID   INTEGER      NOT NULL,
  TEST_XML  SYS.XMLTYPE
)
TABLESPACE users
XMLTYPE "TEST_XML" STORE AS CLOB
XMLSCHEMA "motest3.xsd"
ELEMENT "TESTS"
/


Prompt Index
CREATE INDEX TEST_MO3_NUM_IDX ON TEST_MO3 (
                SUBSTR( EXTRACTVALUE (TEST_XML, '/TESTS/*/Number'),1,4),
                SUBSTR( EXTRACTVALUE (TEST_XML, '/TESTS/*/Series'),1,1),
                SUBSTR( EXTRACTVALUE (TEST_XML, '/TESTS/*/Year'),1,4))
TABLESPACE users
/

View user_ind_expressions shows the expresions as;

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center