WITH + HInt MATERIALIZE is not necessarily valid

WITH + HInt MATERIALIZE is not necessarily valid. It must be materialized after multiple calls. It is called only once, and MATERIALIZE syntax is useless: MATERIALIZE Description: indicates that the optimizer materialized the inline view-a temporary view is created during execution.

[sql] with dd as (select  object_type,owner,object_Id    from dba_objects)  select OBJECT_TYPE, count(*)    from dd   group by OBJECT_TYPE  union all  select owner, count(*) from dd group by owner;      with dd as (select /*+materialize */object_type,owner,object_Id from dba_objects)  select OBJECT_TYPE, count(*)    from dd   group by OBJECT_TYPE  union all  select owner, count(*) from dd group by owner;  

 

Running Environment: ORACLE, Release 11.2.0.1.0. description: In the SQL-99, The WITH clause is added to the query statement, so that the same subquery can be executed only once in an SQL statement, in addition to the complex query can be simplified, increase readability. In addition, we know that the GTT (global temporary) global temporary table and materialize view Materialized VIEW of ORACLE can improve the query efficiency. In fact, we can use WITH an undisclosed hint materialize to replace GTT and materialized views when the number of subquery results is not large., And the execution result reduces the time spent by half. The hint materialize is not publicly reported by oracle. It instructs oracle cbo to materialized temporary tables in the with clause. This is unnecessary after oracle10g, but when using with, if it is found that it is not materialized, you can use this hint to force oracle to perform this processing so that this subquery (temporary table) it is processed only once. Conclusion: under certain circumstances, we can use the combination of with and hint materialize to replace the global temporary table and materialized view to improve the query efficiency. The usage is as follows. 1. If the number of results records in a query is not too large, GTT is recommended if the number of results records is large, because you can specify indexes. 2. If the query value and query filter conditions contain expressions that are repeatedly executed, you can use this method to reduce the number of computations. 3. The same subquery is used multiple times. After reading this, I thought it was not a materialized prompt to reduce the time. Maybe it was SELECT/* + NO_MERGE (V_TMP) FULL (V_TMP) */*. This prompted me to reduce the time. Shenzhen-Dao (726442711) after the prompt is added at 17:25:53, it is in the form of a temporary table that needs to be materialized after multiple calls. It is called only once, and MATERIALIZE syntax is useless: MATERIALIZE Description: indicates that the optimizer materialized the inline view-a temporary view is created during execution. It can prevent the outer predicates from being pushed internally and expand in the inline view. The WITH statement itself can share the results and add materialized results in the same statement to indicate whether the results are redundant and whether the results are officially unavailable.