Re: Problem with XMLType indexing. Please confirm
- From: "Michael O'Shea" <michael.oshea@xxxxxxxxxxxx>
- Date: 3 Aug 2005 11:50:37 -0700
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@xxxxxxxxxxxx
__/__/__/ 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;
.
- Follow-Ups:
- Re: Problem with XMLType indexing. Please confirm
- From: Ko van der Sloot
- Re: Problem with XMLType indexing. Please confirm
- References:
- Problem with XMLType indexing. Please confirm
- From: Ko van der Sloot
- Problem with XMLType indexing. Please confirm
- Prev by Date: Re: Problem with XMLType attribibutes? please confirm
- Next by Date: Re: Problem with XMLType attribibutes? please confirm
- Previous by thread: Problem with XMLType indexing. Please confirm
- Next by thread: Re: Problem with XMLType indexing. Please confirm
- Index(es):
Relevant Pages
|
