Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: sum function force full table scan?

Re: sum function force full table scan?

From: RSH <RSH_Oracle_at_worldnet.att.net>
Date: Mon, 22 Apr 2002 19:35:59 GMT
Message-ID: <zCZw8.40351$Rw2.3109069@bgtnsc05-news.ops.worldnet.att.net>


You could use a function based index.

Apart from that, functions on columns for search purposes that have no, or just regular indices on them, will cause a full table scan.

Your version of Oracle may not support this feature.

RSH. "Ed Wong" <ewong_at_mail.com> 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.
>
> 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 - 14:35:59 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US