How to make and use a stack in Civ 4:
So there is indeed planning and strategy required to make a good stack to go against the opponent. As you described, you cannot just spam 100% of your current best unit, Warrior, UU or Horsemen, and expect to wallop the enemy.
If you encounter difficulty, you
need to use your Siege Engines to soften the stack. So this is strategy on the part of SoD!
In fact, Soren Johnson and his team made some excellent subtle balances to SoD. If Siege Engines are causing trouble, split your stacks or place Cavalry adjacent to the catapults in the stack. The Siege Engines would be damaged by flanking and its effectiveness would be reduced subsequently. Civ IV encourages smaller varied stacks, but if you felt that your strategy would work better as a single stack, they didn't stop you either.
Repeat this procedure up through air units at which point you can swap bombers in for siege units, assuming you ever reach them. Failure to maintain a single stack will make it easier for single-unit spam to overwhelm each stack individually.
And the opponent would just counter with Fighters based in his cities? Again, it still required a mix of forces,
i.e. strategy. SAMs and Mobile AA also grants an interception chance. If you have neither Air Power nor AA, then you'll just have to proceed in either smaller stacks or halt your advance altogether until you get that Air Defenses up that you so desperately need,
strategic advance or strategic hold, we could choose.
Compare this to 1UPT where Air Power means nothing when you can only bombard one tile, which doesn't significantly deal the damage that the introduction of Air Power was supposed to represent.
Notice how little is going on once the stack is built? Stacks lead to crude attack-move meat grinder tactics. They're the bane of any RTS, and tolerating them in a TBS doesn't help the game.
Until your stack is reduced, of course, then you'll have to build that careful composition of units based on new realities. If you try to leave one unit behind to heal, it'll just be wiped off by the nearest cavalry.
Compare this to Civ V's 1UPT where if I encountered a force of counter-units to my frontline, the massive traffic jams of the back would prevent me from sending my counter-units where I need them most.
The consequences of 1UPT, which led to slow production times as addressed by Sulla, meant that insta-heal was provided as a promotion for 1UPT to be viable in Civ V
If anything, 1UPT resembles the meat-grinder more than SoD, where your line pushed the opponent's hex-by-hex.
For good stacks, you have to let attackers target the best thing to attack, not the worst. This means that when you field a counter to the enemy, it can actually do its job of bringing down the unit it was built to destroy, instead of just acting as a stationary speed bump because your counter can't reach its intended target. Adding multiple equipment options for units on both attack and defense lets you design counters more directly and puts more depth into the stack designing as well (e.g. GalCiv2 or AC), but I don't think it's necessary to make stacks fun as stacks worked well enough without it even in GC1 (which did have intelligent targeting for stacks).
I can agree with the Intelligent-Targeting concept, but also consider this from another point:
Why would I bother placing my weaker units into a stack when it'll be selectively targeted and destroyed anyway? I might as well place individual units in different tiles, at least it'll occupy space. 1UPT carpeting results.
In one previous Civ, Civ II, I think, stacks were destroyed once one unit lost combat. Civ IV released how broken such a system was and changed it to its current form.
If I'm in a stack, I should be able to enjoy the stack's protection, that's the basis of a stack, not a design error.
Of course, this leads to the problem of glass cannon units being difficult to use effectively since they'll all be targeted first. 1UPT handles this effectively. If you Horseman have a path to the enemy Archers, they get to attack the Archers. You can also position your troops in a defensive arrangement to prevent your glass cannons from being attacked first and force the enemy to attack the units you want them to, but defensive lines can be flanked or a weak defensive line can be broken. You're not victim to defenders who can't defend or attackers who can't attack what they need to.
Yeah, if the map was big enough a la Panzer General. Civ V, however, is not. The map sizes at Standard and lower are so small that it constrains any maneuver of units. I get more traffic jams than I get battles.
At larger maps, the game has all sorts of scaling problems and issues on top of technical unoptimization that makes the game unplayable. Large maps problems have been discussed elsewhere, if you're interested.
I have no issue with 1UPT by itself. But its implementation in Civ V is downright horrid and actually does the 1UPT concept a great disservice.