Re: Problem with XMLType indexing. Please confirm

From: Michael O'Shea <michael.oshea_at_tessella.com>
Date: 3 Aug 2005 11:50:37 -0700
Message-ID: <1123095037.386404.149890@f14g2000cwb.googlegroups.com>

Hello Ko. I have attempted to look into this behaviour for you. I pasted your script verbatim into 10g release 10.1.0.2.0 personal on Windows XP. The behaviour you observe in 9i is not reproducible in 10g.  Full details are included in the trace below. I suggest you look at metalink for the bug fix.
Kind regards
Mike

TESSELLA Michael.OShea_at_tessella.com

__/__/__/ Tessella Support Services plc
__/__/__/ 3 Vineyard Chambers, ABINGDON, OX14 3PX, England
__/__/__/ Tel: (44)(0)1235-555511 Fax: (44)(0)1235-553301

http://www.tessella.com Registered in England No. 1466429

SQL>
SQL> drop table bug;

Table dropped.

SQL>
SQL> create table bug ( doc_id NUMBER, data_char xmltype );

Table created.

SQL> insert into bug values( 1, XMLType(   2 '<document>

  3     <ti>Test 1</ti>
  4     <py>2002</py>

  5 </document>' ) );

1 row created.

SQL>
SQL> insert into bug values( 2, XMLType(   2 '<document>

  3     <ti>Test 2</ti>
  4     <py>2002</py>

  5 </document>' ) );

1 row created.

SQL>
SQL> insert into bug values( 3, XMLType(   2 '<document>

  3     <ti>Test 3</ti>
  4     <py>2003</py>

  5 </document>' ) );

1 row created.

SQL>
SQL> insert into bug values( 4, XMLType(   2 '<document>

  3     <ti>Test 4</ti>
  4     <py>2002</py>

  5 </document>' ) );

1 row created.

SQL>
SQL> insert into bug values( 5, XMLType(   2 '<document>

  3     <ti>Test 5</ti>
  4     <py>2003</py>

  5 </document>' ) );

1 row created.

SQL>
SQL> create index my_idx on bug(data_char) indextype is ctxsys.context;

Index created.

SQL>
SQL> select count( doc_id ) from bug where   2 existsNode(data_char,'//py[.>"2002"]') > 0 OR contains(data_char,'2
  3 inpath(//ti)' ) > 0;

COUNT(DOC_ID)

---

            3

SQL>
SQL> select count( doc_id ) from bug where contains(data_char,'2   2 inpath(//ti)' ) > 0 OR existsNode(data_char,'//py[.>"2002"]') > 0;

COUNT(DOC_ID)

---

            3

SQL>
SQL> create index my_x_idx on bug(data_char) indextype is ctxsys.CTXXPATH;

Index created.

SQL>
SQL> select count( doc_id ) from bug where   2 existsNode(data_char,'//py[.>"2002"]') > 0 OR contains(data_char,'2
  3 inpath(//ti)' ) > 0;

COUNT(DOC_ID)

---

            3

SQL>
SQL> select count( doc_id ) from bug where contains(data_char,'2   2 inpath(//ti)' ) > 0 OR existsNode(data_char,'//py[.>"2002"]') > 0;

COUNT(DOC_ID)

---

            3

SQL> SELECT *
  2 FROM V$VERSION; BANNER

---

Personal Oracle Database 10g Release 10.1.0.2.0 - Production

PL/SQL Release 10.1.0.2.0 - Production

CORE 10.1.0.2.0 Production

TNS for 32-bit Windows: Version 10.1.0.2.0 - Production

NLSRTL Version 10.1.0.2.0 - Production

SQL> SPOOL OFF Ko van der Sloot wrote:
> Hello,
> i posted this a few months ago on comp.databases.oracle.misc, but got no
> reaction at all. So here is a retry on comp.databases.oracle.server
>
> Below you'l find a self contained script, which, after adding a
> ctxsys.CTXXPATH index, produces very strange results.
> i.c: (A OR B) gives 4 hits where (B OR A) gives 5 hits.
>
> It's not clear to me if this is due to some wrong ideas about XMLType or
> a misbeahaving of Oracle.
> I would be very pleased if some of your good people try the script on
> various Orcacle versions and report their findings.
>
> Our Orcale version:
> Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
> With the Partitioning, OLAP and Oracle Data Mining options
> JServer Release 9.2.0.4.0 - Production
>
> We get the following counts out of this script:
> 3, 3, 3, 5
> The last number is unbelievable WRONG!
>
> thanks for your help
> Ko vd Sloot
> Tilburg University
>
> here is the script: (maybe needs some reformating)
> ============================================================
> drop table bug;
>
> create table bug ( doc_id NUMBER, data_char xmltype );
> insert into bug values( 1, XMLType(
> '<document>
> <ti>Test 1</ti>
> <py>2002</py>
> </document>' ) );
>
> insert into bug values( 2, XMLType(
> '<document>
> <ti>Test 2</ti>
> <py>2002</py>
> </document>' ) );
>
> insert into bug values( 3, XMLType(
> '<document>
> <ti>Test 3</ti>
> <py>2003</py>
> </document>' ) );
>
> insert into bug values( 4, XMLType(
> '<document>
> <ti>Test 4</ti>
> <py>2002</py>
> </document>' ) );
>
> insert into bug values( 5, XMLType(
> '<document>
> <ti>Test 5</ti>
> <py>2003</py>
> </document>' ) );
>
> create index my_idx on bug(data_char) indextype is ctxsys.context;
>
> select count( doc_id ) from bug where
> existsNode(data_char,'//py[.>"2002"]') > 0 OR contains(data_char,'2
> inpath(//ti)' ) > 0;
>
> select count( doc_id ) from bug where contains(data_char,'2
> inpath(//ti)' ) > 0 OR existsNode(data_char,'//py[.>"2002"]') > 0;
>
> create index my_x_idx on bug(data_char) indextype is ctxsys.CTXXPATH;
>
> select count( doc_id ) from bug where
> existsNode(data_char,'//py[.>"2002"]') > 0 OR contains(data_char,'2
> inpath(//ti)' ) > 0;
>
> select count( doc_id ) from bug where contains(data_char,'2
> inpath(//ti)' ) > 0 OR existsNode(data_char,'//py[.>"2002"]') > 0;
Received on Wed Aug 03 2005 - 13:50:37 CDT