Unreal 3 Engine will be used in many, many titles after the release of UT2007, it will be the next standard for gaming. It will be what they thought the Doom 3 engine would be but it never happened because of the Doom 3 engine's limitations. So yes, upgrading around this game will assure the playability of games for at lest 2-3 years after its release.
P.S., you wont need a dual-core processor to run it, but it will be almost a requirement to run it at decent settings because of CPU demands. And lets shut up about it running on a 9700 Pro or FX series card, thats nonsense. The requirements will clearly be 6 series minimum for Nvidia (6600 GT i imagine), and for ATI, i don't know. Lets just say we will be lucky if we have a SM2.0 fallback mode so it will run on the X800/X850 series cards. I imagine it will run pretty poorly on those cards though especially in SM2.0.