Yup, it's official. Upgrading to 10 makes the difference.
yitbsal_at_yahoo.com wrote:
> Thanks for your reply. I guess I have to go to Oracle with this.
>
>
> Michael O'Shea wrote:
> > yitbsal_at_yahoo.com wrote:
> > > >From the script and execution below, see that:
> > >
> > > SELECT COUNT(*)
> > > FROM FOO
> > > WHERE CONTAINS(search_col1,'FILE') > 0
> > > OR (:the_filter = 0 AND CONTAINS(search_col2,'FILE') > 0);
> > >
> > > returns a smaller count than
> > >
> > > SELECT COUNT(*)
> > > FROM FOO
> > > WHERE CONTAINS(search_col1,'FILE') > 0;
> > >
> > > How can this happen??
> > >
> > >
> > > script
> > > -----------
> > >
> > > SET ECHO ON;
> > > SET TIMING ON;
> > > DROP TABLE FOO;
> > > CREATE TABLE FOO (search_col1 VARCHAR2(100), search_col2
> > > VARCHAR2(100));
> > > -- Loads of dummy data
> > > INSERT INTO FOO (search_col1,search_col2)
> > > SELECT '<html>' || OBJECT_NAME || ' ' || OBJECT_TYPE || '</html>',
> > > '<html>' || SUBOBJECT_NAME || ' ' || OWNER || '</html>'
> > > FROM ALL_OBJECTS WHERE ROWNUM <= 20000;
> > > COMMIT;
> > > CREATE INDEX I1 ON FOO(search_col1) INDEXTYPE IS CTXSYS.CONTEXT
> > > PARAMETERS(' section group ctxsys.html_section_group');
> > > CREATE INDEX I2 ON FOO(search_col2) INDEXTYPE IS CTXSYS.CONTEXT
> > > PARAMETERS(' section group ctxsys.html_section_group');
> > >
> > > variable the_filter NUMBER;
> > > begin
> > > :the_filter := 0;
> > > end;
> > > /
> > >
> > > SELECT COUNT(*)
> > > FROM FOO
> > > WHERE CONTAINS(search_col1,'FILE') > 0;
> > >
> > > SELECT COUNT(*)
> > > FROM FOO
> > > WHERE CONTAINS(search_col1,'FILE') > 0
> > > OR (:the_filter = 0 AND CONTAINS(search_col2,'FILE') > 0);
> > > ------------
> > >
> > > run
> > > ------
> > > SQL> SET TIMING ON
> > > SQL> DROP TABLE FOO
> > > Table dropped.
> > > Elapsed: 00:00:01.75
> > > SQL> CREATE TABLE FOO (search_col1 VARCHAR2(100), search_col2
> > > VARCHAR2(100))
> > > Table created.
> > > Elapsed: 00:00:00.09
> > > SQL> -- Loads of dummy data
> > > SQL> INSERT INTO FOO (search_col1,search_col2)
> > > SELECT '<html>' || OBJECT_NAME || ' ' || OBJECT_TYPE || '</html>',
> > > '<html>' || SUBOBJECT_NAME || ' ' || OWNER || '</html>'
> > > FROM ALL_OBJECTS WHERE ROWNUM <= 20000
> > > 20000 rows created.
> > > Elapsed: 00:00:02.04
> > > SQL> COMMIT
> > > Commit complete.
> > > Elapsed: 00:00:00.09
> > > SQL> CREATE INDEX I1 ON FOO(search_col1) INDEXTYPE IS CTXSYS.CONTEXT
> > > PARAMETERS(' section group ctxsys.html_section_group')
> > > Index created.
> > > Elapsed: 00:00:02.14
> > > SQL> CREATE INDEX I2 ON FOO(search_col2) INDEXTYPE IS CTXSYS.CONTEXT
> > > PARAMETERS(' section group ctxsys.html_section_group')
> > > Index created.
> > > Elapsed: 00:00:01.14
> > > SQL> variable the_filter NUMBER
> > > SQL> begin
> > > :the_filter := 0;
> > > end;
> > > PL/SQL procedure successfully completed.
> > > Elapsed: 00:00:00.09
> > > SQL> SELECT COUNT(*)
> > > FROM FOO
> > > WHERE CONTAINS(search_col1,'FILE') > 0
> > >
> > > COUNT(*)
> > > ----------
> > > 47
> > >
> > >
> > > 1 row selected.
> > > Elapsed: 00:00:00.14
> > > SQL> SELECT COUNT(*)
> > > FROM FOO
> > > WHERE CONTAINS(search_col1,'FILE') > 0
> > > OR (:the_filter = 0 AND CONTAINS(search_col2,'FILE') > 0)
> > >
> > > COUNT(*)
> > > ----------
> > > 2
> > >
> > >
> > > 1 row selected.
> > > Elapsed: 00:00:01.17
> >
> >
> >
> >
> > Hi, from your previous post I note you are using 9.2.0.6.0. I cannot
> > reproduce the problem using your script under 10.2.0.1.0 XE/Debian
> > Sarge x86. Script output included inline below.
> >
> > 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
> > www.tessella.com Registered in England No. 1466429
> >
> >
> > SQL> SET ECHO ON;
> > SQL> SET TIMING ON;
> > SQL> DROP TABLE FOO;
> >
> > Table dropped.
> >
> > Elapsed: 00:00:03.13
> > SQL> CREATE TABLE FOO (search_col1 VARCHAR2(100), search_col2
> > 2 VARCHAR2(100));
> >
> > Table created.
> >
> > Elapsed: 00:00:00.09
> > SQL> -- Loads of dummy data
> > SQL> INSERT INTO FOO (search_col1,search_col2)
> > 2 SELECT '<html>' || OBJECT_NAME || ' ' || OBJECT_TYPE || '</html>',
> > 3 '<html>' || SUBOBJECT_NAME || ' ' || OWNER || '</html>'
> > 4 FROM ALL_OBJECTS WHERE ROWNUM <= 20000;
> >
> > 5292 rows created.
> >
> > Elapsed: 00:00:02.01
> > SQL> COMMIT;
> >
> > Commit complete.
> >
> > Elapsed: 00:00:00.01
> > SQL> CREATE INDEX I1 ON FOO(search_col1) INDEXTYPE IS CTXSYS.CONTEXT
> > 2 PARAMETERS(' section group ctxsys.html_section_group');
> >
> > Index created.
> >
> > Elapsed: 00:00:03.12
> > SQL> CREATE INDEX I2 ON FOO(search_col2) INDEXTYPE IS CTXSYS.CONTEXT
> > 2 PARAMETERS(' section group ctxsys.html_section_group');
> >
> > Index created.
> >
> > Elapsed: 00:00:01.57
> > SQL>
> > SQL>
> > SQL> variable the_filter NUMBER;
> > SQL> begin
> > 2 :the_filter := 0;
> > 3 end;
> > 4 /
> >
> > PL/SQL procedure successfully completed.
> >
> > Elapsed: 00:00:00.01
> > SQL> SELECT COUNT(*)
> > 2 FROM FOO
> > 3 WHERE CONTAINS(search_col1,'FILE') > 0;
> >
> > COUNT(*)
> >
> > ----------
> >
> > 81
> >
> >
> > Elapsed: 00:00:00.43
> > SQL> SELECT COUNT(*)
> > 2 FROM FOO
> > 3 WHERE CONTAINS(search_col1,'FILE') > 0
> > 4 OR (:the_filter = 0 AND CONTAINS(search_col2,'FILE') > 0);
> >
> > COUNT(*)
> >
> > ----------
> >
> > 81
> >
> >
> > Elapsed: 00:00:01.45
> > SQL>
> > SQL> SELECT * FROM V$VERSION;
> >
> > BANNER
> > ----------------------------------------------------------------
> > Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product
> > PL/SQL Release 10.2.0.1.0 - Production
> > CORE 10.2.0.1.0 Production
> > TNS for Linux: Version 10.2.0.1.0 - Production
> > NLSRTL Version 10.2.0.1.0 - Production
> >
> > Elapsed: 00:00:00.02
> > SQL>
> > SQL>
> > SQL>
Received on Wed Dec 06 2006 - 10:00:48 CST
