Re: Oracle View Machanism - Opinions Please!!!!!!

In article <769224308snz_at_hoxton.demon.co.uk>, paul_at_hoxton.demon.co.uk (Paul Beardsell) wrote:
> Nonsense, Ed! In my (not so) humble opinion, that is. The problem is
> with poor query analysers. I say: Continue to write structured code -
> your database vendor has addressed / is addressing / will address /
> should address this problem. Views on views are great. If you hit
> a performance problem (and you probably won't) then think about
> reducing the nesting. The same advice was offered routinely to 3GL
> programmers back in the days when compilers were not all that good:
>
> "Code it in a high-level language, we'll re-write bits in assembly
> if necessary."
>
> Note how compilers often produce better machine code than programmers
> nowadays. Query analysers are getting there too.
>
> Just as patriotism is the last refuge of the scoundrel,
> performance is the last refuge of the writer of poorly structured code.

i agree. i have a working system with about 50 tables and over 200 views. many of these views are based on views that are based on views.... i honestly don't know what the deepest level of nesting is, but i'm sure it's at least 5 in some places. most of the logic of my system is contained in these views.

in oracle 6 with forms 3.0 this was the *only* way to get work done on the server instead of the client. note that a cursor loop in a form will pull every record from the server to the client, whether you need it or not.

furthermore, even if you have stored sequential procedures, a sequential procedure will always be sequential. a smart optimizer has a chance of processing a view in parallel on multiple engines.

the procedural approach is certainly more familiar to most programmers, but that doesn't mean it's the best approach. there's an old chestnut to the effect that if you know FORTRAN you can learn LISP in about a month. if you don't know FORTRAN, you can learn LISP in about a week. there is at least some truth to this idea.

have i had performance problems due to my approach? of course! have you ever written a large system in which you didn't need to address performance problems *somewhere* in the system? there are places in my system where i have needed to kludge to get performance. who hasn't? there was one particularly nasty place where i eventually gave up and used a procedural loop to get the speed i needed. later on, though, i discovered a non-procedural view-based approach that was even faster than my loop. the oracle optimizer (like its competitors) still has a lot of room for improvement.

we're not going to make progress until we learn to write code that doesn't use row-at-a-time logic. Received on Fri May 20 1994 - 16:53:49 CEST