|
Re: Query having multiple subqueries to be simplified [message #689603 is a reply to message #689602] |
Tue, 20 February 2024 03:35   |
John Watson
Messages: 8965 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
Welcome to the forum.
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Your query has all those outer joins to subqueries. It could be more efficient to replace all that by projecting an appropriate case statement. Here's a simple example, in the SCOTT demo schema:
orclz>
orclz> --your way,
orclz> SELECT e.deptno,
2 e.ename,
3 s.loc,
4 r.loc
5 FROM emp e,
6 (SELECT *
7 FROM dept
8 WHERE dname = 'SALES') s,
9 (SELECT *
10 FROM dept
11 WHERE dname = 'RESEARCH') r
12 WHERE e.deptno = s.deptno(+)
13 AND e.deptno = r.deptno(+);
DEPTNO ENAME LOC LOC
--------------- ---------- ------------- -------------
20 SMITH DALLAS
20 JONES DALLAS
20 SCOTT DALLAS
20 ADAMS DALLAS
20 FORD DALLAS
30 ALLEN CHICAGO
30 WARD CHICAGO
30 MARTIN CHICAGO
30 BLAKE CHICAGO
30 TURNER CHICAGO
30 JAMES CHICAGO
10 CLARK
10 KING
10 MILLER
14 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 487804956
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 1120 | 9 (0)| 00:00:01 |
|* 1 | HASH JOIN OUTER | | 14 | 1120 | 9 (0)| 00:00:01 |
|* 2 | HASH JOIN OUTER | | 14 | 700 | 6 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMP | 14 | 280 | 3 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL| DEPT | 1 | 30 | 3 (0)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | DEPT | 1 | 30 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("E"."DEPTNO"="DEPT"."DEPTNO"(+))
2 - access("E"."DEPTNO"="DEPT"."DEPTNO"(+))
4 - filter("DNAME"(+)='SALES')
5 - filter("DNAME"(+)='RESEARCH')
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- this is an adaptive plan
orclz>
orclz> --my approach,
orclz> SELECT e.deptno,
2 e.ename,
3 ( CASE
4 WHEN d.dname = 'SALES' THEN d.loc
5 ELSE NULL
6 END ) sl,
7 ( CASE
8 WHEN d.dname = 'RESEARCH' THEN d.loc
9 ELSE NULL
10 END ) rl
11 FROM emp e
12 join dept d
13 ON ( e.deptno = d.deptno );
DEPTNO ENAME SL RL
--------------- ---------- ------------- -------------
20 SMITH DALLAS
30 ALLEN CHICAGO
30 WARD CHICAGO
20 JONES DALLAS
30 MARTIN CHICAGO
30 BLAKE CHICAGO
10 CLARK
20 SCOTT DALLAS
10 KING
30 TURNER CHICAGO
20 ADAMS DALLAS
30 JAMES CHICAGO
20 FORD DALLAS
10 MILLER
14 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 615168685
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 700 | 6 (0)| 00:00:01 |
|* 1 | HASH JOIN | | 14 | 700 | 6 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| DEPT | 4 | 120 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMP | 14 | 280 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("E"."DEPTNO"="D"."DEPTNO")
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
orclz>
|
|
|
Re: Query having multiple subqueries to be simplified [message #689605 is a reply to message #689603] |
Tue, 20 February 2024 11:16   |
cookiemonster
Messages: 13965 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
That'll work so long as key of ods_infusion_data is just hub_key. But if it's hub_key and infsn_typ then that approach is going to result in the query returning duplicated rows and you'll have to use min/max and group by to get back to the original result set.
I'd just get rid of the inline views and use ANSI syntax
FROM lh_ods.ods_hub_data ohd
JOIN <tables that arent outer joined>
LEFT JOIN lh_ods.ods_infusion_data phi1 ON ohd.hub_key = phi1.hub_key
AND phi1.infsn_typ = 'PRIMARY_HOME_INFUSION_1'
LEFT JOIN lh_ods.ods_infusion_data phi2 ON ohd.hub_key = phi2.hub_key
AND phi1.infsn_typ = 'PRIMARY_HOME_INFUSION_2'
.....
[Updated on: Tue, 20 February 2024 11:17] Report message to a moderator
|
|
|
|
Re: Query having multiple subqueries to be simplified [message #689607 is a reply to message #689606] |
Tue, 20 February 2024 11:23   |
cookiemonster
Messages: 13965 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Actually....
this:
AND (
ohd.record_type <> 'CM'
AND ohd.hub_sr_stat_dt IS NOT NULL
OR ohd.record_type = 'CM'
AND ohd.hub_sr_stat_dt IS NOT NULL
OR ohd.record_type = 'CM'
AND ohd.hub_sr_stat_dt IS NULL
)
is saying that if record_type is not CM then hub_sr_stat_dt must be not null, but if record_type is CM then it doesn't matter what hub_sr_stat_dt is set to.
Which is logicaly equivalent to this:
AND (ohd.hub_sr_stat_dt IS NOT NULL OR ohd.record_type = 'CM')
There's 3 clauses in that form
|
|
|
Re: Query having multiple subqueries to be simplified [message #689611 is a reply to message #689605] |
Wed, 21 February 2024 03:35   |
John Watson
Messages: 8965 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
I like the CM solution, because it makes use of an ANSI join construct that I have always (until now) thought was stupid. For me the great thing about ANSI is that you have a clear separation of join predicates (in a JOIN clause) and filter predicates (in a WHERE clause). The fact that you can include a filter in the join seemed stupid to me, but this is a clear example of where it is useful:orclz>
orclz> SELECT e.deptno,
2 e.ename,
3 s.loc,
4 r.loc
5 FROM emp e
6 left join dept s
7 ON e.deptno = s.deptno
8 AND s.dname = 'SALES'
9 left join dept r
10 ON e.deptno = r.deptno
11 AND r.dname = 'RESEARCH';
DEPTNO ENAME LOC LOC
--------------- ---------- ------------- -------------
20 SMITH DALLAS
20 JONES DALLAS
20 SCOTT DALLAS
20 ADAMS DALLAS
20 FORD DALLAS
30 ALLEN CHICAGO
30 WARD CHICAGO
30 MARTIN CHICAGO
30 BLAKE CHICAGO
30 TURNER CHICAGO
30 JAMES CHICAGO
10 CLARK
10 KING
10 MILLER
14 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 487804956
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 686 | 9 (0)| 00:00:01 |
|* 1 | HASH JOIN OUTER | | 14 | 686 | 9 (0)| 00:00:01 |
|* 2 | HASH JOIN OUTER | | 14 | 406 | 6 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMP | 14 | 126 | 3 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL| DEPT | 1 | 20 | 3 (0)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | DEPT | 1 | 20 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("E"."DEPTNO"="R"."DEPTNO"(+))
2 - access("E"."DEPTNO"="S"."DEPTNO"(+))
4 - filter("S"."DNAME"(+)='SALES')
5 - filter("R"."DNAME"(+)='RESEARCH')
Note
-----
- this is an adaptive plan
orclz> There is still the issue of the outer join constraining the choice of driving table and multiple hits on the probe table, so performance might be a consideration.
|
|
|
Re: Query having multiple subqueries to be simplified [message #689612 is a reply to message #689611] |
Wed, 21 February 2024 04:34  |
cookiemonster
Messages: 13965 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
I'm seriously bemused that you didn't spot the use of that a long time ago.
It's the same as this (old oracle syntax)
FROM lh_ods.ods_hub_data ohd,
lh_ods.ods_infusion_data phi1,
lh_ods.ods_infusion_data phi2
WHERE ohd.hub_key = phi1.hub_key(+)
AND phi1.infsn_typ(+) = 'PRIMARY_HOME_INFUSION_1'
AND ohd.hub_key = phi2.hub_key(+)
AND phi1.infsn_typ(+) = 'PRIMARY_HOME_INFUSION_2'
If you've got filter conditions against a table that is outer-joined then you either make that condition part of the outer join itself or you end up with a line like this in the where clause:
(<outer joined table column> = 'value' OR <a not null column on the outer joined table> IS NULL)
Or you make the outer-joined table an inline view (as OP did) or a WITH clause subquery and stick the filter in there.
But all those options are harder to follow than just using the filter directly in ANSI outer join.
|
|
|