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

My email has stopped prefixing replies with the chosen symbol - until I find out why anything with "--" in front of it in the mix below is my comment.

--

Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Next Seminar dates:
(see http://www.jlcomp.demon.co.uk/seminar.html )

____USA__________November 7/9   (Detroit)
____USA__________November 19/21 (Dallas)
____England______November 12/14

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Billy Verreynne wrote in message ...
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. :-)

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.

--

• I think the issue I am aiming at assumes that you can
• first state the problem clearly and concisely in human
• terms before you translate to a computer language:
  --
  
• Take the example:
  --
  
• The children of the children of the children,
• excluding my father, of the father of my father.
  --
  
• My paternal grandfather's other grandchildren
  --
  
• My second cousin's twice removed on my
• father's side.
  --
  
• The first is your COBOL programmer, or your end-user without
• logic: and I would argue that whilst it is very simple in its
• content, there is a time when simplicity becomes convoluted
• and high-risk because it takes too long to follow.
  --
  
• The second two are pretty much identical in complexity
• in their description of the requirement - it may be that the
• third one could be implemented to run somewhat quicker
• than the second in some variant of a relational database.
• pun intended).
  --
  
• I THINK I have got the third definition correct, but (and I don't
• think I'm the only one) I would have to check very carefully the- - -meaning of 'second' and 'twice' in this context. Knowing
• that this was the case i would be strongly inclined to use
• the second statement as my source translation to SQL.
  --
  

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.

--

• As you see above, I agree with your comment on "logic skill"
  --
  
• We only appear to diverge because I insist that the first step in the
• logic is to express the problem succinctly in your own language before
• trying to translate it into another,
  --