Data base optimization method for estimating query and trigger procedure costs

摘要

The method of the invention estimates a total processing cost incident to execution of a query statement in respect of a relational database, wherein execution of the query statement results in a further execution of a trigger procedure. The method first estimates the query statement's costs by analyzing components of the query statement to determine a processing time there for in relation to a database table that is the target of the query statement. The method then determines the number of rows that are affected in the database table as a result of execution of the query statement. Next, components of a trigger procedure that is invoked in response to execution of the query statement are analyzed to determine a “base cost” for a single execution of the trigger procedure visa-vis data that is contained in a single row of the database table. The components of the trigger procedure are next analyzed to determine a per row cost for execution of the trigger procedure. The per row cost is determined from an estimate of time required for execution of the trigger procedure for a single row and from an estimate of time required for execution of the trigger procedure for a plurality of rows. Thereafter, the total processing cost of a query statement is calculated by adding the query statement cost to a sum of the either (i) the base cost if only one row is subjected to the query statement or (ii) the per row cost, multiplied by the number of rows affected by the query statement.