ModRewrite is specifically used to re-write dynamic URL,s made by .php sites and most CMS (content management systems)
Module re-write the name gives some insight into what it does, it re writes a URL into a human and search engine friendly URL.
For example a unwritten non search engine friendly URL could look like
xyz.com.au/index.php?/cat76=?ghy36 apart from the fact it tells the web user nothing about the page they are viewing the URL has almost no chance of being indexed without good external links pointing to that page.
Now lets look at a rewritten URL, xwz.com.au/digital-cameras-canon-canon-G9-compact-camera-review.html
Now you can see in this example for one the web user can easily see the web page is going to be a review about the Canon G9 Compact camera, this web page will also be easily indexed by the search engine directories with some simple internal linking.
ModReWrite is the combination of configuration to the .htaccess file located in your sites root directory and a module built into the Apache web server.
Update: As of Monday 22nd of September 2008 Google announced it can crawl dynamic URL's we still recommend rewriting URL's as for one there are many other search engines that still struggle to properly index dynamic URL's and there are far more reasons than just indexing to confider. We encourage you to read our post about the Google statement which also contain reasons to rewrite your URL's. Read why you should rewrite your URL's in response to Google's article.