Re: 2.5 billion row table scan...

You can increase your db_block_buffers possibly, with even 4g, but take advantage of the processors that you have. Use hints, or set the parallel clause on the table. See below.

alter table xxx parallel(degree 4 instances 1); -- leave at least 2 for other things.

or just use a hint.

select /*+ full(a) parallel(a,4,1) */ count(*) from xxx a; -- Parallel only works on full scans either indexes or tables

If you do have an index

select /*+ index_ffs(a) parallel(a,4,1) */ count(*) from xxx a;

Both should improve, but most of all do O/S striping, and datafile striping. Look under I/O tuning in the oracle tuning guide to make sure that the O/S stripe is big enough for the table. No less than 2 * db_block_size * db_file_multiblock_read_count. Also, be careful not to increase db_file_multiblock_read_count too high, it causes the optimizer to do full scans on some smaller tables.

I have a table that isn't isn't quite as big, but I get a lot better response time than that. I seem to recall that it takes less than 30 minutes for 500M rows.

--
Robert Fazio, Oracle DBA
rfazio_at_home.com
remove nospam from reply address
http://24.8.218.197/
<sergey_s_at_my-deja.com> wrote in message news:8hgv5a$gla$1_at_nnrp1.deja.com...


>

>

> Due to time constraints, we may have to stick with scanning 2.5 billion

> row table (and figure out another access path later). It's partitioned

> (each partition about 50-60 mil rows). Current memory is at 4 Gig, and

> there are six processors (Sun machine). We will probably double the

> memory soon and add two more processors.

>

> With it's current configuration, however, it was still cheaper to scan

> the table than use any indexes, and it takes roughly 18 hours.

>

> Can you suggest anything to speed up the scan?

>

> Thank you!

>

> Sergey

>

>

> Sent via Deja.com http://www.deja.com/

> Before you buy.