Time to gush.
I absolutely love this game. I got it back when it first came out for Christmas and proceeded to play the ever loving crap out of it. I managed to get all of the game's gold medals, unlock the hard mode and get all of the gold medals in that.
There's something about the concept of the game and its execution that makes it so exciting for me. Basically, there are three tiers to every level. The first is just clearing the path for the nuclear weapon, the second is razing the entire level, and the last was to unlock the fast timing. This basically meant that I could play each level in a variety of ways to accomplish all of the goals.
Even better, each level is wildly different in its design and execution, and the variety of vehicles make for some interesting puzzle solving and destruction.
There's so many layers of depth to the title it's pretty amazing for an N64 game. The true shame is that it has never been followed up by Rare (or Nintendo, I have no idea who owns the rights), especially when there is so much promise for a game like this on a current generation platform.
Like Kathrine said, at face value, it's a whole lot of fun to wreck stuff, even if you don't want to explore any of the depth of the title. Everyone with an N64 needs this game.