Skip to Main Content

Creating index using XMLIndex, changes the result set when querying

496895Mar 29 2010 — edited Mar 31 2010
Hi there,

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.

Keith
Comments
Post Details
Added on Mar 29 2010
4 comments
1,485 views