Had to write one once in a development DBA role. It wasn't quite the
monster you describe but was a few pages long and probably the only way to
meet the performance requirement on the scale of systems customers were
using at the time - Sun V880s.
This query had to price calls separated into in-country and international
tariffs, with 4 types of discount that could apply and infinite discount
tiers.
It was suggested that sucking the call data out and running pricing in an
external mechanism written in C could do better, but it was likely the
overhead of sucking the data out and then pumping it back in would outweigh
the advantage.
This was a case where writing a monster SQL actually allowed the optimiser
to do its stuff. A PL/SQL could have done it, but would not have been able
to take advantage of early row elimination when an overriding discount tier
made other discount types irrelevant. Or something like that. (It was a
long time ago.)
Regards,
J
On Tue, 1 Mar 2016, 19:42 angelo, <angelolistas@xxxxxxxxx> wrote:
Halo Matt,
Sorry for saying, but this is the real world (and cruel)... lol
On 1 March 2016 at 12:56, Matt Adams <MAdams@xxxxxxxxxxxxxxxxxxx> wrote:
1.8 million characters!!!!! How in bloody hell does ANYTHING write a
single sql statement that 1.8 million characters long.
Just for fun, I pasted it into microsoft word….ITS OVER 500 PAGES. It’s
a freaking novel!!
It’s a mass of nested selects (I quit counting at six levels deep) and
massive numbers of unions. It optimizer must have lost its mind trying to
figure this one out.
I’ve never seen anything quite like it. This may be the most obscene
thing I’ve ever seen in 23 years as an oracle dba.
I can’t imagine someone actually writing this monstrosity. It must have
been generated by some application at run time.
And I’ve just been asked to troubleshoot why this statement takes so
long…hmm….can’t imagine a single reason.
I just needed to vent a little to somebody who might understand. What’s
the most egregious statement you’ve come across?
We now return you to your regularly scheduled helpful oracle related mail
list.
Matt
**** This communication may contain privileged and/or confidential
information. If you are not the intended recipient, you are hereby notified
that disclosing, copying, or distributing of the contents is strictly
prohibited. If you have received this message in error, please contact the
sender immediately and destroy any copies of this document. ****
