Database Tuning (MOSC)

MOSC Banner

how to speed up a select *

edited Jul 20, 2009 10:53AM in Database Tuning (MOSC) 18 commentsAnswered
 Hello

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?

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center