I have a large table data that has an ascending counter the column counter for each row. Assuming that my program is working correctly, counter should increase by 20 units per row.

Therefore, for each table the query Select (Max(counter)-Min(counter))/(Max(ROWID)-1) should return 20 if the program works correctly. If the program drops any rows, the query would return > 20.

As I know that Max and Min are in rows 0 and N, is there a better way to run this search without requiring the processor to search the entire table for the max value?

CodePudding user response：

What you could do is maintain a table with a single row that keeps a record of the lowest and highest values.

You could maintain this table using

After the deletions (the lowest and highest rows being included in the deletions) then counterhighlow is :-

The trigger monitoring table includes 10004 rows, the first 10000 for the inserts e.g :-

....

The last 4 rows, the deletions being :-