Hi, all.
The db is 11.2.0.1 on a window machine.
I would like to unnest a exists subquery which is combined by union all.
Additionally, I want to use the unnested query block for LEADING query block.
Is this possible?
Modifying the query is not an option.
Giving sql hints is available.
--------------------------------------------------------
1. in case of simple subquery, I was able to use the subquery for a leading query block.
select /*+ leading(s1@sub1) aaa*/*
from hr.employees t
where exists (select /*+ unnest qb_name(sub1)*/1 from hr.departments s1
where s1.department_id= t.department_id
and s1.location_id='1500')
Plan hash value: 3261546729
-------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Time | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 45 |00:00:00.01 | 5 | | | |
| 1 | NESTED LOOPS | | 1 | | 45 |00:00:00.01 | 5 | | | |
| 2 | NESTED LOOPS | | 1 | 00:00:01 | 45 |00:00:00.01 | 3 | | | |
| 3 | SORT UNIQUE | | 1 | 00:00:01 | 1 |00:00:00.01 | 2 | 2048 | 2048 | 2048 (0)|
| 4 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 1 | 00:00:01 | 1 |00:00:00.01 | 2 | | | |
|* 5 | INDEX RANGE SCAN | DEPT_LOCATION_IX | 1 | 00:00:01 | 1 |00:00:00.01 | 1 | | | |
|* 6 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 1 | | 45 |00:00:00.01 | 1 | | | |
| 7 | TABLE ACCESS BY INDEX ROWID | EMPLOYEES | 45 | 00:00:01 | 45 |00:00:00.01 | 2 | | | |
-------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("S1"."LOCATION_ID"=1500)
6 - access("S1"."DEPARTMENT_ID"="T"."DEPARTMENT_ID")
-- ********************************************************
2. however, when the subquery is combined by union all,
I could not unnesting the subquery and use it for a leading query block.
select /*+ leading(s1@sub1) aaa*/*
from hr.employees t
where exists (select /*+ unnest qb_name(sub1)*/1 from hr.departments s1
where s1.department_id= t.department_id
and s1.location_id='1500'
union all
select /*+ */ 1 from hr.jobs s2
where s2.job_id=t.job_id
and s2.job_title ='Accountant')
Plan hash value: 820360422
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Time | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 50 |00:00:00.01 | 79 |
|* 1 | FILTER | | 1 | | 50 |00:00:00.01 | 79 |
| 2 | TABLE ACCESS FULL | EMPLOYEES | 1 | 00:00:01 | 107 |00:00:00.01 | 7 |
| 3 | UNION-ALL | | 20 | | 4 |00:00:00.01 | 72 |
|* 4 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 20 | 00:00:01 | 3 |00:00:00.01 | 38 |
|* 5 | INDEX UNIQUE SCAN | DEPT_ID_PK | 20 | | 19 |00:00:00.01 | 19 |
|* 6 | TABLE ACCESS BY INDEX ROWID| JOBS | 17 | 00:00:01 | 1 |00:00:00.01 | 34 |
|* 7 | INDEX UNIQUE SCAN | JOB_ID_PK | 17 | | 17 |00:00:00.01 | 17 |
---------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( IS NOT NULL)
4 - filter("S1"."LOCATION_ID"=1500)
5 - access("S1"."DEPARTMENT_ID"=:B1)
6 - filter("S2"."JOB_TITLE"='Accountant')
7 - access("S2"."JOB_ID"=:B1)
-------------------------------------------------------------------------------------
Thanks in advance.
Best Regards.
Edited by: 869578 on 2013. 6. 2 오전 4:00