Re: Bug in consistent gets for recursive/nested SQL?

On Thu, 30 Sep 2004 23:52:36 +0200, Sybrand Bakker <sybrandb_at_hccnet.nl> wrote:

>On Thu, 30 Sep 2004 20:16:48 GMT, netcomradeNSPAM_at_bookexchange.net
>(NetComrade) wrote:
>
>>Is anyone aware of a bug, where consistent gets are not recorded in
>>original query?
>
>It is not a bug. The logical and physical I/O of *recursive* sql is
>*never* included in the original query.
>Your function issue a select ( a *BAD* idea), the select is
>*recursive* sql, so you don't see the I/O other than by tracing the
>session.

Thanks.. I always thought (looks like thought wrong), that it was the case that recursive SQL was recorded (actually, too bad it's not, would've been easier to find the worst offenders)

>and BTW: I don't know of any case where issuing sql inside a function,
>the function itself called from a select *didn't* result in a
>performance drama, AFAIK it *ALWAYS* does, INVARIABLY.
>Avoid these constructs, AT ALL COST!

I don't disagree.. I've been trying to push for schema redesign (lots of one-to-one tables), outer joins (in some cases, joins insetad of function lookup) and summary tables. Unfortunately, I have to do all the work to 'prove' my points. When I simply bitched about it in the past (as app was developed), no one listened. Now it's.. oh.. "why is it slow", "why do we need more hardware".

-A
.......
We use Oracle 8.1.7.4 on Solaris 2.7 boxes remove NSPAM to email Received on Fri Oct 01 2004 - 10:57:44 CDT