Take a look at this URL from newegg.com - http://www.newegg.com/Product/Product.aspx?Item=N82E16822140118. Do you have any idea what the product is before you click on it? Unless you're really good at memorizing item numbers, probably not. Not only is this URL not attractive to humans, search engines like Google place an emphasis on the URL when looking for keywords. Walmart is guilty of the same problem.
http://www.walmart.com/catalog/product.do?product_id=10290815 is a link to buy dog food, but how would anyone know?
Based on the URL rewriting work I did for blogging, I'm now applying it to our new e-commerce module - Marketpath StoreFront. Like blogging, StoreFront will be an add-on for Marketpath CMS. Our version of the same Walmart page would look like http://www.walmart.com/pets/dogs/dogfood/purina-beneful-healthy-radiance. Much better. This will no doubt be a future trend in online stores as it helps redefine SEO for e-commerce.