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

Jonathan Lewis wrote:

>
> 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.

TOOLS | OPTIONS. Select the SEND tab. There are two settings at the bottom of the dialog box. Mail Sending Format. News Sending Format. Next to both a PLAIN TEXT button. You configure the prefix symbol there. But I think this will only work when you also disable the HTML option and enable the Plain Text one.

Better yet, rather use KNode.. the fact that you need Linux installed for it, is a bonus. :-)

> -- 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:

I agree.

> -- 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.

Yes and no. You are using a fairly rigid example. In which case it will work fine.

I was refering more to cases where a problem can be solved in more than one way. The best way in Oracle, may not be what the user may think the best or most logic way.

One specific query I dealt with from a user was written in a logical way. However, by changing the logic slightly and first eliminate that he did not want, rather than doing that at the end, the query was significantly faster. Instead of having to deal with 100's of thousands of rows until the end of the query (various sub SELECTs and in-line views), the elimination up front reduced that to 40,000 rows.

I could not fault the user's logic. But I could fault his application of the logic to Oracle.

Part of the problem I think is that many users (and unfortunately, developers too) view data processing using RDBMS as row processing and not data set processing. Oracle is not an ISAM file. Nor is it MS Access or dBASE where there is no db engine that can do the data crunching for you.

> -- 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,

I do not have a problem with that. I agree that one should careful think the logic through, before implementing the processing for it. I prefer to not to use English (or one's own language) though. I like to visualise it on paper using diagrams and pseudo code (or Boolean algebra).

And yeah, I have a hard choice between selecting to wear the pointy ears or the ribbed forehead, when attending Trekkie conventions. :-)

--
Billy