1

I am trying to build a complex query which has two levels (one parent query having a nested query). In a very low level I have something like this:

SELECT *
   from (SELECT a.1 a1,
                a.2 a2,
                a.3 a3,
                b.1 b1,
                b.2 b2,
                b.3 b3
           from a
           join b
             on a.1 = b.1) sq
  where sq.b3 = '123'

With this approach the query turns to take a lot of time while when doing like below is immediate...

SELECT *
   from (SELECT a.1 a1,
                a.2 a2,
                a.3 a3,
                b.1 b1,
                b.2 b2,
                b.3 b3
           from a
           join b
             on a.1 = b.1
           where sq.b3 = '123') sq

I understand that nested queries can affect the performance but I was expecting the optimizer to be able to "improve" it and bring more efficiency...

The thing is that I need to use the first approach as in the parent query I am putting quite a lot of validations that cannot be placed in the nested query due to the big coplexity of it...

What am wrong with?

EDIT: ADDING MORE INFO

Below I added a reduced version of the query in which I still see performance problems (putting the filter in OUTSIDE the nested query or INSIDE). Also corresponding execution plans are added:

FILTERING INSIDE THE NESTED QUERY (good results)

 select resultset.*
   from (select first_value(ord.stcust ignore nulls) over(partition by shipment.ship_id
                                                           order by ord.ordnum desc range between unbounded preceding
                                                             and unbounded following) as shp_stcust,
                first_value(ord.st_adr_id ignore nulls) over(partition by shipment.ship_id
                                                              order by ord.ordnum desc range between unbounded preceding
                                                                and unbounded following) as shp_st_adr_id,
                first_value(ord.rtcust ignore nulls) over(partition by shipment.ship_id
                                                           order by ord.ordnum desc range between unbounded preceding
                                                             and unbounded following) as shp_rtcust,
                first_value(ord.rt_adr_id ignore nulls) over(partition by shipment.ship_id
                                                              order by ord.ordnum desc range between unbounded preceding
                                                                and unbounded following) as shp_rt_adr_id,
                first_value(ord.btcust ignore nulls) over(partition by shipment.ship_id
                                                           order by ord.ordnum desc range between unbounded preceding
                                                             and unbounded following) as shp_btcust,
                first_value(ord.bt_adr_id ignore nulls) over(partition by shipment.ship_id
                                                              order by ord.ordnum desc range between unbounded preceding
                                                                and unbounded following) as shp_bt_adr_id,
                first_value(ord.brcust ignore nulls) over(partition by shipment.ship_id
                                                           order by ord.ordnum desc range between unbounded preceding
                                                             and unbounded following) as shp_brcust,
                first_value(ord.br_adr_id ignore nulls) over(partition by shipment.ship_id
                                                              order by ord.ordnum desc range between unbounded preceding
                                                                and unbounded following) as shp_br_adr_id,
                invdtl.dtlnum
           from shipment
          inner
           join shipment_line
             on (shipment.ship_id = shipment_line.ship_id)
          inner
           join invdtl
             on (shipment_line.ship_line_id = invdtl.ship_line_id)
          inner
           join invsub
             on (invdtl.subnum = invsub.subnum)
          inner
           join invlod
             on (invsub.lodnum = invlod.lodnum)
          inner
           join ord_line
             on (shipment_line.ordnum = ord_line.ordnum and shipment_line.ordlin = ord_line.ordlin and shipment_line.ordsln = ord_line.ordsln and shipment_line.wh_id = ord_line.wh_id and shipment_line.client_id = ord_line.client_id)
          inner
           join ord
             on (ord_line.ordnum = ord.ordnum and ord_line.wh_id = ord.wh_id and ord_line.client_id = ord.client_id)
           left
           join ctnmst invlod_ctnmst
             on (invlod.vc_ctncod = invlod_ctnmst.ctncod and invlod.wh_id = invlod_ctnmst.wh_id)
           left
           join ctnmst invsub_ctnmst
             on (invsub.vc_ctncod = invsub_ctnmst.ctncod and invlod.wh_id = invsub_ctnmst.wh_id)
           left
           join prtftp_dtl
             on (invdtl.prtnum = prtftp_dtl.prtnum and invdtl.ftpcod = prtftp_dtl.ftpcod and invlod.wh_id = prtftp_dtl.wh_id and invdtl.prt_client_id = prtftp_dtl.prt_client_id and 0 = prtftp_dtl.uomlvl)
          where invdtl.dtlnum = 'D00000525035') resultset

