Re: query enhancement

"keeling" <jkeeling77_at_yahoo.com> wrote in message news:1181928165.165185.129550_at_a26g2000pre.googlegroups.com...
>I am attempting to enhance a query so as to avoid a full table scan.
> The query, as it now stands, tests for the presence of 'null' in one
> column; the return of 1 or greater rows will satisfy my test,
> therefore I'd like the query to stop after finding the first
> occurrence of null. present query is as follows:
>
> select count(*) from tableX where columnX = null.
>
> Any suggestions would be greatly appreciated.
>

Try a function based index. Example and discussion follow. CREATE TABLE testcase (THING_1 NUMBER not null, THING_2 NUMBER);

• Insert 1,000,000 rows; note that 1% are null in the second column

declare
l_thing_1 pls_integer := 0;
l_thing_2 pls_integer;

begin

while l_thing_1 < 100000 loop
l_thing_2 := l_thing_1;

if mod(l_thing_1,100) = 0 then
l_thing_2 :=null;
end if;

insert into testcase (thing_1, thing_2) values (l_thing_1, l_thing_2);

l_thing_1 := l_thing_1 + 1;
end loop;

commit;
end;

• Gather stats; Look for nulls in the second column; check out the explain plan

dbms_stats.gather_table_stats (ownname => user, tabname => 'TESTCASE',cascade => true);

select count(*) from testcase where thing_2 is null;

SELECT STATEMENT, GOAL = CHOOSE Cost=201 Cardinality=1 Bytes=5  SORT AGGREGATE Cardinality=1 Bytes=5   TABLE ACCESS FULL Object name=TESTCASE Cost=201 Cardinality=10000 Bytes=50000

Executes in 0.531 seconds

• Create the function based index, gather stats again and rerun the query with a predicate that will exploit the function based index

create index null_function_idx on testcase (nvl(thing_2,-1));

begin
dbms_stats.gather_table_stats (ownname => user, tabname => 'TESTCASE',cascade => true);
end;

select count(*)
from testcase
where nvl(thing_2,-1) = -1

SELECT STATEMENT, GOAL = CHOOSE Cost=25 Cardinality=1 Bytes=5  SORT AGGREGATE Cardinality=1 Bytes=5   INDEX RANGE SCAN Object name=NULL_FUNCTION_IDX Cost=25 Cardinality=10001 Bytes=50005

Executes in 0.032 seconds Received on Fri Jun 15 2007 - 14:54:31 CDT