Re: sum function force full table scan?

ewong_at_mail.com (Ed Wong) wrote in message news:<a5ae1554.0204221038.47ba6c7b_at_posting.google.com>...
> I have a 10 millions row table named table_a. The LASTDATE column is
> indexed and there is only 100k records has value in the LASTDATE
> column. I am trying to do a sum on the LEN column where LASTDATE is
> not null. It skips the index on LASTDATE and do a full table scan
> which takes 30 minutes. It only happen when I do a SUM function
> though. I understand that SUM operation may take a little longer but
> it shouldn't need to do a full table scan if I am only looking for
> 100k records(1% of the table). If I do other operations such as
> count(*) it uses index and returns the results in seconds. The
> following is the plan.

The reason it uses full table scan is that you sum the column which is not in the index. In short, when you do count(*), optimizer doesn't need any other column from the table other than lastdate. To do sum(len) it also needs len. It assumes that full scan is faster than combination of index scan and rowid table access. Try to analyze table and index in full. If it doesn't help, force the plan using hints. Actually, this is one of the cases when cost-based optimizer doesn't work good.

>
> SQL> select sum(len) from table_a where lastdate is not null; -- 30
> minutes
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=87491 Card=1 Bytes=6
> )
>
> 1 0 SORT (AGGREGATE)
> 2 1 PARTITION HASH (ALL)
> 3 2 TABLE ACCESS (FULL) OF 'TABLE_A' (Cost=8749
> 1 Card=212183 Bytes=1273098)
>
> SQL> select count(*) from table_a where lastdate is not null; -- 1
> second
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=102 Card=1 Bytes=2)
> 1 0 SORT (AGGREGATE)
> 2 1 INDEX (FAST FULL SCAN) OF 'IN_TABLE_A_LASTDATE' (NON-U
> NIQUE) (Cost=102 Card=212183 Bytes=424366)
>
> I am using CBO. Please help!
>
> ewong
Received on Mon Apr 22 2002 - 17:17:49 CDT