how to speed up a select *
I am using oracle 10.2.0.3 We have a new table with about 500mil rows and size about 35-40G. The app team needs to dump that table into a flat file for future processing. They do that via select * from table. This command has been running for several hours now. I have been looking at the table and it has about 5 million blocks. My block size is 8K and multiblock read count is 32. I think that this is the reason this select is taking so slow. When I run select count(1) from table. it takes about a minute.
Would creating a new tablespace for this table with block_Size set to 32K help to speed up this SQL?