Recently in class a student mentioned that she’d be told
that for the WITH clause you don’t need to use the MATERIALIZE
hint anymore. Maybe. It turns out that if you REALLY want the
subquery factor to be materialized, you do need to use it.
Keep in mind that the optimizer sees your SQL statement as
just that, a statement. It can and will
rearrange and rewrite your SQL into something it likes better. And this means it can merge your subquery
factors defined in the WITH clause into the statement.
I’ll use a simple statement to illustrate this point. Clearly this select could (and really
should) be written without a WITH clause at all. This is just a simple example to illustrate
the point. Once you have a better
understanding of how it works you can decide what is best for your particular
situation.
Edit: Tests were done on an Oracle Database 12c Enterprise Edition Release 12.2.0.1.0, on a windows laptop.
Edit: Tests were done on an Oracle Database 12c Enterprise Edition Release 12.2.0.1.0, on a windows laptop.
SQL> --
no hints
SQL> WITH
inline_view AS
2 (
3
SELECT /*+ qb_name(cust_totals)*/ CUST_NO, SUM(TOTAL_ORDER_PRICE) AS
total_orders
4
FROM ord2
5
GROUP BY CUST_NO
6 )
7
SELECT /*+ qb_name(main) */ *
8
FROM inline_view where total_orders > 1400000;
CUST_NO TOTAL_ORDERS
---------------
---------------
9379
1409495.95
…
If we run this the optimizer will merge the subquery factor
and the main query together into one statement.
Which really makes sense here, the subquery factor isn’t really doing
anything special. Here is the plan, notice the query block MAIN disappears
completely and the only reference to the CUST_TOTALS block is the table
alias:
SQL_ID bnzfdp1yvk7vc, child number 0
-------------------------------------
WITH
inline_view AS ( SELECT /*+ qb_name(cust_totals)*/ CUST_NO,
SUM(TOTAL_ORDER_PRICE)
AS total_orders FROM ord2 GROUP BY CUST_NO )
SELECT /*+
qb_name(main) */ * FROM inline_view
where total_orders >
1400000
Plan hash
value: 2809195938
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT |
| | |
69 (100)| |
|* 1 |
FILTER | |
| | | |
| 2 |
HASH GROUP BY | |
50 | 550 | 69
(2)| 00:00:01 |
| 3 |
TABLE ACCESS FULL| ORD2 | 12890 |
138K| 68 (0)| 00:00:01 |
----------------------------------------------------------------------------
Query Block
Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$8092F496
3 - SEL$8092F496 / ORD2@CUST_TOTALS
Predicate
Information (identified by operation id):
---------------------------------------------------
1 -
filter(SUM("TOTAL_ORDER_PRICE")>1400000)
Now to use the MATERIALIZE hint and the subquery
factor defined in the WITH clause stays as a separate
entity.
SQL> --
materialize hint
SQL> WITH
inline_view AS
2 (
3
SELECT /*+ materialize qb_name(cust_totals)*/ CUST_NO,
SUM(TOTAL_ORDER_PRICE) AS total_orders
4
FROM ord2
5
GROUP BY CUST_NO
6 )
7
SELECT /*+ qb_name(main) */ *
8
FROM inline_view where total_orders > 1400000;
CUST_NO TOTAL_ORDERS
---------------
---------------
9379 1409495.95
…
In the plan this time we can see the both query blocks and a
new one which is the materialized data from the subquery factor.
SQL_ID 65bmr36y814d3, child number 0
-------------------------------------
WITH
inline_view AS ( SELECT /*+ materialize qb_name(cust_totals)*/
CUST_NO,
SUM(TOTAL_ORDER_PRICE) AS total_orders
FROM ord2 GROUP BY
CUST_NO )
SELECT /*+ qb_name(main) */ * FROM
inline_view where
total_orders
> 1400000
Plan hash
value: 1306428065
-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 72 (100)| |
| 1 |
TEMP TABLE TRANSFORMATION
| | |
| | |
| 2 |
LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D6653_4B0C1F7
| | | | |
| 3 |
HASH GROUP BY
| | 1000 | 11000 | 69
(2)| 00:00:01 |
| 4 |
TABLE ACCESS FULL
| ORD2 |
12890 | 138K| 68
(0)| 00:00:01 |
|* 5 | VIEW | | 1000 | 26000 | 2
(0)| 00:00:01 |
| 6 |
TABLE ACCESS FULL
| SYS_TEMP_0FD9D6653_4B0C1F7 |
1000 | 11000 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------------
Query Block
Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - MAIN
2 - CUST_TOTALS
4 - CUST_TOTALS / ORD2@CUST_TOTALS
5 - SEL$03D1C9D1 / INLINE_VIEW@MAIN
6 - SEL$03D1C9D1 / T1@SEL$03D1C9D1
Predicate
Information (identified by operation id):
---------------------------------------------------
5 -
filter("TOTAL_ORDERS">1400000)
This makes it clear that if the optimizer can merge the
subquery factor in to the main query it will.
The materialize hint will keep the subquery factor as a separate
entity. Bottom line is that if you want
a subquery factor kept as a separate entity, and you notice that in the plan
it’s being merged in, then use the materialize hint.
Which brings up another point. Some folks will use the NO_MERGE hint to
keep the subquery factor from being merged in.
This works, sort of. Here is the
same query using the NO_MERGE hint.
SQL> --
no merge hint
SQL> WITH
inline_view AS
2 (
3
SELECT /*+ no_merge qb_name(cust_totals)*/ CUST_NO,
SUM(TOTAL_ORDER_PRICE) AS total_orders
4
FROM ord2
5
GROUP BY CUST_NO
6 )
7
SELECT /*+ qb_name(main) */ *
8
FROM inline_view where total_orders > 1400000;
CUST_NO TOTAL_ORDERS
---------------
---------------
9379 1409495.95
…
And now here is the plan.
Notice that the subquery factor isn’t completely merged in, but it does
become basically an inline view instead.
This of course might be just fine and works the way you want, however
it’s not a materialized version of the subquery factor, but an inline
view.
SQL_ID 6mf7u56n8bad6, child number 0
-------------------------------------
WITH
inline_view AS ( SELECT /*+ no_merge qb_name(cust_totals)*/
CUST_NO,
SUM(TOTAL_ORDER_PRICE) AS total_orders
FROM ord2 GROUP BY
CUST_NO )
SELECT /*+ qb_name(main) */ * FROM
inline_view where
total_orders
> 1400000
Plan hash
value: 2169976290
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT |
| | |
69 (100)| |
| 1 |
VIEW | |
1 | 26 | 69
(2)| 00:00:01 |
|* 2 |
FILTER | |
| | | |
| 3 |
HASH GROUP BY | | 1 |
11 | 69 (2)| 00:00:01 |
| 4 |
TABLE ACCESS FULL| ORD2 | 12890 |
138K| 68 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Query Block
Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - CUST_TOTALS / INLINE_VIEW@MAIN
2 - CUST_TOTALS
4 - CUST_TOTALS / ORD2@CUST_TOTALS
Predicate
Information (identified by operation id):
---------------------------------------------------
2 -
filter(SUM("TOTAL_ORDER_PRICE")>1400000)
I was asked via Twitter what would happen if the no_query_transformation hint was used. The optimizer produces the same plan as the no_merge hint query.
ReplyDelete