4 ms·
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
by benbjohnson 14y ago
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.
- chris_wot 14y agoThe performance must have been horrendous! The query optimiser must have gone nuts - that's 35! possible join combinations :-(
- benbjohnson 14y agoI 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_wot 14y agoIf 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.
- jeltz 14y agoThat 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.