Water: easy clean-up, thin enough to enable good thrusting but thick enough to stay in place (provided you get a good lube). Doesn't mess with rubber.
Oil: stays around well, can be thicker (good for big, slow moving toys).
Downsides: Water: for me the big one is preservatives. Many water-based lubes cause a burning feeling. They also dry out and are more likely to "squeegee" off.
Oil: Can mess up rubber/latex/jelly. Hard to clean up, can mess up fabrics.