Ingres Rowcounts

                         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:                                                             
------------------------------------------------------------------------------
Ingres Database Reference
To William's Home Page
Email William