Re: What the Query Rewrite

The quick version is that 'query rewrite' is the Oracle engine's ability to re-write the SQL statements you issue so that it can take advantage of particular new features.

For example, if you've implemented Fine-grained Access Control (sometimes called Virtual Private Database), then your users will be issuing queries such as 'select * from orders' -as far as they are concerned they are querying every record in the table. But, if you've implemented FGAC, then what actually happens is that the query is silently rewritten by the Oracle engine, to append a 'where' clause -such as 'where custcode = 30547' -so that, in fact, they only get to see some of the rows in the table (ie, their own orders).

Another example is Materialized Views. These are snapshots of table data, with various aggregations, ordering and computations pre-computed and stored. Querying such a view for, say, the total value of all orders grouped by customer would be a lot quicker than computing such totals on-the-fly from an ORDERS table containing many hundreds of thousands of records. But your application was developed (let us say) in Oracle 8.0 when Materialized Views did not exist, and therefore blythely carries on issuing the 'select custcode, sum(ordervalue) from orders group by custcode' query.

The magic of query re-writing is that the *optimizer* can see that, now the materialized view is in place, it would make more sense from a performance point of view to query that than the Orders table directly -and it therefore silently re-writes the query to be something like 'select custcode, orderval from order_MV'. You wouldn't even know the re-write had happened unless you looked carefully at an explain plan -so it's a good way of boosting the performance of computationally-intensive parts of applications without having to tinker with the source code at all. The application still issues the same old code as before; it's the optimizer that makes the decision to re-write the query as submitted, if it feels it would be worthwhile to do so.

Check out the documentation at technet.oracle.com for the Materialized View topic, and you'll get a good idea of how powerful query re-write can be in the right circumstances, and what things you have to do to make it possible to happen.

Regards
HJR

--
----------------------------------------------
Resources for Oracle: http://www.hjrdba.com
===============================

"frank" <frankw_at_qd.lucent.com> wrote in message
news:a0jutq$lfr_at_nntpa.cb.lucent.com...


> Hello,

>

> Please give me an explanation on the Query Rewrite, what is it used for.

>

> Thanks in advance

> Frank

>

>