Not so much that the memory has to be overclocked, but it tends only to be useful in overclocking (eg. an Intel with 800 FSB - quad pumped DDR400 ).
For AMD processors, you rarely hit that kind of level - that would be 275 FSB the way most AMD boards quote it, and I don't know many AMD combinations being pressed much above 240. It would be good in an overclocking situation with a P4 though.
The downsides to very high clock rate DDR, are generally high price and poor cycle timings - going above PC3700 on an AMD tends to be counterproductive unless you are sure you can hit a really high FSB overclock, so the FSB speed makes up for the poor memory timings.