Re: Strange query - Oracle Text problems?

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 wrote:
> 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 - 08:38:17 CST