Re:Bring a database down? (Score 1)

I work with customers doing Business Intelligence against Data Warehouses, and I can assure you that it is quite easy to develop runaway SQL Queries. Most of the queries we see are generated by some tool, and are not humanly readable or understandable. It is the nature of Business Intelligence that these queries are adhoc, untested. In my own data warehousing system, I frequently run into poor optimizer choices. Usually it involves nested queries, where common sense tells you the optimizer should run the innermost query first, store the temp results, then join in the outer query; but for some reason, the DB chooses to do the outer query first, and re-executes the 'static' inner query at every loop. I've had queries run for hours; then I extracted the inner query to an explicit temp table first, and the total query runs in seconds. Oracle also has a bad habit of choosing to do a hash join (instead of nested loops), only to find out that the hash join exceeds available RAM, and the temp results have to page to disk. Again, queries that should run in seconds thrash the machine for hours...