The Rules Engine uses a number of SQL optimization techniques that make sure that SQL statements are executed only for those records that correspond or relate to the records actually changed by the user.
Row binding is the general term used to describe these techniques.
If the SQL statement of a constraint were used literally, all records in the database would be re-checked each time a constraint is activated. This is because SQL statements by nature apply to the entire set of records involved.
With row binding, the Rules Engine substitutes known column values for one record only in a SQL statement. Placeholders for column values appear in BenchMark Profiler and Tracer output as colons followed by integers:
Row binding is achieved by:
Consider the business rule: "Guides have a minimum personal discount of 10%". This business rule is implemented by a corrective, single-record constraint:
SET discount = 10
WHERE guide = 'Y'
AND (discount < 10
discount is null)
When a person without a discount is turned into a guide, the Rules Engine can evaluate the constraint locally, and issues the UPDATE statement:
SET DISCOUNT = :1
, GUIDE = :2
WHERE (PERSON.PERSON_ID = :3
In this statement, a WHERE clause is added that restricts the update to the record currently visible on the screen. This way the statement is bound to one row.
Consider the business rule: "A reservation can only be canceled after the reservation is booked". This business rule is implemented by a restrictive, single-record constraint:
SELECT '' violation
WHERE cancel_date < book_date
When a reservation is canceled, the Rules Engine is able to evaluate this constraint locally via the SQL statement:
WHERE ( :1 < :2
In this statement, for the cancel_date and book_date columns, current screen values are substituted. The substituted values are listed below the statement between parentheses. This is also an example of row binding; this way the statement is bound to one row.
Consider the business rule: "The return date of a scheduled tour should match the preset duration of the tour". This business rule is implemented by a corrective, multi-record constraint:
UPDATE schedtour st
SET st.return_date =
SELECT TRUNC(st.start_date) + t.num_days - 1
FROM tour t
WHERE t.destination = st.destination
AND t.tour_type = st.tour_type
When the number of days for a tour is changed, at record validation time the Rules Engine:
WHERE ( :1 = SCHEDTOUR.DESTINATION
AND :2 = SCHEDTOUR.TOUR_TYPE
FOR UPDATE OF SCHEDTOUR.SCHEDTOUR_ID
In this statement, two WHERE clauses are added that restricts the SELECT statement to the scheduled tours of ONE record, the tour record currently visible on the screen. This way the statement is bound to one row.
SET SCHEDTOUR_ID = :1
, DESTINATION = :2
, TOUR_TYPE = :3
, START_DATE = :4
, RETURN_DATE = :5
, PRICE = :6
, MAX_PART = :7
, GUIDE = :8
WHERE ( SCHEDTOUR.SCHEDTOUR_ID = :9
In this statement, a WHERE clause is added that restricts the update to one record: the scheduled tour record within the triggered row event. This way the statement is bound to one row.
For an example of how a restrictive, multi-record constraint is handled, refer to "Constraint Key Query".