undefined | Better HN

0 pointsbenbjohnson14y ago0 comments

I worked as a DBA somewhere that did everything in 5NF. It was a nightmare. I only stayed there 3 months. In my last week there, I remember having to debug a 35 table join. I'm so glad I left.

0 comments

4 comments · 1 top-level

chris_wot14y ago· 3 in thread

The performance must have been horrendous! The query optimiser must have gone nuts - that's 35! possible join combinations :-(

benbjohnsonOP14y ago

I had left before they were willing to performance test the application. But I heard that after they launched that it was awful.

The Oracle optimizer quits optimizing after the first ~7 joins so we had to do manual optimization after that. Almost every query was at least 7 joins. The guy running the DBA group was following the Data Model Resource Book to a "T", which I think is appropriate for an OLAP database, but is not cool for OLTP.

chris_wot14y ago

If you want to see something hilarious, get a 12 join query and add "AND 1=0" on the end of the WHERE clause.

Oracle doesn't look at the query and immediately return no rows, instead the optimizer continues to generate a full plan. Apparently, according to Oracle support, they have no intention of finding "AND false" predicates to shortcut query plan compilation.

jeltz14y ago

That could work in PostgreSQL with the genetic join optimizer. You would not get the best plan or necessarily a stable one but it could be fast enough. I believe the worst reporting queries I have written could have been almost as bad as 35 joins. Of course they were not fast but not as slow as one might think.

j / k navigate · click thread line to collapse