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	Description
net_src	char(32)	Name of the source node
net_dest	char(32)	Name of the destination node
net_cost	float8	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.
net_exp1	float8	Expansion field (should be set to zero)
net_exp2	float8	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:

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.

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.

net_src char(32)	net_dst char(32)	net_cost (f8)
sanfrancisco	newyork	0.001
sanfrancisco	washington	0.002
sanfrancisco	sanfrancisco	0.0002

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.