web:config

tips and tricks for the interwebs

SQL challenge

Now for something technical, unrelated to web dev and Opera…alien
I joined my company in March 2007, and since then I've worked mostly in Oracle. Back then, I only had a theoretical knowledge of databases, but meanwhile I learned a lot in most areas, and now I'll fairly comfortable working with a big database.
In many technologies you’ll always find fun stuff to do. So know I post you a contest:
Which is the smallest possible query you can write, that'll take the longest to execute?
This is solely an exercise. I won't buy you a pack of peanuts monkey if you beat everyone else to the answer.
Some notes:
  • it may work only in a specific database
  • you can only use builtin functions, if any, not custom ones.
  • your answer must be solely a SQL query, the name of the database the query runs and an explanation of how it works.
  • doing wild Cartesian merges is not the answer, like
    select 1 from dba_objects, dba_objects, dba_objects, dba_objects, dba_objects, ...
    for Oracle. When you find that the query takes an eternity to execute, you can still add another dba_objects to the FROM clause and you’ll go beyond eternity, while making the query fatter and therefore beating the prime purpose: to be the smallest


Good luck! The challenge is on. cool

P.S.: Yes, I have a possible answer and I think I already gave enough hints wink

Plugsome ToolbarSpammers using M2?

Comments

serious Thursday, June 12, 2008 8:06:25 AM

if you can have to seperate connections you could open a session in the one with the ... that option that locks the page completely (damn, I should know that, I've got a database-test in a week) and then try to read the data with the other connection.
that should be possible in ... 3 short lines imo (but I can't really prog any sql ^^)

João EirasxErath Thursday, June 12, 2008 10:30:19 PM

If you have one session with some line locked and other session trying to read that line, then the 2nd session should not block. Only if the later tries to lock the line too.

But, that's a hack, and breaks the contest rules p

serious Friday, June 13, 2008 6:00:23 AM

jup, that's what I meant. setting the transaction to serializable (which shouldn't allow any reads if Ive understood my db-scriptum correctly) and then trying to read the data from another connection while the transaction is still alive. But well, if only one connection is allowed I don't have a clue ^^

Write a comment

New comments have been disabled for this post.