It all depends on the motherboard.
When you overclock a P4, you're pushing the FSB up way high. Currently DDR isn't able to fill all of the bandwidth that a P4 has.
I'm not too fluent on P4 mobo dividers, but most of them will allow you to set a memory divider so that your RAM doesn't have to run in sync with the FSB. Hopefully someone else will be able to shed some light on this
The short answer for you is no, Corsair is not required for overclocking, but it's very high performance RAM and will give you great results.
Kingston's PC2700 Value RAM is doing pretty great at overclocking too... it's a lot cheaper than Corsair and all of it will at least do PC3200 speeds.