Home > Articles > Data > SQL

  • Print
  • + Share This

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.

  • + Share This
  • 🔖 Save To Your Account

Related Resources

There are currently no related titles. Please check back later.