Re: Simple Oracle Query Taking Too Long

Charles Hooper,

I believe that your analysis is most likely the culprit, because the table was originally 83 million rows and is now down to 62 million rows. I remember in watching the number of rows selected per minute and it started gradually getting slower. So in essence rownum 1 is really 21 million rows into the table.

I do believe that the data is being transferred correctly over just using the rownum <=1000 though. The table that the data is being transferred to has a unique key on it that does not allow duplicate data. So if the select put the data in there but the delete deleted different data, then eventually the select would grab that data and try reinserting it and fail with unique constraint violation. That has not happened yet with over 20 million records.

Daniel Morgan

Oracle was not working on anything else at the time I tried it, I also tried it multiple times later on.

Could you or anyone else give me a recommendation for extent size and pctincrease?

I am guessing that you are recommending that all tablespaces be locally managed?

The same select on other tables of similar size takes about .03 seconds

Thanks for all of the help, I am rewriting the entire procedure to use the index I applied and stop using rownum. Then trash the temp table when I am done. Received on Thu Sep 07 2006 - 14:30:35 CDT