Strange query - Oracle Text problems?

[yit...@yahoo.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

Copyright (c) 1982, 2005, Oracle. All rights reserved.

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

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

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

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)

[BicycleRepairman]

Given your setup, I don't understand what "WHERE 'FILE' IS NULL" means.
I would guess that you mean to put 'where search_col is null' ????

If you think about it, "WHERE 'FILE' IS NULL" equates to 'where the
character string f-i-l-e is null (obviously that never happens), so the
optimizer eliminates it.
'where :var is null' has to be evaluated, though -- you know the var is
'FILE', but the optimizer doesn't.

hth

[yit...@yahoo.com]

I included the example with "WHERE 'FILE' IS NULL" (the second query
below) just to show that in this case, the query proceeds efficiently.

However, in the case where I say "WHERE :the_filter IS NULL" (the third
query below), given :the_filter = 'FILE', the query does not proceed
efficiently.

Yes, "WHERE :the_filter IS NULL" has to be evaluated, but why is it
doing a full table scan?

[BicycleRepairman]

Your message subject is correct -- "Strange Query" -- but not in the
way you are expecting.

Think about your where clause for a minute.

Oracle is expecting

where {column name} is NULL

you are changing it to

where {string literal} is null
(that is, not the FILE column that doesn't exist in your sample schema,
but the letters 'file')

I don't believe this is what you are trying to ask. A more likely query
is:
SELECT COUNT(*)
FROM FOO
WHERE search_col IS NULL OR CONTAINS(search_col,:the_filter) > 0;

with which I have had better luck.

If, for some reason, you are really trying to determine whether the
user entered in one of your column names as their search expression,
then you have a different problem with an entirely different approach
-- one that doesn't have anything to do with Oracle Text.

[yit...@yahoo.com]

That is precisely what I'm trying to ask! To elaborate, what I've
posted is a simplified version of a query (still simplified) of the
sort:

SELECT *
FROM articles
WHERE (:title_filter IS NULL OR CONTAINS(title,:title_filter) > 0)
AND (:abstract_filter IS NULL OR CONTAINS(abstract,:abstract_filter) >
0)
AND (:publication_filter IS NULL OR
CONTAINS(publication,:publication_filter) >0)
...

where title_filter, abstract_filter, and publication_filter are strings
containing search terms for title, abstract, and publication
respectively. These are input parameters to my procedure.

What I'm saying is return the records where a title filter is not
specified or (it's specified) the title contains the filter criteria.
And so on.

So if title_filter = NULL, abstract_filter = 'economy', and
publication_filter = NULL, then I get the rows that have 'economy' in
the abstract column.

I hope this explanation is clear.

[BicycleRepairman]

Now we are getting somewhere!
So -- can you agree with me that this where clause does not do what you
want:
WHERE (:title_filter IS NULL OR CONTAINS(title,:title_filter) > 0))
that is, when Oracle grabs it (with a filter var of 'fubar') it parses
the clause as
WHERE ('fubar' IS NULL OR CONTAINS(title,'fubar') > 0))
or if the filter var is null
WHERE ('' IS NULL OR CONTAINS(title,'') > 0))
in the first case, 'fubar' is null is always false, so the contains
clause result pertains.
in the second case, you should get an oracle text error
[CONTAINS(title,'') > 0 does not make sense] As far as I can tell, you
must pass a value into the search expression parameter of the contains
clause. Try your example with :the_filter set to NULL and see what
happens.

You have to eliminate unnecessary contains clauses from the where
clause as you prepare the where clause. In doing that, you can simply
OR the results of the contains clauses.

hth