Row Binding

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:

:1, :2,...

Row binding is achieved by:

·	Adding WHERE clauses to SQL statements that will restrict the scope of the statement to one record.

·	Substitution of known column values for one record in a SQL statement.

Example 1

Consider the business rule: "Guides have a minimum personal discount of 10%". This business rule is implemented by a corrective, single-record constraint:

UPDATE person

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:

UPDATE COURSE1.PERSON

SET DISCOUNT = :1

, GUIDE = :2

WHERE (PERSON.PERSON_ID = :3

)

(10,Y,236)

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.

Example 2

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

FROM reservation

WHERE cancel_date < book_date

When a reservation is canceled, the Rules Engine is able to evaluate this constraint locally via the SQL statement:

SELECT ''

FROM DUAL

WHERE ( :1 < :2

)

(1999/0423000000,1999/0422224414)

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.

Example 3

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:

1.	Updates the tour itself. This is a straightforward SQL statement.

2.	Selects the scheduled tours that should be updated, and also locks these records. This operation is identified in BenchMark's profiler output as SELECT_FOR_UPDATE:

SELECT SCHEDTOUR.SCHEDTOUR_ID

, SCHEDTOUR.DESTINATION

, SCHEDTOUR.TOUR_TYPE

, SCHEDTOUR.START_DATE

, SCHEDTOUR.RETURN_DATE

, SCHEDTOUR.PRICE

, SCHEDTOUR.MAX_PART

, SCHEDTOUR.GUIDE

FROM COURSE1.SCHEDTOUR

WHERE ( :1 = SCHEDTOUR.DESTINATION

AND :2 = SCHEDTOUR.TOUR_TYPE

)

FOR UPDATE OF SCHEDTOUR.SCHEDTOUR_ID

(AUSTRALIA,SURVIVAL)

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.

3.	For each selected scheduled tour, a new row event is triggered. Each scheduled tour is updated by the statement:

UPDATE COURSE1.SCHEDTOUR

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

)

(131,AUSTRALIA,SURVIVAL,2000/0301000000,2000/0320000000,2000,10,5,131)

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".