oracle-wiki.net - MERGE and NO

The New Home of Oracle-wiki.net

All things oracle...

A website for IT Professionals involved with Oracle database.

Back

MERGE and NO_MERGE Hint

Introduction

The MERGE hint in Oracle is used to encourage the optimizer to merge a view's query into the main query block when the main query references a view. The MERGE hint can also encourage the optimizer to merge an inline view, also known as an inline subquery into the main query block. This can sometimes lead to a more efficient execution plan, especially when dealing with simple views.

Lets look at some examples

Example

Suppose we have a view that calculates total sales per customer from an orders table.

CREATE VIEW customer_sales AS

SELECT customer_id, SUM(order_total) AS total_spent

FROM orders

GROUP BY customer_id

And we run the following query

SELECT *

FROM customer_sales

WHERE total_spent > 10000

By default, Oracle may execute this query by first materializing the customer_sales view and then filtering on total_spent > 10000. See below

Oracle treats customer_sales as a separate view and processes it independently. It first executes the view's query:

SELECT customer_id, SUM(order_total) AS total_spent

FROM orders

GROUP BY customer_id

The output of this view is materialized or entirely computed in memory. Then, Oracle applies the outer query:

SELECT *

FROM "computed_materialized_view"

WHERE total_spent > 10000

However, this could be inefficient because it processes all customers e.g. SUM(order_total), before applying the filter. So how would Oracle execute this query if using the MERGE hint.

SELECT /*+ MERGE(cs) */

cs.customer_id, cs.total_spent

FROM customer_sales cs

WHERE cs.total_spent > 10000

The above query with the MERGE hint, will get rewritten by the optimizer in a way similar to this

SELECT customer_id, SUM(order_total) AS total_spent

FROM orders

GROUP BY customer_id

HAVING SUM(order_total) > 10000

In the rewrite, we can see that the following has happened.

The WHERE cs.total_spent > 10000 condition is pushed inside the aggregation.
Instead of aggregating all orders, it only processes rows where SUM(order_total) > 10000, reducing unnecessary computation.
The optimizer now avoids materializing the entire customer_sales view

If your lucky enough to have all your customers spending over $10,0000, it may not be anymore efficient, but if you only have a few customers spending over $10,000 this MERGE should help performance.

Here is the same example, but this time using a subquery or inline view

SELECT *

FROM (

SELECT customer_id, SUM(order_total) AS total_spent FROM sales GROUP BY customer_id

) v

WHERE total_spent > 10000

If you were to use the MERGE hint in this example, then the optimizer could perform the same transformation as above.

Limitations and Considerations

It's important to understand that not all view merging will be efficient, as previously mentioned. The efficiency largely depends on the complexity of the queries and the data involved. Below are some limitations and considerations you should keep in mind.

Complex Queries with Multiple Views

If the query involves multiple views or subqueries, using the MERGE hint may not produce a better plan. In some cases, Oracle may still choose not to merge the view into the query.

Non-Deterministic Views

If the view involves non-deterministic functions e.g. SYSDATE, Oracle may not be able to merge it as the result might vary for each row processed.

Performance Considerations

The MERGE hint doesn't always lead to a performance improvement. In some cases, it may result in a slower execution plan because it may lead to additional work in merging the view. Test,test, test.

Cost of Merging

If the view is large or involves complex joins, merging it into the outer query could increase the complexity of the execution plan. Again, test, test, test

Effects on Join Methods

The MERGE hint can influence the choice of join methods in the query execution plan. If a view is merged, Oracle might choose a hash join, nested loop join, or merge join depending on the query structure and the available indexes. And ofcourse, this can lead to better or worse performance.

Materialized Views

The MERGE hint is intended for regular views (or subqueries). It does not apply to materialized views. See the REWRITE hint instead for materialized views.

Complex View Merging

If using an old version of Oracle, 11g or earlier, then if a view's query block contains a GROUP BY clause or DISTINCT operator in the SELECT list, then the optimizer can merge the view into the accessing statement only if complex view merging parameter is enabled. From 11g, onwards this parameter has gradually been removed and now is part of the optimizers standard processing. However, some versions of Oracle and views may require the following parameters enabled OPTIMIZER_SECURE_VIEW_MERGING and OPTIMIZER_FEATURES_ENABLE. (Deprecated in 23ai) You should consult the documentation for the version of Oracle you are using.

Summary of Key Restrictions and Considerations

Not all views are easily merged, especially if they are complex or non-deterministic.
Performance improvements with MERGE depend on the query and the data, so testing is essential.
The MERGE hint may influence join strategies, but it may not always result in the most efficient plan.

Important Notes

The optimizer code and initialization parameters around view merging has changed significantly over the years, with each version of Oracle changing the behaviour or parameter details. Please consult the documentation carefully before use. Also, note that some documentation, e.g. 23ai, still references "enabling complex view merging" , which although not wrong, is somewhat superfluous as this is the default behavior.

NO_MERGE Hint

The NO_MERGE hint tells the optimizer to avoid merging the outer query with any inline view queries, keeping them as separate queries instead of combining them into a single query.