Creating index using XMLIndex, changes the result set when querying
496895Mar 29 2010 — edited Mar 31 2010
I was tasked with populating an Oracle 11Gr2 database with data from 800,000 XML files. I used the Oracle documentation, and in about 2 days I had the data loaded into a simple table.
Queries of this table were SLOW, so this morning I read about using XMLIndex to index the table... I did this, and it works great -- the queries got a LOT faster.
However -- a "column" from my XML data is supposed to return values of 2000-4000 characters. It worked fine, until I created my index -- after I created the XMLIndex, the data is being truncated to 80 chars in my query.
CREATE TABLE TEST_XMLTABLE OF XMLType; /* XML data has a "node" called TEXT which can be up to 4000 chars */
SELECT to_number( extractValue(OBJECT_VALUE, 'newsitem/@itemid')) as ITEM_ID, Text.text <----- value here will contain full text body up to 4k chars
FROM TEST_XMLTABLE KTX,
XMLTABLE('/newsitem' PASSING ktx.Object_Value columns "TEXT" clob PATH 'text') TEXT
/* now create the index */
CREATE INDEX IDX_xmlindex ON TEST_XMLTABLE (OBJECT_VALUE) INDEXTYPE IS XDB.XMLIndex;
Now, run my query again, and TEXT.TEXT is truncated to only 80 chars!?!?!
I'm not sure what to do next... my users need the full text, so for now I have dropped the XMLIndex... the queries are slow, but they are getting all the data.
Thanks in advance for any help or ideas.