Home » Other » Community Hangout » Worst Ever SQL  () 2 Votes
Worst Ever SQL [message #181629] Mon, 10 July 2006 22:56 Go to next message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Just for fun, what's the worst possible variant of SELECT * FROM DUAL that you can write?

My best effort is below, but I'm on 9i at the moment, so I can't throw in a MODEL clause to mix things up.

The rules:
- You can only select from DUAL.
- No joins
- The SQL must come back with one column (DUMMY) and one row (X)
- The object is to get the most horrid plan with the most number of steps.
- (SELECT * FROM DUAL UNION SELECT * FROM DUAL .....) is cheating

SQL> run
  1  select *
  2  from dual
  3  where rownum = 1
  4  connect by level = 1
  5  start with level = 1 or rownum = 1
  6  group by cube (dummy)
  7  having grouping(dummy) = 0
  8* order by max(dummy) over (order by dummy)

D
-
X


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   SORT (ORDER BY)
   2    1     WINDOW (BUFFER)
   3    2       FILTER
   4    3         SORT (GROUP BY)
   5    4           GENERATE (CUBE)
   6    5             SORT (GROUP BY)
   7    6               COUNT
   8    7                 FILTER
   9    8                   CONNECT BY (WITH FILTERING)
  10    9                     NESTED LOOPS
  11   10                       COUNT
  12   11                         FILTER
  13   12                           TABLE ACCESS (FULL) OF 'DUAL'
  14   10                       TABLE ACCESS (BY USER ROWID) OF 'DUAL'
  15    9                     NESTED LOOPS
  16   15                       BUFFER (SORT)
  17   16                         CONNECT BY PUMP
  18   15                       FILTER
  19   18                         TABLE ACCESS (FULL) OF 'DUAL'

[Updated on: Mon, 10 July 2006 22:58]

Report message to a moderator

Re: Worst Ever SQL [message #181655 is a reply to message #181629] Tue, 11 July 2006 00:56 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Hmm, I once brought down one of our development server Wintel box to it's knees with a SELECT from dual GROUP BY CUBE. Our DBA couldn't believe that I only used dual to do that.

MHE
Re: Worst Ever SQL [message #181661 is a reply to message #181629] Tue, 11 July 2006 01:08 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Nice one Ross,
you missed out on one though:
select max(dummy)
allows you to connect by level < infinity, creating a whole new world of possibilities
Cool
Previous Topic: Can I vent?
Next Topic: Phonetic Alphabet
Goto Forum:
  


Current Time: Sun Jul 12 18:10:07 CDT 2020