EXECUTION PLAN FILTERING INSIDE THE NESTED QUERY

Plan hash value: 337977667

------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |                  |     1 |   398 |    11  (10)| 00:00:01 |
|   1 |  VIEW                                   |                  |     1 |   398 |    11  (10)| 00:00:01 |
|   2 |   WINDOW SORT                           |                  |     1 |   869 |    11  (10)| 00:00:01 |
|   3 |    NESTED LOOPS OUTER                   |                  |     1 |   869 |    10   (0)| 00:00:01 |
|   4 |     NESTED LOOPS OUTER                  |                  |     1 |   842 |     9   (0)| 00:00:01 |
|   5 |      VIEW                               |                  |     1 |   834 |     8   (0)| 00:00:01 |
|   6 |       NESTED LOOPS                      |                  |     1 |   284 |     8   (0)| 00:00:01 |
|   7 |        NESTED LOOPS                     |                  |     1 |   196 |     7   (0)| 00:00:01 |
|   8 |         NESTED LOOPS OUTER              |                  |     1 |   171 |     6   (0)| 00:00:01 |
|   9 |          NESTED LOOPS                   |                  |     1 |   163 |     5   (0)| 00:00:01 |
|  10 |           NESTED LOOPS                  |                  |     1 |   143 |     4   (0)| 00:00:01 |
|  11 |            NESTED LOOPS                 |                  |     1 |   132 |     3   (0)| 00:00:01 |
|  12 |             NESTED LOOPS                |                  |     1 |    85 |     2   (0)| 00:00:01 |
|  13 |              TABLE ACCESS BY INDEX ROWID| INVDTL           |     1 |    56 |     1   (0)| 00:00:01 |
|* 14 |               INDEX UNIQUE SCAN         | INVDTL_PK        |     1 |       |     1   (0)| 00:00:01 |
|  15 |              TABLE ACCESS BY INDEX ROWID| INVSUB           |     1 |    29 |     1   (0)| 00:00:01 |
|* 16 |               INDEX UNIQUE SCAN         | INVSUB_PK        |     1 |       |     1   (0)| 00:00:01 |
|  17 |             TABLE ACCESS BY INDEX ROWID | SHIPMENT_LINE    |     1 |    47 |     1   (0)| 00:00:01 |
|* 18 |              INDEX UNIQUE SCAN          | SHIPMENT_LINE_PK |     1 |       |     1   (0)| 00:00:01 |
|* 19 |            INDEX UNIQUE SCAN            | SHIPMENT_PK      |     1 |    11 |     1   (0)| 00:00:01 |
|  20 |           TABLE ACCESS BY INDEX ROWID   | INVLOD           |     1 |    20 |     1   (0)| 00:00:01 |
|* 21 |            INDEX UNIQUE SCAN            | INVLOD_PK        |     1 |       |     1   (0)| 00:00:01 |
|* 22 |          INDEX UNIQUE SCAN              | CTNMST_PK        |     1 |     8 |     1   (0)| 00:00:01 |
|* 23 |         INDEX UNIQUE SCAN               | ORD_LINE_PK      |     1 |    25 |     1   (0)| 00:00:01 |
|  24 |        TABLE ACCESS BY INDEX ROWID      | ORD              |     1 |    88 |     1   (0)| 00:00:01 |
|* 25 |         INDEX UNIQUE SCAN               | ORD_PK           |     1 |       |     1   (0)| 00:00:01 |
|* 26 |      INDEX UNIQUE SCAN                  | CTNMST_PK        |     1 |     8 |     1   (0)| 00:00:01 |
|* 27 |     TABLE ACCESS BY INDEX ROWID         | PRTFTP_DTL       |     1 |    27 |     1   (0)| 00:00:01 |
|* 28 |      INDEX RANGE SCAN                   | PRTFTP_DTL_IDX1  |     3 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  14 - access("INVDTL"."DTLNUM"='D00000525035')
  16 - access("INVDTL"."SUBNUM"="INVSUB"."SUBNUM")
  18 - access("SHIPMENT_LINE"."SHIP_LINE_ID"="INVDTL"."SHIP_LINE_ID")
  19 - access("SHIPMENT"."SHIP_ID"="SHIPMENT_LINE"."SHIP_ID")
  21 - access("INVSUB"."LODNUM"="INVLOD"."LODNUM")
  22 - access("INVLOD"."VC_CTNCOD"="INVLOD_CTNMST"."CTNCOD"(+) AND 
              "INVLOD"."WH_ID"="INVLOD_CTNMST"."WH_ID"(+))
  23 - access("SHIPMENT_LINE"."ORDNUM"="ORD_LINE"."ORDNUM" AND 
              "SHIPMENT_LINE"."ORDLIN"="ORD_LINE"."ORDLIN" AND "SHIPMENT_LINE"."ORDSLN"="ORD_LINE"."ORDSLN" AND 
              "SHIPMENT_LINE"."WH_ID"="ORD_LINE"."WH_ID" AND "SHIPMENT_LINE"."CLIENT_ID"="ORD_LINE"."CLIENT_ID")
  25 - access("ORD_LINE"."ORDNUM"="ORD"."ORDNUM" AND "ORD_LINE"."WH_ID"="ORD"."WH_ID" AND 
              "ORD_LINE"."CLIENT_ID"="ORD"."CLIENT_ID")
  26 - access("INVSUB"."VC_CTNCOD"="INVSUB_CTNMST"."CTNCOD"(+) AND 
              "INVLOD"."WH_ID"="INVSUB_CTNMST"."WH_ID"(+))
  27 - filter("PRTFTP_DTL"."UOMLVL"(+)=0)
  28 - access("from$_subquery$_016"."QCSJ_C000000001200008"="PRTFTP_DTL"."WH_ID"(+) AND 
              "from$_subquery$_016"."QCSJ_C000000001200012"="PRTFTP_DTL"."PRTNUM"(+) AND 
              "from$_subquery$_016"."QCSJ_C000000001200010"="PRTFTP_DTL"."PRT_CLIENT_ID"(+) AND 
              "from$_subquery$_016"."QCSJ_C000000001200090"="PRTFTP_DTL"."FTPCOD"(+))

