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 Fri Dec 01 2006 - 14:50:50 CST
