Encountering dependency hell is usually a sign that you don't know what you're doing.
Red Hat "just works" and has always enjoyed the reputation of being the most bulletproof distro if you could afford it.
I've always heard that said about Debian more than about Red Hat (though Red Hat certainly is pretty stable).
Red Hat has a lot more success in businesses though because you can get contractual support; which may not only be useful if you don't want to get the skills in-house but also because your own customer may contractually require it.
But this is not true in all areas, especially in some aspects that matter to companies such as training/certification and having good up-to-date documentation.
And as a volunteer-driven project, I don't think Debian can ever be as responsive to end-user problems or requirements as a commercial product can be.
But it definitely gives Red Hat a good run for its money. For example, the Debian LAMP stack has long been and still is the gold standard.
A good sysadmin would know how to avoid dependency hell. For example this might include taking basic precautions such as testing changes in a chroot or development/testing environment, before rolling them out.
I'm not saying the problem didn't exist, only that it wasn't an actual problem if you knew what you were doing.
Also, RedHat didn't ship with (relatively) much software (install everything was an option). Now you've got to configure; make; make install. Now you've got two parallel installations of libraries and software.
Oh. And the RPM database would die periodically (rpm anything would hang), requiring a reinstallation.
Since moving to Debian over a decade ago, I think I had to configure; make; make install something only once (it was an old and unmaintained Java library on SF.net). Almost everything (open source) is in the Repos.
There is no distro today that provides packages for all the software you will ever want, or the specific version that you need. At some point, you will resort to installing software from outside the officially-supported sources, whether from experimental or user-maintained package repositories, or from a third party in binary or source form.
Until recently, this was an operation that wasn't guaranteed to be easy or straightforward or risk-free. In the worst case, it could even screw up your system in ways that are time-consuming to diagnose and fix.
In the example you gave, I would conclude that OpenOffice didn't package their RPM well, because it ended up driving RH6 users down the rabbit hole. At the very least, they could have unpacked all the files under /opt and provided static binaries, or included all the libs in the archive. Many packages still do that today, such as Vagrant, which installs under /opt/vagrant and includes its own Ruby interpreter there.
Nowadays there are efforts underway to make installing custom software safe and easy, projects like: flatpak, OSTree, appimage, and snap. Hopefully we can reach a point where you can install whatever version of whatever software you want without breaking anything.
Generally, nowadays you keep a system relatively up to date using apt-get update and upgrade, pacman or yum. Back then all you had was RPM (the equivalent of dpkg).
The attitude was "you want new versions? Go to upstream, download the .RPM and install".
Now, OO (don't remember if it existed back then) depends on certain versions of (say) GTK.
What do you do?
Go to gtk.org and download the rpm.
Rinse and repeat
I never install anything from source. Packages from yum repositories only.
Upgrade OpenOffice with a downloaded RPM? No. The point of RedHat is stability for enterprises. If you want the latest version of everything, RedHat is the wrong distribution.