Re: Simple Oracle Query Taking Too Long

Sybrand Bakker wrote:
> 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

Interesting discussion.

If I recall correctly, Jonathan Lewis mentioned something about an optimization in Oracle dealing with the use of ROWNUM=1 or ROWNUM<100 in his "Cost-Based Oracle Fundamentals" book that indicated that Oracle is smart enough to recognize that it can stop an in-process full table scan once X number of rows are read from the table. Searching the archives of comp.database.oracle.server for "ROWNUM & server performance" will help find one of his posts that is seemingly related to the subject.

Assume that the blocks in your table are stored as follows (greatly simplifying of course: 1 is a full/nearly full block, 0 is an empty/nearly empty block, and H is the high water mark for the table):

11111111111111111111111111111111111111111111111111111H

Assuming that you can without fail SELECT and DELETE the same 1000 rows without using an ORDER by clause, you copy the first 1000 rows into a new table and delete the first 1000 rows. The blocks in your table may now look like this:

00000011111111111111111111111111111111111111111111111H

If Oracle was smart enough to stop the full table scan after the first 1000 rows were read before, it would be able to stop almost immediately. Now the position of the first 1000 rows has shifted due to the delete. During the next read and delete of 1000 rows, in the full table scan Oracle must read through all of the blocks (including those that once held the deleted rows) until the first 1000 rows are read - since it now must read twice the number of blocks, it will _probably_ take a bit longer to complete. The blocks in the table may now look like this:

00000000000011111111111111111111111111111111111111111H
00000000000000000011111111111111111111111111111111111H
00000000000000000000000011111111111111111111111111111H

And the process continues, with each SELECT taking longer to complete. I could, of course, be wrong in the above assumptions.

As strongly suggested by others in the group, you simply cannot guaranty that you will select and delete the same 1000 rows using ROWNUM alone. ROWNUM is a pseudo column that affected by an ORDER BY clause - the first row returned will always be ROWNUM=1, the second ROWNUM=2, etc.

I would suggest fixing the extent size for the tablespace so that the initial extent size is appropriate, and the 1% increase is set to 0. Then create a new table by selecting * from the current table, rename the old table, then rename the new table as necessary.

Of course, another possibility are waits caused by writing the dirty blocks in the buffer cache, which in turn first require writes to the redo logs, in order to make room for the blocks read by the full table scan...

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Wed Sep 06 2006 - 20:18:32 CDT