> RPost wrote: > So then what is the difference between using a VTI and using a view?
Good question. My use of VTIs has been limited to this particular discussion, but I think the biggest difference is the language we use: with views, our playing field is the scope of SQL; with VTIs, we have pretty much all of Java to work with, which gives us more freedom to do result set manipulations.
That said, though, it DOES look like everything we'd want to be able to do _can_ be done in SQL (via subqueries), and hence option #2 in my previous email. In effect, we'd be creating "view-like" functionality through use of subqueries. If we were to add an option #3 that is kind of like #2 but would actually be _creating_ views, then we'd have to answer the additional question of when do we create the views and where do we store them? Would be they become another set of SYS objects? Such an approach would be more complicated than #2, I _think_...
The advantage of VTIs over subqueries and views is that we wouldn't have to change anything at build time or database creation time--the general flow would remain exactly as it is now. If we opt for subqueries and/or Views, then we have to figure out how to create those subqueries/views automatically from the existing JDBC metadata procedures (because again, we want to avoid having duplicate, humanly-maintained sets of queries), which requires additional work at build or database creation time.
But then, like I said, since we _are_ able to do all the manipulations that ODBC requires using SQL, maybe option #2 (or a view-based #3) would be preferable to doing it in Java...
If anyone with more experience with VTIs knows of other/more significant differences between them and SQL views, feel free to say so...
Army
