I need some expert advice on using partitioned views. We currently have a
set of centralized tables with 1 million records and 2 million records
respectively that are being heaviliy inserted and updated against in our
system. We have done many traces and identified that most of the locks /
blocks in the system occur around these tables.

These tables contain metadata about items within our system. Each item sits
within a container which is represented by its own table. There are about
1000 active containers within our system. Each time you pull an item in the
system, you have to first go to the centralized meta data tables, and then
join to the actual container table.

We are currently discussing a new design, that would get rid of the metadata
tables, and merge the data (few columns) to sit within the container tables
themselves. An item can sit in multiple containers at the same time, but is
not necessary to query accross containers for that item (union).

We have estimated 1-2 (probably significantly more) months on the new
design, with significant recoding of the api and the stored procedures.

As an alternative method, Partitioned views are being considered.
Conceptually we could break the metadata tables, into copies for each
container. Then create 2 partitioned views with the same name as the current
tables, that joins all of the child tables. Each table within the view would
have a check constraint to limit which records it is allowed to have.
Including this check field in select/update/delete queries is not a problem
because we are already doing that.

If this method would work, it would probably cut the development to 1-2
weeks tops. We would only have to make slight changes to the api so that
when we create a new container table, we also create the respective meta
data tables. We would use bigints, and set identity set ranges for each
table so as to not have collisions of ids. Also we would have to recompile
the partitioned view, but that is not a big deal. We would of course also
have to write a conversion to convert all the existing data into the new
tables.

So having said all that, I would greatly appreciate a response from someone
who has actually worked with Partitioned Views (not just read about them)
who can advise whether this design sounds feasible and raise performance
concerns.. Also if anyone is aware of any constraints (such as number of
tables in a union within a view) that would also be helpful.

Thanks

Glenn Block
Senior Software Engineer
Global Factory