Re: Help!! Even stranger query...

From: Michael O'Shea <michael.oshea_at_tessella.com>
Date: 24 Nov 2006 12:37:09 -0800
Message-ID: <1164400629.466844.197680@45g2000cws.googlegroups.com>

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(*)

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(*)

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 Nov 24 2006 - 14:37:09 CST