Using Query Plans for Views - Firebird

Views may present some difficulty for users of the PLAN feature. Ordinarily, users may treat a view the same as a table. However, if you want to define a custom plan, you need to be aware of the indexes and structures of the base table(s) participating in the view.

The optimizer treats a view reference as if the base tables used in creating the view were inserted into the FROM list of the query.

Suppose a view is created as follows:

CREATE VIEW V_PROJ_LEADERS ( PROJ_ID, PROJ_TITLE, LEADER_ID, LEADER_NAME) AS SELECT P.PROJ_ID, P.PROJ_NAME, P.TEAM_LEADER, E.FULL_NAME, FROM PROJECT P JOIN EMPLOYEE E ON P.TEAM_LEADER = E.EMPNO;

A simple query on the view

SELECT * FROM V_PROJ_LEADERS;

outputs this plan:

PLAN JOIN (V_PROJ_LEADERS P NATURAL,V_PROJ_LEADERS E INDEX (RDB$PRIMARY7))

Notice that the optimizer accesses the indexes of the base tables (through the aliases P and E) to evaluate the best way to retrieve the view. It is the SELECT specification of the CREATE VIEW declaration that determines the logic for executing the join.

The next query is a little more complex. This time, the view is joined to the table EMPLOYEE_PROJECT, an intersection of the primary keys of the EMPLOYEE and PROJECT tables. From there, it is joined back into the EMPLOYEE table, to provide a denormalized listing that includes the names of the members of all of the projects commanded by the view:

SELECT PL.*, EMP.LAST_NAME FROM V_PROJ_LEADERS PL JOIN EMPLOYEE_PROJECT EP ON PL.PROJ_ID = EP.PROJ_ID JOIN EMPLOYEE EMP ON EP.EMP_NO = EMP.EMP_NO; PLAN JOIN (EMP NATURAL,EP INDEX (RDB$FOREIGN15),PL P INDEX (RDB$PRIMARY12), PL E INDEX (RDB$PRIMARY7))

This time, the foreign key index on the EMPLOYEE_PROJECT (aliased as EP) column EMP_NO is used to select the project members’ names from the second “hit” on EMPLOYEE. As before, the join inside the view uses the primary key of EMPLOYEE to search for TEAM_LEADER matches.

If you decide to write a custom plan for a query that works on a view, you need to be familiar with the view definition in your own estimations of indexes and access methods.


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Firebird Topics