Table used in with clause getting accessed multiple times-CodePudding

My understanding of Oracle "WITH" clause is, if we want to use a table multiple times in query then keep it in with clause and use that with clause name in rest of the query to avoid multiple hits on the same tables. See below code, why dual table is getting accessed two times?

    explain plan for
    with temp as
    (
    select * from dual
    )
    select * from temp
    union all
    select * from temp;

select * from table(dbms_xplan.display);


PLAN_TABLE_OUTPUT
Plan hash value: 716948595
 
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     2 |     4 |     4   (0)| 00:00:01 |
|   1 |  UNION-ALL         |      |       |       |            |          |
|   2 |   TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------

CodePudding user response：

Well, that's not exactly how it works. Oracle calls it a "subquery_factoring_clause", and the documentation says:

The subquery_factoring_clause lets you assign a name (query_name) to a subquery block. You can then reference the subquery block multiple places in the query by specifying query_name. Oracle Database optimizes the query by treating the query_name as either an inline view or as a temporary table.

A WITH clause gives the optimizer the option of materializing or caching the subquery (ie, only running the subquery once), but the optimizer will still make its own decision. Since accessing dual is very cheap (it's already in-memory), in this case it treats it as an inline view and does view merging, rewriting the query to something like:

select * from (select * from dual)
union all
select * from (select * from dual)

If your subquery was more complicated, Oracle would materialize it instead. See this answer on the DBA SE for an example (and an explanation of how that can sometimes hurt performance). This Oracle documentation also has a nice explanation of inline views vs factored subqueries.

CodePudding user response：

The optimizer is free to consider either inlining the view (as it does in your example) or materializing it. If you want to force the optimizer to materialize, use the materialize hint; but if you don't see an improvement, remove it. Sometimes the optimizer is wrong, but sometimes you are.

You may wonder why the optimizer doesn't choose to materialize the view in your example. You may think that with the view inlined, the runtime will access the table twice, and you may think this will generate too much hard disk access. That is likely wrong (and that's probably the optimizer's view): it will access the data twice, but it will very likely cache it; only one access will be from disk (slow) while the second will be from cache. If instead the view is materialized, it must be written somewhere; even if it's to cache, you have reading from disk once (to compute the view) and then reading the data from the view (from cache) twice. More work than with inlining.