I always thought the coolest song intro was to "The Story in Your Eyes" by the Moody Blues. The intro actually had a title called "Procession". It starts with an "outer space" sound representing the creation of the planet and then proceeds forward through time, playing a theme on different instruments through the ages - starting with "caveman" chanting, then drums, moving to some middle-eastern instrument and then a lute, mandolin, pipe organ, full orchestra and finally ending with an electric guitar which plays the first notes of the main song. VERY COOL!
Matter of fact I may need to get a six pack on the way home and give it a spin:-)