sum function force full table scan?

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.

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 - 13:38:04 CDT