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.
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’;
(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.
You might also like to view...
A(n) ____________________ symbol is simply an image that you can reuse multiple times on the stage.
Fill in the blank(s) with the appropriate word(s).
To select a block of text, click where the selection is to begin, hold down Ctrl, and then click where the selection is to end
Indicate whether the statement is true or false
When you see the Security Warning bar, you can click the Disable Security button to continue editing your document
Indicate whether the statement is true or false
When PivotTable data are rearranged, corresponding changes are automatically made in the PivotChart
Indicate whether the statement is true or false.