Data base optimization method for estimating query and trigger procedure costs

A memory media for controlling a computer to estimate a total processing cost to execute a query statement with respect to a relational database, wherein execution of said query statement results in a further execution of a trigger procedure, said memory media comprising:

• a) means for controlling said computer to estimate a query cost of a query statement in relation to a database table that is a target of said query statement;
• b) means for controlling said computer to determine a number of rows that are affected in said database table, as a result of execution of said query statement;
• c) means for controlling said computer to analyze components of a trigger procedure that are invoked in response to execution of said query statement to determine a base cost for a single execution of said trigger procedure in relation to data contained in a single row of said database table;
• d) means for controlling said computer to further analyze components of said trigger procedure to determine a per row cost for execution of said trigger procedure;
• and
• e) means for controlling said computer to calculate said total processing cost by adding said query cost to a sum of said base cost plus said per row cost multiplied by the number of rows affected by execution of said query statement, as determined by said determining step.

A system for estimating a total processing cost for executing a query statement with respect to a relational database, wherein execution of said query statement results in a further execution of a trigger procedure, said system comprising:

• a relational database including data organized into logical tables;
• memory means for storing an estimator procedure for controlling a processor to estimate said total processing cost of a query statement and said trigger procedure;
• and
• a processor, which, in combination with said estimator procedure:
• estimates a query cost of said query statement in relation to a database table that is a target of said query statement;
• determines a number of rows that are affected in said database table as a result of execution of said query statement;
• analyzes components of a trigger procedure that is invoked in response to execution of said query statement to determine a base cost for a single execution of said trigger procedure in relation to data contained in a single row of said database table;
• further analyzes components of said trigger procedure to determine a per row cost for execution of said trigger procedure;
• and
• calculates said total processing cost by adding said query cost to a sum of said base cost, plus said per row cost multiplied by the number of rows affected by said query statement.