OraFAQ Forum: SQL & PL/SQL » Query having multiple subqueries to be simplified

Home » SQL & PL/SQL » SQL & PL/SQL » Query having multiple subqueries to be simplified (11g enterprise version )

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Query having multiple subqueries to be simplified [message #689602]

Tue, 20 February 2024 01:28

harishankar_kar
Messages: 22
Registered: July 2014
Location: India

Junior Member

Hi,
Please check the query once and let me know how can we simplify the query .

Attachment: query_needs_simplification.sql
(Size: 22.72KB, Downloaded 359 times)

Report message to a moderator

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: 8931
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>

Report message to a moderator

Re: Query having multiple subqueries to be simplified [message #689605 is a reply to message #689603]

Tue, 20 February 2024 11:16

cookiemonster
Messages: 13920
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 #689606 is a reply to message #689605]

Tue, 20 February 2024 11:19

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

I doubt there's much that can be done to actually simplify that query, but using ANSI syntax would make it more readable

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: 13920
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

Report message to a moderator

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: 8931
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.

Report message to a moderator

Re: Query having multiple subqueries to be simplified [message #689612 is a reply to message #689611]

Wed, 21 February 2024 04:34

cookiemonster
Messages: 13920
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.

Report message to a moderator

Previous Topic:	Help needed in Tricky Group by
Next Topic:	update table attribute help!

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Apr 27 06:42:17 CDT 2024