Improving performance: Very Slow Oracle SQL Join

Question

I am a newbie in SQL querying and I am spending 3hrs to get the whole result of joining 2 queries. I have focused on using left joins and avoided using subqueries on the select statement after researching. However it is still extremely slow. I have no close friends who know sql enough to explain whats wrong or what I approach I should take. I am also new here so if this question is not allowed please inform me and I will remove it immediately.

This is the structure of the query... The first query will get the member details. The second query will get the transaction details. The relationship is, one product has many sub-plans which has many members. One product also has many transactions which is made on a per product basis. I am required to show all transactions and duplicate each line for each member. I joined the queries using the product primary key. Prior to joining, I have tested both individual queries and they turned out fine. Only 1-2 secs and I get the result. But joining the two, I end up with 3 hrs of waiting.

SELECT
MPPFF.N_DX,
MPPFF.PM_A_P,
MPPFF.FEE1,
MPPFF.FEE2,
MPPFF.FEE3,
MPPFF.FEE4,
MPPFF.FEE11,
MPPFF.FEE12,
MPPFF.FEE5,
MPPFF.N_NO,
MPPFF.SETN_DX,
MPPFF.PRIME_NO,
MPPFF.SECN_NO,
MPPFF.COMM_A,
MPPFF.TYX_NO,
MPPFF.P_NAME,
MPPFF.B_BFX,
MPPFF.B_FM,
MPPFF.B_TO,
MPPFF.BB_NAME_P,
MPPFF.BB_NAME_S,
MPPFF.REVERSE_BFX,
MPPFF.TYX_REF_NO,
MPPFF.BB_NO_AX,
MPPFF.BB_NAME_AX,
MPPFF.DXC,
MPPFF.ST,
MPPFF.DAY,
MPPFF.CE_D_PRODUCT,
MPPFF.CE_H,
MPPFF.AS_C_E,
MPPFF.BCH,
MPPFF.RCPY_NO,
MPPFF.RE_BFX,
MPPFF.A_END,
MPPFF.PLACE,
MPPFF.MEMB_DX,
MPPFF.MBR_NO,
MPPFF.MBR_TR_BFX,
MPPFF.CE_D_TERM_CE,
MPPFF.MEMBER_AS,
MPPFF.C_USER,
MPPFF.C_BFX,
MPPFF.U_USER,
MPPFF.U_BFX

