> The whole SELECT statements takes 25 minutes to finish
Do you mean it takes 25 minutes to return the first fetch of rows?
> Do you have any idea , what might be the problem????
But the DB is very well instrumented so that you can find out exactly what's going on.
For further information, see:
>The whole SELECT statements takes 25 minutes to finish
Are you waiting for *ALL* the rows to be returned ? How many rows is it ?
If you seeing only the first 'n' (e.g. 50 or 100) rows (e.g. in a tool like TOAD), that is not complete execution of the query. You need to scroll down to the last row to get the actual execution time for the "whole" select statement.
Hemant K Chitale
I use TOAD!
So there's your answer. Toad only fetchs a limited number of rows at a time. Then as you page down it fetches more.
Check your 'View => Options => Schema Browser => Data and Grids' and check the 'Limit grid fetch' value.
A SELECT statement only fetches that first small amount but the CTAS and COUNT have to fetch ALL of the data.