Re: sum function force full table scan?

In the SUM example you have to read the table, because LEN is not a column of the index. In that case it very likely quicker to do a full table scan than reading 1% of the table by the index. You can check this by forcing the use of the index by doing a

select /*+ index(table_a in_table_a_lastdate) */ sum(len) from table_a
where lastdate is not null;

I bet it will be slower than the full table scan.

Jaap.

On 22 Apr 2002 11:38:04 -0700, ewong_at_mail.com (Ed Wong) wrote:

>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 - 16:41:27 CDT