12.1.0.2
common table expressions are renamed internally to form 'SYS_TEMP_xxxxx_xxxx'
Ive got a plan that's running to 100s of lines with multiple CTEs, it would be useful if we had a way to rename these. I don't think its possible but hoping someone may know, this is different to a qb_name hint which is useful in itst
with cte1 as (select /*+ qb_name(CTE1) */ 1 as col1 from dual),
cte2 as (select /*+ qb_name(CTE2) */ 1 as col1 from cte1 ),
cte3 as (select /*+ qb_name(CTE3) */ 1 as col1 from cte2)
select /*+ qb_name(MAINSEL) */ cte1.col1, cte2.col1, cte3.col1
from cte1, cte2, cte3
;
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 8 (0)| 00:00:01 |
| 1 | TEMP TABLE TRANSFORMATION | | | | | |
| 2 | LOAD AS SELECT | SYS_TEMP_0FD9D66AA_553C9D96 | | | | |
| 3 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 4 | MERGE JOIN CARTESIAN | | 1 | 3 | 6 (0)| 00:00:01 |
| 5 | NESTED LOOPS | | 1 | 3 | 4 (0)| 00:00:01 |
| 6 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
| 7 | VIEW | | 1 | 3 | 2 (0)| 00:00:01 |
| 8 | TABLE ACCESS FULL | SYS_TEMP_0FD9D66AA_553C9D96 | 1 | 13 | 2 (0)| 00:00:01 |
| 9 | BUFFER SORT | | 1 | | 4 (0)| 00:00:01 |
| 10 | VIEW | | 1 | | 2 (0)| 00:00:01 |
| 11 | TABLE ACCESS FULL | SYS_TEMP_0FD9D66AA_553C9D96 | 1 | 13 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$82792E6B
2 - SEL$053E6583
3 - SEL$053E6583 / DUAL@CTE1
6 - SEL$82792E6B / DUAL@CTE1
7 - SEL$874A7A8D / CTE2@MAINSEL
8 - SEL$874A7A8D / T1@SEL$874A7A8D
10 - SEL$874A7A8E / CTE2@CTE3
11 - SEL$874A7A8E / T1@SEL$874A7A8E
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "CTE2"."COL1"[NUMBER,2]
2 - SYSDEF[4], SYSDEF[0], SYSDEF[1], SYSDEF[112], SYSDEF[0]
4 - (#keys=0) "CTE2"."COL1"[NUMBER,2]
5 - (#keys=0) "CTE2"."COL1"[NUMBER,2]
7 - "CTE2"."COL1"[NUMBER,2]
8 - "C0"[NUMBER,22]
9 - (#keys=0)
11 - "C0"[NUMBER,22]