Home » Server Options » Text & interMedia » Problem using CTXXPATH index (Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 on Windows)
Problem using CTXXPATH index [message #410644] Mon, 29 June 2009 08:29
orakevin
Messages: 1
Registered: June 2009
Location: Bologna
Junior Member
Hi all,
i'm using Oracle 10g R2 on Windows.

I created this table

create table PERSISTENT_COMPOSITION
(
  COMPOSITION_ID NUMBER(19) not null,
  XML_CONTENT    SYS.XMLTYPE not null,
)


and filled it with more or less 1.000.000 records (that si 1.000.000 xml document loaded into XML_CONTENT).
Then first of all i tested it with a simple query just like the following:

SELECT *
  FROM PERSISTENT_COMPOSITION t
 WHERE existsNode(t.xml_content, '/composition/archetype_details/archetype_id[value="openEHR-EHR-COMPOSITION.composition_test.v1"]') = 1;


obtaining the expected result: 50,000 records found.
Now, in order to improve query performances, i created a CTXXPATH index as follows:

CREATE INDEX IDX#COMP_CTXXPATH ON PERSISTENT_COMPOSITION(XML_CONTENT) INDEXTYPE IS CTXSYS.CTXXPATH;


Then i tested the new performances using exactly the same query shown above...and here comes the problem: the query returns NO RESULT! No record was found! I looked at the query execution plan and it uses the created index IDX#COMP_CTXXPATH...but no record could be found...

I thought it could be a matter of namespace: in fact loaded xml documents have a xmlns set and so i changed the query as follows:

SELECT *
FROM persistent_composition t
WHERE existsNode(t.xml_content,
                  '/composition/archetype_details/archetype_id[value="openEHR-EHR-COMPOSITION.composition_test.v1"]',
		   'xmlns="this.is.an.xmlns.url.org/v1"') = 1


and surprise: i obtained my 50,000 results just like before BUT, looking at the query execution plan, the IDX#COMP_CTXXPATH index HASN'T BEEN USED!!!

I really don't understand why using the IDX#COMP_CTXXPATH i get no result....can someone help me?

Thank you very much

P.S: i tried using ANALYZE (both on index and on table), CTX_DDL.sync_index and CTX_DDL.optimize_index but got no result..
Previous Topic: Oracle Text error
Next Topic: A question about multi_lexer
Goto Forum:
  


Current Time: Thu Mar 28 14:34:37 CDT 2024