Help!! Even stranger query...

From: <yitbsal_at_yahoo.com>
Date: 24 Nov 2006 09:18:13 -0800
Message-ID: <1164388693.268405.213810@f16g2000cwb.googlegroups.com>

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

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

1 row selected.
Elapsed: 00:00:01.17 Received on Fri Nov 24 2006 - 11:18:13 CST