Materialize Hint Does Not Work With XMLTABLE
(Doc ID 2886059.1)
Last updated on JULY 22, 2024
Applies to:
Oracle Database - Enterprise Edition - Version 19.11.0.0.0 and laterInformation in this document applies to any platform.
Symptoms
When using materialize hint /*+ MATERIALIZED */, it's expected to to use TEMP TABLE TRANSFORMATION as a step in the execution plan, like below:
-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 23 (100)| |
| 1 | TEMP TABLE TRANSFORMATION | | | | | |
| 2 | LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9 | | | | |
| 3 | NESTED LOOPS | | 1 | 242 | 21 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL | ODS_T| 1 | 208 | 2 (0)| 00:00:01 |
| 5 | VIEW | | 8168 | 271K| 19 (0)| 00:00:01 |
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 23 (100)| |
| 1 | TEMP TABLE TRANSFORMATION | | | | | |
| 2 | LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9 | | | | |
| 3 | NESTED LOOPS | | 1 | 242 | 21 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL | ODS_T| 1 | 208 | 2 (0)| 00:00:01 |
| 5 | VIEW | | 8168 | 271K| 19 (0)| 00:00:01 |
Changes
Upgrade the database from version 18c, 19.3, 19.4, 19.5, 19.6, 19.7,.19.8,.19.9 and 19.10 to 19.11 and above
Cause
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
| Symptoms |
| Changes |
| Cause |
| Solution |
| References |