FILTERING OUTSIDE THE NESTED QUERY (slow results)

select resultset.*
   from (select first_value(ord.stcust ignore nulls) over(partition by shipment.ship_id
                                                           order by ord.ordnum desc range between unbounded preceding
                                                             and unbounded following) as shp_stcust,
                first_value(ord.st_adr_id ignore nulls) over(partition by shipment.ship_id
                                                              order by ord.ordnum desc range between unbounded preceding
                                                                and unbounded following) as shp_st_adr_id,
                first_value(ord.rtcust ignore nulls) over(partition by shipment.ship_id
                                                           order by ord.ordnum desc range between unbounded preceding
                                                             and unbounded following) as shp_rtcust,
                first_value(ord.rt_adr_id ignore nulls) over(partition by shipment.ship_id
                                                              order by ord.ordnum desc range between unbounded preceding
                                                                and unbounded following) as shp_rt_adr_id,
                first_value(ord.btcust ignore nulls) over(partition by shipment.ship_id
                                                           order by ord.ordnum desc range between unbounded preceding
                                                             and unbounded following) as shp_btcust,
                first_value(ord.bt_adr_id ignore nulls) over(partition by shipment.ship_id
                                                              order by ord.ordnum desc range between unbounded preceding
                                                                and unbounded following) as shp_bt_adr_id,
                first_value(ord.brcust ignore nulls) over(partition by shipment.ship_id
                                                           order by ord.ordnum desc range between unbounded preceding
                                                             and unbounded following) as shp_brcust,
                first_value(ord.br_adr_id ignore nulls) over(partition by shipment.ship_id
                                                              order by ord.ordnum desc range between unbounded preceding
                                                                and unbounded following) as shp_br_adr_id,
                invdtl.dtlnum
           from shipment
          inner
           join shipment_line
             on (shipment.ship_id = shipment_line.ship_id)
          inner
           join invdtl
             on (shipment_line.ship_line_id = invdtl.ship_line_id)
          inner
           join invsub
             on (invdtl.subnum = invsub.subnum)
          inner
           join invlod
             on (invsub.lodnum = invlod.lodnum)
          inner
           join ord_line
             on (shipment_line.ordnum = ord_line.ordnum and shipment_line.ordlin = ord_line.ordlin and shipment_line.ordsln = ord_line.ordsln and shipment_line.wh_id = ord_line.wh_id and shipment_line.client_id = ord_line.client_id)
          inner
           join ord
             on (ord_line.ordnum = ord.ordnum and ord_line.wh_id = ord.wh_id and ord_line.client_id = ord.client_id)
           left
           join ctnmst invlod_ctnmst
             on (invlod.vc_ctncod = invlod_ctnmst.ctncod and invlod.wh_id = invlod_ctnmst.wh_id)
           left
           join ctnmst invsub_ctnmst
             on (invsub.vc_ctncod = invsub_ctnmst.ctncod and invlod.wh_id = invsub_ctnmst.wh_id)
           left
           join prtftp_dtl
             on (invdtl.prtnum = prtftp_dtl.prtnum and invdtl.ftpcod = prtftp_dtl.ftpcod and invlod.wh_id = prtftp_dtl.wh_id and invdtl.prt_client_id = prtftp_dtl.prt_client_id and 0 = prtftp_dtl.uomlvl)
          ) resultset where resultset.dtlnum = 'D00000525035'

