Hi all,
i'm using Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 on Windows.

I created this table

Code:
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:

Code:
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:

Code:
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:

Code:
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..