Document #: US-13254,EN ------------------------------------------------------------------------------ Major subject: data_mgmt Minor subjects: system_catalogs Keywords: tech_notes Abstract: INGRES Rowcounts - Equivalent to INGRES Release 6 Technical Note #16 or Release 5 note #39. Expert note: INGRES Rowcounts ================ Overview -------- The number of rows in each INGRES table is stored in the system cata- log named "iirelation" in the field "reltups". This field is accessed when you type "HELP TABLE your_table" and prints as the "Number of Rows" (referred to below as rowcount). o When is Rowcount Updated? o How Can Rowcount Be Wrong? o How To Find The Correct Rowcount o Summary When is Rowcount Updated? ------------------------- For performance reasons, a table's rowcount is not updated every time a row is appended or deleted. The three conditions which trigger updates to rowcount are the following: 1. a user exits normally from an INGRES session, the rowcount for every table appended to or deleted from during a session is updated. 2. than 100 rows is added or deleted to the table within a transaction. 3. the table is modified. After the rowcount is updated, all other INGRES users in that database will see the updated value of rowcount. How Can Rowcount Be Wrong? -------------------------- A table's rowcount can become incorrect due to either of the following: 1. If a user exits abnormally from an INGRES session (for example, a system crash), the rowcount will not be updated as they exit. Thus, exiting abnormally after doing APPENDs and DELETEs can cause rowcount to become incorrect. 2. If there are several users simultaneously updating the same database, then the rowcounts each user sees can be temporarily incorrect. How To Find The Correct Rowcount -------------------------------- There are 2 ways to find the exact number of rows in a table: 1. Count the number of rows in the table at a time when no current INGRES session is updating the table. In SQL issue a query such as: /* Prevent concurrent updating. Wait 10 secs for table lock.*/ SET LOCKMODE ON yourtable WHERE LEVEL=TABLE, TIMEOUT=10; SELECT COUNT(your_table.non_keyed_field) FROM your_table; In QUEL issue a query such as: RANGE OF t IS your_table /* Prevent concurrent updating. Wait 10 secs for table lock.*/ SET LOCKMODE ON your_table WHERE LEVEL=TABLE, TIMEOUT=10 RETRIEVE (rowcount = COUNT(your_table.non_keyed_field)) 2. Modify the table with the QUEL or SQL MODIFY command. The rowcount displayed by the HELP command is always correct immedi- ately after a table is modified. Summary ------- A table's rowcount will be correct if no INGRES sessions are currently updating the table and if no one has performed updates to the table and then exited INGRES abnormally. Rowcount is always correct immediately after a table is modified via the MODIFY command. Releases affected: 6.0/00(all.all) - Releases not affected: Errors: Bugs/SIRS: ------------------------------------------------------------------------------
© William Yuan 2000