Re: Strange query - Oracle Text problems?

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

yitbsal_at_yahoo.com wrote:
> 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)
Received on Wed Nov 22 2006 - 07:02:08 CST