7. Understanding Ingres Star Catalogs : Ingres Star Catalogs : iidbdb Catalogs : The iiddb_netcost Catalog
Share this page                  
The iiddb_netcost Catalog
The iiddb_netcost catalog in the iidbdb is used to weigh the relative network costs of a transaction in order to compute the best query execution plan (QEP). Data in this catalog is used by Ingres Star’s distributed optimizer.
Column Name
Data Type
Name of the source node
Name of the destination node
Cost of moving one byte from the source node to the destination node as a multiple of 1 DIO (Disk I/O). This field contains a float that is the cost of transferring one byte from the source to the destination site. This cost should be made in terms of DIO units. Network costs are added to DIO costs in order to determine which plan is cheapest. See the example below.
Expansion field (should be set to zero)
Expansion field (should be set to zero)
All data transfers are made by first transferring the data to the Star Server from the source site, then transferring data from the Star Server to the destination site. As a result, the only entries in the iiddb_netcost table that will be useful are those that include the Star Server node name as one of the sites.
Note:  The StarView utility does not allow users to populate the iiddb_netcost catalog. If your configuration contains greatly differing network costs and you wish to provide network cost information to Ingres Star, you must do so manually.
To make inserts and updates into the iiddb_netcosts catalog, you must be a privileged user and log in as the installation owner. At the operating system prompt, enter:
sql iidbdb '-u$ingres' +U
sql iidbdb -u$ingres "+U"
The Star Server must be restarted for the new iiddb_netcosts values to take effect.
When you make changes to the iiddb_netcost catalog, you can analyze differences in query plan strategies by using the set qep statement. The network or N costs are printed in the last line of each node in the query plan.
For further details on query execution plans, see the Database Administrator Guide.
Example: Net_cost
Assume that the Star Server is located on a node named sanfrancisco (using lower case is the default), and that the remote sites are named newyork and washington.
Some of the entries in iiddb_netcost could be:
net_src char(32)
net_dst char(32)
net_cost (f8)
Note that there is an entry in which sanfrancisco is both the source and destination sites. This represents transfers of data from the Star Server to and from local databases on the Star Server site (including the coordinator database), but these costs are relatively low.
Also, typically, the same cost applies for either direction so that if only one row exists between two nodes, the other direction is assumed to be the same cost.
For example, suppose 10000 bytes is to be transferred from newyork to washington. The data is routed through sanfrancisco since the Star Server exists on that node. The cost of transferring from newyork to sanfrancisco is 0.001*10000=10 units, and the cost from sanfrancisco to washington is 0.002*10000=20 units, so the total cost is 30 units. If there were 20 disk I/Os and 2 CPU units involved, the total cost would be 52 units.