Strange query - Oracle Text problems?

From: <yitbsal_at_yahoo.com>
Date: 21 Nov 2006 11:01:39 -0800
Message-ID: <1164135699.474510.263010@m7g2000cwm.googlegroups.com>

Consider the following queries:

:the_filter := 'FILE';
(1)

SELECT COUNT(*)
FROM FOO
WHERE CONTAINS(search_col,:the_filter) > 0;

(2)

SELECT COUNT(*)
FROM FOO
WHERE 'FILE' IS NULL OR CONTAINS(search_col,:the_filter) > 0;

(3)

SELECT COUNT(*)
FROM FOO
WHERE :the_filter IS NULL OR CONTAINS(search_col,:the_filter) > 0;

(1) and (2) use the text index and run normally, but (3) does a full
table scan, but I don't understand why.

The reason behind my using a query of the form (3) is to say 'keep the record if the filter is null or the record contains the filter'. I'm using the filter as a switch, and (3) is actually a simplified form of a complex query with several tables and filters. How can I do this without the query using a full table scan?

Below is the script and a record of the full session:

SET ECHO ON;
SET TIMING ON;
DROP TABLE FOO;
CREATE TABLE FOO (search_col VARCHAR2(100)); -- Loads of dummy data
INSERT INTO FOO (search_col) SELECT OBJECT_NAME || ' ' || OBJECT_TYPE FROM ALL_OBJECTS WHERE ROWNUM <= 20000;
COMMIT;
CREATE INDEX I1 ON FOO(search_col) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS(' section group ctxsys.html_section_group'); variable the_filter varchar2(100);
begin
:the_filter := 'FILE';
end;
/
SET AUTOTRACE ON EXPLAIN;
SELECT COUNT(*)
FROM FOO
WHERE CONTAINS(search_col,:the_filter) > 0; SELECT COUNT(*)
FROM FOO
WHERE 'FILE' IS NULL OR CONTAINS(search_col,:the_filter) > 0; SELECT COUNT(*)
FROM FOO
WHERE :the_filter IS NULL OR CONTAINS(search_col,:the_filter) > 0;

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production With the Partitioning, Oracle Label Security, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Nov 21 11:21:22 2006

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production With the Partitioning, Oracle Label Security, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production

SQL> SET TIMING ON;
SQL> DROP TABLE FOO; Table dropped.

Elapsed: 00:00:00.73
SQL> CREATE TABLE FOO (search_col VARCHAR2(100));

Table created.

Elapsed: 00:00:00.03
SQL> -- Loads of dummy data
SQL> INSERT INTO FOO (search_col) SELECT OBJECT_NAME || ' ' || OBJECT_TYPE
2 FROM ALL_OBJECTS WHERE ROWNUM <= 20000;

20000 rows created.

Elapsed: 00:00:01.92
SQL> COMMIT; Commit complete.

Elapsed: 00:00:00.01
SQL> CREATE INDEX I1 ON FOO(search_col) INDEXTYPE IS CTXSYS.CONTEXT 2 PARAMETERS(' section group ctxsys.html_section_group');

Index created.

Elapsed: 00:00:10.76
SQL> variable the_filter varchar2(100);
SQL> begin
2 :the_filter := 'FILE';
3 end;
4 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
SQL> SET AUTOTRACE ON EXPLAIN;
SQL> SELECT COUNT(*)
2 FROM FOO
3 WHERE CONTAINS(search_col,:the_filter) > 0;

COUNT(*)

Elapsed: 00:00:00.07

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=59) 1 0 SORT (AGGREGATE)
2 1 DOMAIN INDEX OF 'I1' (Cost=0 Card=1 Bytes=59)

SQL> SELECT COUNT(*)
2 FROM FOO
3 WHERE 'FILE' IS NULL OR CONTAINS(search_col,:the_filter) > 0;

COUNT(*)

Elapsed: 00:00:00.08

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=59) 1 0 SORT (AGGREGATE)
2 1 DOMAIN INDEX OF 'I1' (Cost=0 Card=1 Bytes=59)

SQL> SELECT COUNT(*)
2 FROM FOO
3 WHERE :the_filter IS NULL OR CONTAINS(search_col,:the_filter) > 0;

COUNT(*)

Elapsed: 00:00:04.06

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=34482 Card=1 Bytes=5 9)

1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'FOO' (Cost=34482 Card=453 Bytes= 26727) Received on Tue Nov 21 2006 - 13:01:39 CST