1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Problem with CTXXPATH index

Discussion in 'SQL PL/SQL' started by lcavina, Jun 29, 2009.

  1. lcavina

    lcavina Guest

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

    I created this table

    Code (Text):
    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 (Text):
    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 (Text):
    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 (Text):
    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..