EXECUTION PLAN FILTERING OUTSIDE NESTED QUERY

Plan hash value: 2559978577

--------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name               | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                    |   570K|   216M|       |   105K  (1)| 00:21:09 |
|*  1 |  VIEW                           |                    |   570K|   216M|       |   105K  (1)| 00:21:09 |
|   2 |   WINDOW SORT                   |                    |   570K|   472M|   494M|   105K  (1)| 00:21:09 |
|*  3 |    HASH JOIN RIGHT OUTER        |                    |   570K|   472M|       |  1642   (1)| 00:00:20 |
|*  4 |     TABLE ACCESS FULL           | PRTFTP_DTL         |   645 | 17415 |       |    10   (0)| 00:00:01 |
|   5 |     NESTED LOOPS OUTER          |                    |   401K|   322M|       |  1630   (1)| 00:00:20 |
|   6 |      VIEW                       |                    |   401K|   319M|       |  1627   (1)| 00:00:20 |
|*  7 |       HASH JOIN                 |                    |   401K|   108M|       |  1627   (1)| 00:00:20 |
|   8 |        TABLE ACCESS FULL        | ORD                |  1229 |   105K|       |    16   (0)| 00:00:01 |
|   9 |        NESTED LOOPS             |                    |   401K|    75M|       |  1610   (1)| 00:00:20 |
|* 10 |         HASH JOIN               |                    |   401K|    65M|       |  1606   (1)| 00:00:20 |
|  11 |          INDEX FULL SCAN        | SHIPMENT_PK        |  1161 | 12771 |       |     2   (0)| 00:00:01 |
|* 12 |          HASH JOIN              |                    |   401K|    61M|       |  1603   (1)| 00:00:20 |
|  13 |           VIEW                  | index$_join$_003   |  4523 |   207K|       |    15   (0)| 00:00:01 |
|* 14 |            HASH JOIN            |                    |       |       |       |            |          |
|  15 |             INDEX FAST FULL SCAN| SHIPMENT_LINE_PK   |  4523 |   207K|       |     4   (0)| 00:00:01 |
|  16 |             INDEX FAST FULL SCAN| SHIPMENT_LINE_IDX5 |  4523 |   207K|       |    15   (0)| 00:00:01 |
|  17 |           NESTED LOOPS          |                    |   404K|    43M|       |  1586   (1)| 00:00:20 |
|* 18 |            HASH JOIN            |                    |  7227 |   402K|       |   140   (1)| 00:00:02 |
|  19 |             TABLE ACCESS FULL   | INVSUB             |  7224 |   204K|       |    69   (0)| 00:00:01 |
|  20 |             NESTED LOOPS OUTER  |                    |  8570 |   234K|       |    70   (0)| 00:00:01 |
|  21 |              TABLE ACCESS FULL  | INVLOD             |  8570 |   167K|       |    69   (0)| 00:00:01 |
|* 22 |              INDEX UNIQUE SCAN  | CTNMST_PK          |     1 |     8 |       |     1   (0)| 00:00:01 |
|* 23 |            INDEX RANGE SCAN     | INVDTL_IDX5        |    56 |  3136 |       |     1   (0)| 00:00:01 |
|* 24 |         INDEX UNIQUE SCAN       | ORD_LINE_PK        |     1 |    25 |       |     1   (0)| 00:00:01 |
|* 25 |      INDEX UNIQUE SCAN          | CTNMST_PK          |     1 |     8 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("RESULTSET"."DTLNUM"='D00000525035')
   3 - access("from$_subquery$_016"."QCSJ_C000000001200010"="PRTFTP_DTL"."PRT_CLIENT_ID"(+) AND 
              "from$_subquery$_016"."QCSJ_C000000001200008"="PRTFTP_DTL"."WH_ID"(+) AND 
              "from$_subquery$_016"."QCSJ_C000000001200090"="PRTFTP_DTL"."FTPCOD"(+) AND 
              "from$_subquery$_016"."QCSJ_C000000001200012"="PRTFTP_DTL"."PRTNUM"(+))
   4 - filter("PRTFTP_DTL"."UOMLVL"(+)=0)
   7 - access("ORD_LINE"."ORDNUM"="ORD"."ORDNUM" AND "ORD_LINE"."WH_ID"="ORD"."WH_ID" AND 
              "ORD_LINE"."CLIENT_ID"="ORD"."CLIENT_ID")
  10 - access("SHIPMENT"."SHIP_ID"="SHIPMENT_LINE"."SHIP_ID")
  12 - access("SHIPMENT_LINE"."SHIP_LINE_ID"="INVDTL"."SHIP_LINE_ID")
  14 - access(ROWID=ROWID)
  18 - access("INVSUB"."LODNUM"="INVLOD"."LODNUM")
  22 - access("INVLOD"."VC_CTNCOD"="INVLOD_CTNMST"."CTNCOD"(+) AND 
              "INVLOD"."WH_ID"="INVLOD_CTNMST"."WH_ID"(+))
  23 - access("INVDTL"."SUBNUM"="INVSUB"."SUBNUM")
  24 - access("SHIPMENT_LINE"."ORDNUM"="ORD_LINE"."ORDNUM" AND 
              "SHIPMENT_LINE"."ORDLIN"="ORD_LINE"."ORDLIN" AND "SHIPMENT_LINE"."ORDSLN"="ORD_LINE"."ORDSLN" AND 
              "SHIPMENT_LINE"."WH_ID"="ORD_LINE"."WH_ID" AND "SHIPMENT_LINE"."CLIENT_ID"="ORD_LINE"."CLIENT_ID")
  25 - access("INVSUB"."VC_CTNCOD"="INVSUB_CTNMST"."CTNCOD"(+) AND 
              "INVLOD"."WH_ID"="INVSUB_CTNMST"."WH_ID"(+))