FROM (
        SELECT
        FF.N_DX,
        FF.PM_A_P,
        FF.FEE1,
        FF.FEE2,
        FF.FEE3,
        FF.FEE4,
        FF.FEE11,
        FF.FEE12,
        FF.FEE5,
        FF.N_NO,
        FF.SETN_DX,
        FF.PRIME_NO,
        FF.SECN_NO,
        FF.COMM_A,
        FF.TYX_NO,
        FF.P_NAME,
        FF.B_BFX,
        FF.B_FM,
        FF.B_TO,
        FF.BB_NAME_P,
        FF.BB_NAME_S,
        FF.REVERSE_BFX,
        FF.TYX_REF_NO,
        FF.BB_NO_AX,
        FF.BB_NAME_AX,
        FF.DXC,
        FF.ST,
        FF.DAY,
        FF.CE_D_PRODUCT,
        FF.CE_H,
        FF.AS_C_E,
        FF.RCPY_NO,
        FF.RE_BFX,
        FF.A_END,
        FF.BCH,
        MPP.MBR_NO,
        MPP.MBR_TR_BFX,
        MPP.CE_D_TERM_CE,
        MPP.C_USER,
        MPP.C_BFX,
        MPP.U_USER,
        MPP.U_BFX,
        MPP.PLACE,
        MPP.MEMBER_AS,
        MPP.TYX_DX,
        MPP.AS_DX,
        MPP.PRODUCT,
        MPP.POPL_DX,
        MPP.MEMB_DX,
        FF.TYX_DX

        FROM (
                SELECT
                MBR.MEMB_DX,
                MBR.MBR_NO,
                MBR.MBR_TR_BFX,
                MBR.CE_D_TERM_CE,
                MBR.C_USER,
                MBR.C_BFX,
                MBR.U_USER,
                MBR.U_BFX,
                MPP.PLACE,
                MPP.MEMBER_AS,
                MPP.TYX_DX,
                MPP.AS_DX,
                MPP.PRODUCT,
                MPP.POPL_DX

                FROM (
                        SELECT
                        MPP.PLACE,
                        MPP.MEMBER_AS,
                        MPP.TYX_DX,
                        MPP.AS_DX,
                        MPP.PRODUCT,
                        MPP.POPL_DX,
                        MMP.MEMB_DX

                        FROM(
                                SELECT
                                MPP.PLACE,
                                MPP.TYX_AS_DXC MEMBER_AS,
                                MPP.TYX_DX,
                                MPP.AS_DX,
                                MPP.POPL_DX,
                                RPT.PRODUCT

                                FROM
                                TABLE1 MPP

                                LEFT JOIN (
                                        SELECT
                                        SUBSTR(CE_D_PRODUCT,9) PRODUCT,
                                        AS_DX
                                        FROM
                                        TABLE6 RPT,
                                        TABLE7 PP
                                        WHERE
                                        PP.PRTY_DX = RPT.PRTY_DX
                                ) RPT
                                ON  MPP.AS_DX = RPT.AS_DX

                        ) MPP

                        LEFT JOIN (
                                SELECT
                                POPL_DX,
                                MEMB_DX
                                FROM
                                TABLE4
                        )MMP
                        ON MPP.POPL_DX=MMP.POPL_DX

                ) MPP,
                (
                        SELECT
                        MBR.MEMB_DX,
                        MBR.MBR_NO,
                        MBR.TERM_BFX MBR_TR_BFX,
                        MBR.CE_D_TERM_CE,
                        MBR.C_USER,
                        MBR.C_BFX,
                        MBR.U_USER,
                        MBR.U_BFX

                        FROM
                        TABLE8 MBR
                ) MBR
                WHERE
                MPP.MEMB_DX = MBR.MEMB_DX
        ) MPP
        INNER JOIN
        (
                SELECT
                FF.N_DX,
                ROUND(CB.FEE5 * FF.RATE,2) PM_A_P,
                CB.FEE1,
                CB.FEE2,
                CB.FEE3,
                CB.FEE4,
                CB.FEE11,
                CB.FEE12,
                CB.FEE5,
                FF.N_NO,
                FF.SETN_DX,
                FF.PRIME_NO,
                FF.SECN_NO,
                FF.COMM_A,
                FF.TYX_NO,
                FF.P_NAME_1||', '||FF.P_NAME_2||' '||FF.P_NAME_3 P_NAME,
                FF.B_BFX,
                FF.B_FM,
                FF.B_TO,
                FF.BB_NAME_1_P||', '||FF.BB_NAME_2_P BB_NAME_P,
                FF.BB_NAME_1_S||', '||FF.BB_NAME_2_S BB_NAME_S,
                CB.REVERSE_BFX,
                FF.TYX_REF_NO,
                FF.BB_NO_AX,
                FF.BB_NAME_1_AX||' '|| FF.BB_NAME_2_AX BB_NAME_AX,
                CASE 
                        WHEN FF.CE_D_ST IN ('A', 'B', 'C') THEN 'AC'
                        WHEN FF.DAY >1 THEN 'NEW'
                        ELSE 'AB'
                END DXC,
                FF.CE_D_ST ST,
                FF.DAY,
                FF.CE_D_PRODUCT,
                FF.CE_D_COMP CE_H,
                FF.AS_C AS_C_E,
                FF.RCPY_NO,
                FF.RE_BFX,
                ROUND(CB.A_S,2) A_END,
                FF.TYX_DX,
                MP.BCH

                FROM
                TABLE2 CB,
                TABLE3 FF

                LEFT JOIN (
                        SELECT
                        SUBSTR(CE_D_BCH_O,13) BCH,
                        TYX_DX
                        FROM
                        TABLE5 MP
                )MP
                ON MP.TYX_DX = FF.TYX_DX

                WHERE
                FF.SETN_DX = CB.SETN_DX AND
                EXTRACT( YEAR FROM FF.EFF_BFX) >=2013
        ) FF    
        ON MPP.TYX_DX = FF.TYX_DX

)MPPFF
;

