Getting original definition of function based index on XML column
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;
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;
0