> I suspect that Oracle treats views as SQL "macros". > So it doesn't have nor need much metadata about a view, besides the > definition itself.
That's my understanding, too. Interestingly, though, because there is a lot of meta information about materialized views, and also about common table expressions. The latter are not the same as regular views, which can be seen from execution plans. It seems that they're formally more expressive than ordinary views, which is a shame. > At least that's how I would implement views if I were to build a > database engine and had to work with time-to-market constraints :-) Well, if the CBO could collect statistics / histograms, etc about views, that would be quite a feature... On the other hand, once a view is expanded and transformed into the surrounding query, depending on the predicates / projections applied, there may not be much left of the original view. E.g: SELECT DISTINCT v.a FROM ( SELECT x.a, y.b FROM x LEFT OUTER JOIN y on a = b ) v The CBO can completely strip and ignore the LEFT OUTER JOIN and select only distinct values from x.a > I dimly remember that Oracle has a way to tell which columns of a view > are updatable. Maybe this information can somehow be creatively abused > to infer which view columns consistute a PK. I think, "updatable" means that if you reduce a view to its "updatable" columns, you can INSERT / UPDATE / DELETE into that part of the view. Oracle mentions the term "key-preserved table" in this context: http://docs.oracle.com/cd/E11882_01/server.112/e10595/views001.htm#i1006232 Typically, when adding many-to-one relationships, column "updatability" disappears from the view. Anyway, among the updatable columns, you still don't know which ones are part of a/the primary key. I have tried to find a solution to this problem before, without any luck: http://stackoverflow.com/q/5500738/521799 The only reliable way to automatically derive underlying constraint information from view columns seems to be by applying strict conventions. If I'm wrong, I'll happily attribute a 250k bounty on Stack Overflow to a better answer :-), as this would solve a couple of non-jOOQ problems for me, too. > If I read Jooq's mission statement correctly, such a requirement would > be outside Jooq's sope, too. If primary keys can be derived reliably from view meta information, jooq-codegen could be changed to consider that. We could also discuss some additional code generator configuration to tell the generator which columns should be considered as PKs - e.g. by specifying a regex.
