Hello all,

we face some severe performance problems and identified at least major
problem. Our application is a localized Web application. Localized
texts are maintained in a database table. We defined views that return
unlocalized data, ie. texts to be localized are returned as an index
into the dictionary table. Based on these views, we defined localized
views which return all data from the underlying base view plus
localized information.

We use nested views for other purposes as well, for example something
like

 create view vs_pc_user as
   select
     person.person_id, person.name,
     person.surname,
     person.locale_id as selected_locale_id,
     pc_user.login_name, pc_user.login_password,
     vs_department.department_id, vs_department.department,
     vs_department.division_id, vs_department.division,
     vs_department.company_id, vs_department.company
 from pc_user
   inner join person on pc_user.person_id = person.person_id
   left join vs_department on person.department_id =
vs_department.department_id;

Definition of "vs_pc_user" relies on "vs_department" which is a view
aggregating data from three tables (department, division and company).

All tables have an identity column and a unique index on this column.
All relations between tables use the value of the identity column as
foreign key and there is an index an all foreign keys.

Now if I let DB2 explain a statement like "select * from vs_pc_user
where person_id=1" I see three expensive table scans on all tables
"vs_department" depends on.

If I redefine for example "vs_pc_user" expanding the nested view
"vs_department" DB2 uses the indexes I defined.

What's wrong with using nested views? How can I increase performance
without expanding all nested views?