Re: Number of rows in a cursor

Darin M Strait (sg84p772@dunx1.ocs.drexel.edu) wrote: : I'm trying to find the number of rows contained in a cursor. : : [ stuff deleted ] I have some thoughts about the formulation of your question. (assuming a multiprogramming system) Unless an exclusive lock is placed on the table on which the cursor is defined, you can not know in advance how many rows will be fetched by the cursor, since other users can delete rows from the "active set" before you have the change of fetching them; also new rows can be inserted in the "active set" by other users after the cursor was opened. If a scroll cursor is used, in which case - most frequently - the active set is implemented as a temporary table, the rows are usually placed into the temporary table one by one the first time they are fetched. If the temporary table is filled immediately after opening the cursor (i.e. when a temporary table is created to process the query), then of course, the number of rows in the "active set" (which is a snapshot of the data at one moment) can be counted, but then there is the problem of stale data. Even if the isolation level is set to "Repeatable Read", rows of the "active set" can have changed in the actual table, before you fetch them the first time, but you won't see the changes. (After the first fetch, the rows can't change anymore because of the locks); Here I wonder what is the use of setting "Repeatable Read" isolation, if you don't care about the data being stale the first time you fetch them, why wouldn't you let them change (in the actual table) in between 2 fetches; you won't see the changes anyway (since the changes are not propagated to the temporary table; at least, this is what I gathered from the manual). I would very much appreciate further comments on this topic. ----------------------------------------------------------- Ria Vandenberghe Ria.Vandenberghe@rug.ac.be UNIVERSITY OF GHENT (BELGIUM) - Computer Science Laboratory Technologiepark-Zwijnaarde 9, B-9052 ZWIJNAARDE, BELGIUM tel: +32/9/264.55.10 fax: +32/9/264.58.42