Improve this question
3
  • The query will have as much complexity as it needs to satisfy the business requirement. What you CAN do is structure the query such that it is as readable as possible. I have found that common table expressions (CTE) can make a big difference to readability. In fact I try to avoid the type of nested sub-query you have there whenever possible because in a 2500 line query it becomes impossible to keep track of what's what. Commented May 15, 2017 at 14:38
  • @byle.05 As you've presented your query, I would expect Oracle to run both queries (assuming your column names aren't actually numbers) with the same execution plan (i.e. it would rewrite the query behind the scenes). However, you mention your actual query is a lot more complex - it's likely that the complexity (e.g. analytic queries, etc) is causing Oracle not to be able to push the predicate/unnest the query. If you could update your question to more accurately reflect your actual query, we might stand a chance of being able to suggest alternatives for you. Commented May 15, 2017 at 15:42
  • 1
    Hi, I just added more information. I tried to reduce the query to a "presentable" approach so its easier to analyse. With this approach the query is faster but still suffers performance when putting the filter in one place or another... As mentioned by Boneist I was expecting ORacle optimizer to merge it but seems it is not able. I feel the partition by clauses are causing this but I would like to keep them because there are quite many more in the original query... So if there is some approach to achieve better results without changing the way the orignal query is structurised would be nice :) Commented May 16, 2017 at 8:53

1 Answer 1

Reset to default
1

Unfortunately, since the predicate you're trying to filter on is not part of the partition by key, Oracle won't be able to push the predicate from the outer query inside the inline view. That's because analytic functions are applied after the where clause, and changing which rows are selected could well change the results.

I know you said the recently added example query was a reduced version of your actual query, so what I'm about to suggest may not apply. However, I'm hoping that your actual query only has analytic functions on the shipment related tables, and if that's the case, the following should work.