Thank you for replying. I do not know what CTEs are but it's something for me to research now. :) — Nic, Sep 28 '16 at 08:27
To try to optimize a query, it's necessary to know the table structure, including indexes, the explain plan of the current statement and something on the number of rows in each table; please post these informations — Aleksej, Sep 28 '16 at 08:27
Thank you for replying. I do not know about the indexing because I did not make the DB and tables but I will post the other informations. — Nic, Sep 28 '16 at 08:37
You can get informations about indexes with a query on `all_indexes` and similar — Aleksej, Sep 28 '16 at 08:42

score 9 · Accepted Answer · answered Sep 29 '16 at 04:18

Use ROWNUM to prevent optimizer transformations from degrading the performance.

You are encountering a common problem - two queries run fast separately but run slow when put together. Oracle does not have to run the queries in the order they are written. It can merge views, push predicates around, and generally completely re-write the query to run in a different order. Normally this is a great thing because you don't want to have to worry about which physical order to join tables. But sometimes Oracle applies the wrong transformations and the results are disastrous.

There are two ways to solve these problems.

Look at table structures, the statements, the execution plans, SQL monitoring or traces, statistics, etc. Try to find out which operation is slow, and why (use cardinality as your guide), and then try to fix it. This process can easily take hours, maybe even days, but it's the best way to learn.
Stop the optimizer from combining the queries with a simple trick. There are a few ways to do this but in my experience the simplest way is to add the pseudo-column ROWNUM to any inline view that you do not want transformed. ROWNUM is a special column that tells Oracle "this query block must be returned in a specific way, don't do anything to it".

Change this:

--This is slow:
select ...
from
(
    --This is fast:
    select ...
) inline_view1
join
(
    --This is fast:
    select ...
) inline_view2
    on ...

to this:

--Now this is fast.
select ...
from
(
    --This is fast:
    select rownum /*add rownum to prevent slow transformations*/, ...
) inline_view1
join
(
    --This is fast:
    select rownum /*add rownum to prevent slow transformations*/, ...
) inline_view2
    on ...

In your code I believe the two inline views to modify would be the outer-most MPP and FF.

On a side note, I disagree with with some of the other comments and answers.

A CTE will not help here since none of the tables are used twice.
You don't always need to know a million details about the query to tune it. Unless you have the time and want to improve your skills.
I think your over-all query structure is good. You are on the right path to building great SQL statements. Inline views are the key to writing SQL - build small units of code, combine them in simple steps, repeat. Putting all the tables together in one massive join is a recipe for spaghetti code. Although I agree with others that you should avoid the old-fashioned join syntax. And the query would really benefit from some comments and more meaningful names. And don't feel afraid to put all the select list items on one line. Having a 500-column line isn't ideal, but you want to focus on the joins, not the simple list of columns.

