Re: Simple Oracle Query Taking Too Long

On Wed, 6 Sep 2006 13:49:58 -0700, "Bryan Hunter" <bryan_at_exitexchange.com> wrote:

>The table that I am migrating the data to is not relivent to the problem, so
>I did not feel that the number of rows or indexing was important just the
>process that I was performing.
>
>I did copy a portion of the data to a new table as suggested with no
>indexing and selecting where rownum = 1, still took almost 1 minute.

If you would have run explain plan on that statement (which you obviously didn't )
you would have seen
count stopkey
  full table scan
    <your table>

Which means *ALL* rows are brought into buffer cache, and only 1 of them is transferred to your client.

>
>The original table has initial extent of 64kb and a 1% increase with
>unlimited extents
>
>I do not know if I agree with the statement that select the first 1000 rows
>of a table will result in a different 100 0rows every time as suggested by G
>Quesnel. I would appreciate any comments on this also.
>
>

They would not necessarily always be the same, unless you had exclusively locked the table.
You assume too easily none of the affected blocks are on the free list for the table. If however blocks are on the free list, and you didn't lock your table, other sessions *can* insert records, and *those* *will* be selected by your delete statement.

--
Sybrand Bakker, Senior Oracle DBA