Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: SQL Power !!

RE: SQL Power !!

From: Orr, Steve <>
Date: Fri, 09 May 2003 11:39:29 -0800
Message-ID: <>

Hi Kirti,

Computers can generate queries even more complicated/dumber than humans, well most humans. ;-)

We have a patented dynamic SQLgenerator thingy which spews out some gawd awful stuff. We can't just tune queries, we have to ask duhvelopment to tune the SQLgenerator thingy and they're scared to even look at it. On multiple web pages users can click on a plethora of radio buttons, check boxes, and fill in fields or type in text areas to their hearts' delight. Then the SQLgenerator thingy assembles/scrambles this mass of mish-mash and creates syntactically correct SQL. The thingy often sorts needlessly, joins/self-joins tables needlessly, queries on dates into the next millennia, repeatedly gets the same unchanging GUI presentation data over and over, tracks user clicks, only generates literals and never uses bind variables, etc. ad nauseum. We have entertained moving some data sets from another DBMS to Oracle because it hit the 30 table join limit on the other DBMS. I've been invited to peer inside the thingy but haven't had the nerve yet as it could send me over the edge o! r !
stress my altogether too fragile grip on reality, whatever that is. The thingy is simultaneously wonderful, awesome, awful, dreadful and is growing into a beast which can't be slain.

Looking for the Holy Grail and a magic sword in a stone somewhere... Steve Orr
Going Bozo in Bozeman, Montana

-----Original Message-----
Sent: Friday, May 09, 2003 11:27 AM
To: Multiple recipients of list ORACLE-L


I can challenge that !

I just finished optimising a query produced by developpement, once printed it was 6 pages long and accessed many tables.

But the best part is that a table was accessed 22 times. Yes 22 times
(inline views madness).

After spending 1 hour trying to understand the beast without success, I just call the guy and ask what was the qestion the query was supposed to answer.

Stephane Paquette
Administrateur de bases de donnees
Database Administrator
Standard Life
Tel (514) 925-7187 <>

-----Original Message-----
Sent: Friday, May 09, 2003 12:22 PM
To: Multiple recipients of list ORACLE-L

Here is what was caught by my co-worker recently. One of the Developers, of our Preferred Vendor, actually wrote this stuff. This is invoked by the one of the forms on the intranet Web application. I was told there are quite a few scripts like this one. Performance issues? No one is complaining (yet) .... so who cares :) Enjoy...
- Kirti
SELECT DISTINCT CUST1.customer_id, LIST1.finding_name, NVL(LIST1.atn_npa, ' '),

NVL(LIST1.atn_cop, ' '), NVL(LIST1.atn_line_no, ' '), LIST1.lni,
NVL(LIST1.listed_tn_npa, ' '), NVL(LIST1. listed_tn_cop, ' '),
NVL(LIST1.listed_tn_line_no, ' '),

--- cut ---
Please see the official ORACLE-L FAQ:
Author: Orr, Steve

Fat City Network Services    -- 858-538-5051
San Diego, California        -- Mailing list and web hosting services
To REMOVE yourself from this mailing list, send an E-Mail message
to: (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L

(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Received on Fri May 09 2003 - 14:39:29 CDT

Original text of this message