Really? Using rownum and not using it tricks the optimizer? I didn't know that. I agree that building a query step by step and not simply joining all tables is a good way of writing queries. Here however the tables *are* all joined, only in an obfuscated way, which even results in outer joins not working. Derived tables are great, especially for the check of existence with `IN` or `EXISTS` rather than joining and having to deal with duplicates, and for aggregations. But as mentioned, in the original query only some tables get joined and their fields shown. Nothing special. — Thorsten Kettner, Sep 29 '16 at 06:06
@ThorstenKettner Yes, and in my experience it's the only safe way to really stop the optimizer from making changes. It's sort of mentioned in [the manual](http://docs.oracle.com/database/121/SQLRF/pseudocolumns009.htm#SQLRF00255): "Using ROWNUM in a query can affect view optimization." The *official* way to force this separation would probably be some combination of hints like `NO_MERGE`, `NO_PUSH_PRED`, `NO_QUERY_TRANSFORMATION`, etc. But due to the way developers treat hints, it's not a safe way - the next programmer will likely change an alias and not realize that it broke a hint. — Jon Heller, Sep 29 '16 at 06:41
I tried this and it works. I only added rownum in one of the inline views though. — Johnny Wu, Mar 15 '19 at 01:00
Used RowNum, and 35 minutes dropped to 1.2 seconds. I will definitely remember that trick. — AnthonyVO, Dec 22 '20 at 17:38

score 1 · Answer 2 · answered Sep 28 '16 at 12:12

Your query is almost unreadable, because of all the nesting. And you are mixing pre 1992 style joins with current join syntax. Don't use the outdated comma-separated join syntax. It is prone to errors. All your outer-joins are void, because at some point you will always have criteria that dismisses outer-joined records, such as when inner-joining table8 on the outer-joined table4's memb_dx.

Your query seems to translate to

select
  <several fields from the tables>
from table1 mpp
join table6 rpt on rpt.as_dx = mpp.as_dx
join table7 pp on pp.prty_dx = rpt.prty_dx
join table4 mmp on mmp.popl_dx = mpp.popl_dx
join table8 mbr on mpp.memb_dx = mmp.memb_dx
join table3 ff on ff.tyx_dx = mpp.tyx_dx and extract(year from ff.eff_bfx) >= 2013
join table2 cb on ff.setn_dx = cb.setn_dx
left join table5 mp on mp.tyx_dx = ff.tyx_dx;

and maybe you want it to be

select
  <several fields from the tables>
from table1 mpp
left join table6 rpt on rpt.as_dx = mpp.as_dx
left join table7 pp on pp.prty_dx = rpt.prty_dx
left join table4 mmp on mmp.popl_dx = mpp.popl_dx
left join table8 mbr on mpp.memb_dx = mmp.memb_dx
join table3 ff on ff.tyx_dx = mpp.tyx_dx and extract(year from ff.eff_bfx) >= 2013
join table2 cb on ff.setn_dx = cb.setn_dx
left join table5 mp on mp.tyx_dx = ff.tyx_dx;

instead or something along the lines. Get rid of all the nesting and stay with a clear and easy to read from clause.

score 1 · Answer 3 · answered Sep 28 '16 at 13:11

One thing others haven't mentioned is the use of

EXTRACT( YEAR FROM FF.EFF_BFX) >=2013

This applies the EXTRACT function to every row selected from TABLE3 (I believe that's what FF refers to at this point in the query). I suggest replacing the above with

FF.EFF_BFX >= TO_DATE('01-JAN-2013', 'DD-MON-YYYY')

or something similar. This requires only a single call to TO_DATE to generate the date constant, which is then compared directly to FF.EFF_BFX, which appears to be a column of type DATE.

This query also uses the same table alias (e.g. FF, MPP, etc) multiple times for different entities in different contexts. In my opinion this is bad practice, and I suggest you rework your query to use a unique alias for each entity, which will make the query easier to understand.

As others have mentioned, getting rid of the pre-1992 joins in the WHERE clause would also help clarify what's going on, as would getting rid of the long column lists. A couple of the subqueries could be eliminated as well which would make the query cleaner and clearer.

After dealing with all the above I get the following:

SELECT *
  FROM (SELECT *
          FROM TABLE1 MPP
          LEFT OUTER JOIN (SELECT SUBSTR(CE_D_PRODUCT, 9) PRODUCT,
                                  AS_DX
                             FROM TABLE6 RPT
                             INNER JOIN TABLE7 PP
                               ON PP.PRTY_DX = RPT.PRTY_DX) RPT
            ON MPP.AS_DX = RPT.AS_DX
          LEFT OUTER JOIN TABLE4 MMP
            ON MPP.POPL_DX = MMP.POPL_DX) MPP
  INNER JOIN TABLE8 MBR
    ON MPP.MEMB_DX = MBR.MEMB_DX
  INNER JOIN (SELECT FF.*,
                     CB.*,
                     ROUND(CB.FEE5 * FF.RATE,2) PM_A_P,
                     FF.P_NAME_1     || ', ' || FF.P_NAME_2 || ' ' || FF.P_NAME_3 P_NAME,
                     FF.BB_NAME_1_P  || ', ' || FF.BB_NAME_2_P BB_NAME_P,
                     FF.BB_NAME_1_S  || ', ' || FF.BB_NAME_2_S BB_NAME_S,
                     FF.BB_NAME_1_AX || ' '  || FF.BB_NAME_2_AX BB_NAME_AX,
                     CASE 
                       WHEN FF.CE_D_ST IN ('A', 'B', 'C') THEN 'AC'
                       WHEN FF.DAY > 1 THEN 'NEW'
                       ELSE 'AB'
                     END DXC,
                     ROUND(CB.A_S,2) A_END,
                     SUBSTR(MP.CE_D_BCH_O, 13) AS BCH
                FROM TABLE2 CB
                INNER JOIN TABLE3 FF
                  ON FF.SETN_DX = CB.SETN_DX
                LEFT OUTER JOIN TABLE5 MP
                  ON MP.TYX_DX = FF.TYX_DX
                WHERE FF.EFF_BFX >= TO_DATE('01-JAN-2013', 'DD-MON-YYYY')) FF
    ON MPP.TYX_DX = FF.TYX_DX

Best of luck.

The idea to re-write the condition is good, but only works with an appropriate index given of course. And I imagine that the condition is even so broad as to still get a big percentage of what is in the table, so even an index on table3(eff_bfx,tyx_dx) might not be used. A covering index should, though. I completely agree that using the same alias for different things is one of the reasons that makes the original query so hard to read. — Thorsten Kettner, Sep 29 '16 at 05:54

score 0 · Answer 4 · answered Sep 28 '16 at 12:15

I tried to make your query more readable:

SELECT MPPFF.*
FROM 
    (SELECT FF.*, MPP.*
    FROM 
        (SELECT MBR.*, MPP.*
        FROM 
            (SELECT MPP.*, MMP.*
            FROM 
                (SELECT MPP.*, RPT.*
                FROM TABLE1 MPP
                LEFT JOIN (SELECT * FROM TABLE6 RPT, TABLE7 PP WHERE PP.PRTY_DX = RPT.PRTY_DX) RPT ON  MPP.AS_DX = RPT.AS_DX) MPP
            LEFT JOIN (SELECT * FROM TABLE4) MMP ON MPP.POPL_DX=MMP.POPL_DX) MPP, 
            (SELECT MBR.* FROM TABLE8 MBR) MBR
        WHERE MPP.MEMB_DX = MBR.MEMB_DX) MPP
    INNER JOIN (SELECT FF.*, CB.* FROM TABLE2 CB, TABLE3 FF
        LEFT JOIN (SELECT * FROM TABLE5 MP ) MP ON MP.TYX_DX = FF.TYX_DX
    WHERE FF.SETN_DX = CB.SETN_DX 
        AND EXTRACT( YEAR FROM FF.EFF_BFX) >=2013) FF ON MPP.TYX_DX = FF.TYX_DX) MPPFF
;

You select 8 different tables and the only WHERE condition is EXTRACT( YEAR FROM FF.EFF_BFX) >= 2013

Unless the tables are tiny it will always take some time to query them all together.

Why do you mix ANSI join syntax and old-style Oracle join syntax?

You are making a good point here mentioning that there exist almost no criteria that would limit the result set. Yes, however one re-writes the query, there may still be a lot of data to retrieve and combine, which can naturally take quite some time. — Thorsten Kettner, Sep 28 '16 at 12:43

Improving performance: Very Slow Oracle SQL Join

4 Answers4

Linked