Looking at Views
When you write a query on a view, the view is translated into its underlying SELECT statements. If you need information from one table but go through a multitable view to get it, you'll pay a price.
Adaptive Server Anywhere create view ordervu as select od.ordnum, od.prodnum,substr( p.name,1, 20) as name, p.price * od.unit as cost from orderdetail od, product p where od.prodnum = p.prodnum [view created]
To find the name of a particular product, you might write a query like this:
Adaptive Server Anywhere
select distinct prodnum, name
from ordervu
where prodnum = 2050
prodnum name
=========== ====================
2050 tax time
[1 row]
It gives you exactly the results you want, but a look at the PLAN output is daunting.
Adaptive Server Anywhere
Estimate 13 I/O operations
Summarize Subquery1 grouping by orderdetail.prodnum,expr
Subquery1:
Estimate 13 I/O operations (best of 2 plans considered)
Temporary table on (orderdetail.prodnum,expr)
Scan product AS p sequentially
Estimate getting here 21 times
Scan orderdetail AS od sequentially
Estimate getting here 693 times
Removing DISTINCT cuts down on the processing a bit, but it also returns multiple copies of the product number and name. It shows one row for every order that includes the product number 2050.
Adaptive Server Anywhere
select prodnum, name
from ordervu
where prodnum = 2050
prodnum name
=========== ====================
2050 tax time
2050 tax time
2050 tax time
2050 tax time
2050 tax time
2050 tax time
2050 tax time
[7 rows]
Estimate 5 I/O operations (best of 2 plans considered)
Scan product AS p sequentially
Estimate getting here 21 times
Scan orderdetail AS od sequentially
Estimate getting here 693 times
Compare the ordervu view PLANs to the product table PLAN for essentially the same query.
Adaptive Server Anywhere
select prodnum, name
from product
where prodnum = 2050
prodnum name
=========== ====================
2050 tax time
Estimate 4 I/O operations
Scan product using unique index prodix
for rows where prodnum equals 2050
Estimate getting here 1 times
Don't use a view if all the data you want is in a single underlying table, and don't make frequently queried views unnecessarily complex.