i'm using Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 on Windows.
I created this table
and filled it with more or less 1.000.000 records (that si 1.000.000 xml document loaded into XML_CONTENT).
create table PERSISTENT_COMPOSITION
COMPOSITION_ID NUMBER(19) not null,
XML_CONTENT SYS.XMLTYPE not null,
Then first of all i tested it with a simple query just like the following:
obtaining the expected result: 50,000 records found.
FROM PERSISTENT_COMPOSITION t
WHERE existsNode(t.xml_content, '/composition/archetype_details/archetype_id[value="openEHR-EHR-COMPOSITION.composition_test.v1"]') = 1;
Now, in order to improve query performances, i created a CTXXPATH index as follows:
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...
CREATE INDEX IDX#COMP_CTXXPATH ON PERSISTENT_COMPOSITION(XML_CONTENT) INDEXTYPE IS CTXSYS.CTXXPATH;
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:
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!!!
FROM persistent_composition t
'xmlns="this.is.an.xmlns.url.org/v1"') = 1
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..