Re: How to tune SQL to avoid ORA-03232 ?

In article <aojgid$e8t$1_at_ctb-nnrp2.saix.net>, Billy says...
>
>Jonathan Lewis wrote:
>
>> A question - when do you make the trade-off between writing
>> SQL which is human readable, and therefore subject to easy
>> comprehension and quick modification by the next person,
>> and writing SQL which is designed to do the job the optimizer
>> ought to do.
>
>Good question. Never thought about it that way. Likely because of two
>factors.
>
>The CBO is not perfect. And I love to mess about wringing every drop of
>performance I can from my Oracle SQL. :-)

Oh ye of little faith! Of course it's perfect! It was, after all, written by Oracle. :)

>
>Second reason. Human readability is not really the issue IMO. And actually
>part of the problem. To explain - I am dealing with a couple of users with
>no formal programming logic training. They tackle the problem in a "common
>human readable way". The results are the most complex SQL statements I have
>ever seen. Nested selects many levels deep. Unions. Inline views. Those
>SQL's really run a couple of A4 page print outs.
>
>The problem IMO is a lack of logic skills. You need to have some training
>and experience to translate a "human problem" into a "computer solution".
>Directly applying human language skills in defining the problem just like
>that in something like SQL... well, IMO SQL is not suited for it.
>
>If anything, ProLog would be more able as it allows you to define the
>problem without needing to specify how it must be solved.
>
>I am also not saying that one should not write readable code. I have done my
>share of Fortran and C/C++ maintenance on cryptic code. But there is a
>difference between writing programmer readable code, and human readable
>code.

Boy, been there and done that! I had to maintain some C code once that it was easier to rewrite from scratch than to work out what the hell it was doing!

>
>> I like the code because it seems to say in SQL exactly
>> what you mean in English.
>
>Which is why COBOL was pretty easy to learn. And I'm sure that most people
>will be able to read an old COBOL program of mine and understand the basics
>of what it is doing, without any formal programming training. But it was
>hell and very frustrating to develop in COBOL.
>
>Give that same person a Delphi/C++/Java program and he will be clueless. But
>I can achieve in Delphi what is impossible in COBOL. In a mere fraction of
>the time that it would take to write a COBOL program with even a 1/4 of the
>complexity of the Delphi program.
>
>Have classes. Can instantiate. :-)
>
>But you have raised an interesting topic Jonathan. What is the target for
>the SQL language? End-users/power users? Or trained and professional
>developers? Especially when looking at how increasingly complex and more
>powerful computer languages become. Where does SQL fit in?

Only MHO, but I don't believe SQL will ever be targetted at end-users. That would mean they'd have to know what they're doing, and the whole definition of end-users is the exact opposite of that. That's why GUI's were invented in the first place. SQL has become (and will continue to be) the language that is generated by GUI tools for accessing databases, AND the tool that those of use who know what they're doing (be they developers or DBA's) will use for getting to information quickly or for writing scripts that need to run in unattended mode.

My $0.02, FWIW.

HTH. Additions and corrections welcome.

Pete

SELECT standard_disclaimer, witty_remark FROM company_requirements; Received on Wed Oct 16 2002 - 10:49:44 CDT