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.
Firebird Related Interview Questions
|RDBMS Interview Questions||MySQL Interview Questions|
|Linux Interview Questions||Mac OS X Deployment Interview Questions|
|Windows Administration Interview Questions||Windows Server 2003 Interview Questions|
|SQL Interview Questions||NoSQL Interview Questions|
|Advanced C++ Interview Questions|
Introduction To Client/server Architecture
About Firebird Data Types
Date And Time Types
Blobs And Arrays
From Drawing Board To Database
Creating And Maintaining A Database
Firebird’s Sql Language
Expressions And Predicates
Querying Multiple Tables
Ordered And Aggregated Sets
Overview Of Firebird Transactions In
Programming With Transactions
Introduction To Firebird Programming
Developing Psql Modules
Error Handling And Events
Security In The Operating Environment
Configuration And Special Features
Interactive Sql Utility (isql)
Database Backup And Restore (gbak)
Housekeeping Tool (gfix)
Understanding The Lock Manager
All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.