Since your analytic functions only affect rows from the shipment, shipment_line, ord_line and ord tables, why not pull those off into a separate subquery, and then join that back to the other tables? Hopefully, that will speed things up considerably - e.g.:

WITH shipment_info AS (SELECT s.ship_id,
                              s.shipment_line_id,
                              first_value(ord.stcust ignore nulls) over(partition by shipment.ship_id
                                                                         order by ord.ordnum desc range between unbounded preceding
                                                                           and unbounded following) as shp_stcust,
                              first_value(ord.st_adr_id ignore nulls) over(partition by shipment.ship_id
                                                                            order by ord.ordnum desc range between unbounded preceding
                                                                              and unbounded following) as shp_st_adr_id,
                              first_value(ord.rtcust ignore nulls) over(partition by shipment.ship_id
                                                                         order by ord.ordnum desc range between unbounded preceding
                                                                           and unbounded following) as shp_rtcust,
                              first_value(ord.rt_adr_id ignore nulls) over(partition by shipment.ship_id
                                                                            order by ord.ordnum desc range between unbounded preceding
                                                                              and unbounded following) as shp_rt_adr_id,
                              first_value(ord.btcust ignore nulls) over(partition by shipment.ship_id
                                                                         order by ord.ordnum desc range between unbounded preceding
                                                                           and unbounded following) as shp_btcust,
                              first_value(ord.bt_adr_id ignore nulls) over(partition by shipment.ship_id
                                                                            order by ord.ordnum desc range between unbounded preceding
                                                                              and unbounded following) as shp_bt_adr_id,
                              first_value(ord.brcust ignore nulls) over(partition by shipment.ship_id
                                                                         order by ord.ordnum desc range between unbounded preceding
                                                                           and unbounded following) as shp_brcust,
                              first_value(ord.br_adr_id ignore nulls) over(partition by shipment.ship_id
                                                                            order by ord.ordnum desc range between unbounded preceding
                                                                              and unbounded following) as shp_br_adr_id
                       FROM   shipment s
                              INNER JOIN shipment_line sl ON (s.ship_id = sl.ship_id)
                              INNER JOIN ord_line ol ON (sl.ordnum = ol.ordnum AND sl.ordlin = ol.ordlin AND sl.ordsln = ol.ordsln AND sl.wh_id = ol.wh_id AND sl.client_id = ol.client_id)
                              INNER JOIN ord o ON (ol.ordnum = o.ordnum AND ol.wh_id = o.wh_id AND ol.client_id = o.client_id))
SELECT shp.shp_stcust,
       shp.shp_st_adr_id,
       shp.shp_rtcust,
       shp.shp_rt_adr_id,
       shp_btcust,
       shp_bt_adr_id,
       shp_brcust,
       shp_br_adr_id,
       invdtl.dtlnum,
       ... -- I'm assuming other columns from the ctnmst and prtftp_dtl tables are in here somewhere, otherwise there'd no point in including them in the join conditions!
FROM   invdtl
       INNER JOIN invsub ON (invdtl.subnum = invsub.subnum)
       INNER JOIN invlod ON (invsub.lodnum = invlod.lodnum)
       LEFT JOIN ctnmst invlod_ctnmst ON (invlod.vc_ctncod = invlod_ctnmst.ctncod AND invlod.wh_id = invlod_ctnmst.wh_id)
       LEFT JOIN ctnmst invsub_ctnmst ON (invsub.vc_ctncod = invsub_ctnmst.ctncod AND invsub.wh_id = invsub_ctnmst.wh_id)
       LEFT JOIN prtftp_dtl prtftp ON (invdtl.prtnum = prtftp.prtnum AND invdtl.fgpcod = prtftp.ftpcod AND invlod.wh_id = prtftp.wh_id AND invdtl.prt_client_id = prtftp.prt_client_id AND prtftp.uomlvl = 0)
       INNER JOIN shipment_info shp ON (invdtl.ship_line_id = shp.ship_line_id)
WHERE  invdtl = 'D00000525035';
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Big apologies for the delays on replying... Thanks a lot @Boneist for the example. This approach does really decrease cost of execution but as you said my query doesnt allow it due to the complexity on it... I can try to make it like that but then I am losing a lot of readibility as well as I am bringing much more complexity (not maintainable...). So instead of this I went to the approach of using an Oracle Package to do the tough calculations. WIth this performance got reduced from several minutes to 2/3 seconds. So issue solved :) Thanks again for all the tips!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.