Discuss how you would want a query optimiser within an ORDBMS to handle this type of query. Use a relational algebra tree to illustrate your answer.

Question

Consider the following schema:
Pet (petNo, petName, petDescription, dateRegistered, picture, surgeryNo,
doctorStaffNo)
Staff (staffNo, sName, sAddress, position, surgeryNo)
Surgery (surgeryNo, surgeryAddress, surgeryTelNo)

where:
Pet contains details of pets and the pet number (petNo) is the key. The surgery where the
pet is registered is given by the surgery number (surgeryNo). A pet can only be registered with one surgery at a time. The doctor who treats the pet is given by the doctorStaffNo.
Picture contains an image of the pet.
Staff contains details of staff and staff number (staffNo) is the key.
Surgery contains details of each surgery and the surgery number (surgeryNo) is the key.

Now consider the following SQL:2003 query:
SELECT petNo, staffNo
FROM Pet p, TABLE StaffDoctors s
WHERE p.doctorStaffNo = s.staffNo AND
p.brownShortHairedTerrier(picture) AND s.surgeryNo = ‘S3’ AND
p.dateRegistered < ‘1-Jan-90’;

The routine brownShortHairedTerrier() is an externally defined routine that searches the specified image for certain characteristics. The StaffDoctors() function is fully defined within SQL3 as:
CREATE FUNCTION StaffDoctors() RETURNS SET(Staff)
SELECT * FROM Staff WHERE position = ‘Doctor’;

Answer 1

(i) The first expectation is that the QP could flatten the SQL-defined function
StaffDoctors().
(ii) Secondly, the normal heuristics of pushing the selection operations down past the join would need to be modified in this case, as it would be better to run the external function, which will be CPU-intensive, after the join operations on a smaller set of records.
(iii) Have index on the result of functions.