Most heavy cavalry and a fair few of the others have a second weapon besides their default lance/spear, nigh invariably with a higher base attack value but lower charge bonus. The idea is obviously to use the main weapon for charging and then draw the sword, mace or whatever for the melee. Given that alt-attack results in the cavalry charging with their lances and switching to the secondary weapon after a moment, I'm guessing it tells them to put the spears away once the charge value bonus has been exhausted.

I tend to press alt-attack even with light cavalry that lack secondary weapons, purely out of habit.

Alt-attack is also how you make missile troops melee, although you probably knew that. Quite useful when you want your horse-archers to run routers down and not waste arrows on them, or need your chariot archers in the rear of the enemy phalanx *now*. Although with elephants (whose default attack is to charge in and stomp) alt-attack means "stand and shoot" instead...