Informatica Tutorials

Big Data Analytics

Cost-Based Optimization

When a query is rewritten, Oracle's cost based optimizer compares the cost of the
rewritten query and original query and chooses the cheaper execution plan.

Query rewrite is available with cost-based optimization. Oracle Database optimizes
the input query with and without rewrite and selects the least costly alternative. The optimizer rewrites a query by rewriting one or more query blocks, one at a time.
If query rewrite has a choice between several materialized views to rewrite a query
block, it will select the ones which can result in reading in the least amount of data.

After a materialized view has been selected for a rewrite, the optimizer then tests
whether the rewritten query can be rewritten further with other materialized views.
This process continues until no further rewrites are possible. Then the rewritten query is optimized and the original query is optimized. The optimizer compares these two optimizations and selects the least costly alternative.

Because optimization is based on cost, it is important to collect statistics both on tables involved in the query and on the tables representing materialized views. Statistics are fundamental measures, such as the number of rows in a table, that are used to calculate the cost of a rewritten query. They are created by using the DBMS_STATS package.

Queries that contain inline or named views are also candidates for query rewrite.
When a query contains a named view, the view name is used to do the matching
between a materialized view and the query. When a query contains an inline view, the
inline view can be merged into the query before matching between a materialized
view and the query occurs.

In addition, if the inline view's text definition exactly matches with that of an inline view present in any eligible materialized view, general rewrite may be possible. This is because, whenever a materialized view contains exactly identical inline view text to the one present in a query, query rewrite treats such an inline view as a named view or a table.

Following figure presents a graphical view of the cost-based approach used during the
rewrite process.

Related Posts Plugin for WordPress, Blogger...

Please Share

Twitter Delicious Facebook Digg Stumbleupon Favorites More

Follow TutorialBlogs
Share on Facebook
Tweet this Blog
Add Blog to Technorati