To delete records beyond 20 from a table.

Question

At any time, I want my table to display the latest 20 rows and delete the rest. I tried rownum > 20 but it said " 0 rows deleted" even when my table had 50 records.However, on triying rownum<20 - the first 19 records were deleted. Please help.

Answer 1

ROWNUM is a pseudo-column which is assigned 1 for the first row produced by the query, 2 for the next, and so on. If you say "WHERE ROWNUM > 20", no row will be matched - the first row, if there was one, would have ROWNUM=1, but your predicate causes it to reject it - therefore the query returns no rows.

If you want to query just the latest 20 rows, you'd need some way of determining what order they were inserted into the table. For example, if each row gets a timestamp when it is inserted, this would usually be pretty reliable (unless you get thousands of rows inserted every second).

For example, a table with definition MYTABLE(ts TIMESTAMP, mycol NUMBER), you could query the latest 20 rows with a query like this:

SELECT * FROM (
    SELECT ts, mycol FROM MYTABLE ORDER BY ts DESC
)
WHERE ROWNUM <= 20;

Note that if there is more than one row with exact same timestamp, this query may pick some rows non-deterministically if there are two or more rows tied for the 20th spot.

If you have an index on ts it is likely to use the index to avoid a sort, and Oracle will use stopkey optimisation to halt the query once it's found the 20th row.

If you want to delete the older rows, you could do something like this, assuming mycol is unique:

DELETE MYTABLE
WHERE mycol NOT IN (
    SELECT mycol FROM (
        SELECT ts, mycol FROM MYTABLE ORDER BY ts DESC
    )
    WHERE ROWNUM <= 20
);

The performance of this delete, if the number of rows to be deleted is large, will probably be helped by an index on mycol.