I have the following query and in the from clause there is a left join with ga and following other tables.
should we use left join keyword for all other tables after ga table or we can use as it is in the query. Is there any performance issues with this query?
query:
from
a@db_link st left join (Select a,b,c,d
from b@db_link where id = 'AD' and num = 4) ga
on st.compensationdate = ga.compensationdate
and st.salestransactionseq = ga.salestransactionseq ,
b@db_link ta,
c@db_link cr,
d@db_link crd_typ,
e@db_link evt_typ,
f@db_link disputes
where st.salestransactionseq = ta.salestransactionseq
and st.id = 'AD'
This is the query plan:
Plan hash value: 3767304471
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | Inst |
------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT REMOTE | | 1 | 661 | 342 (1)| 00:00:01 | | | |
| 1 | NESTED LOOPS | | 1 | 661 | 342 (1)| 00:00:01 | | | |
| 2 | NESTED LOOPS | | 1 | 661 | 342 (1)| 00:00:01 | | | |
| 3 | NESTED LOOPS | | 1 | 612 | 342 (1)| 00:00:01 | | | |
| 4 | NESTED LOOPS | | 1 | 564 | 342 (1)| 00:00:01 | | | |
|* 5 | HASH JOIN | | 1 | 549 | 342 (1)| 00:00:01 | | | |
| 6 | NESTED LOOPS | | 1 | 503 | 0 (0)| 00:00:01 | | | |
| 7 | NESTED LOOPS | | 1 | 503 | 0 (0)| 00:00:01 | | | |
| 8 | NESTED LOOPS | | 1 | 450 | 0 (0)| 00:00:01 | | | |
| 9 | NESTED LOOPS | | 1 | 407 | 0 (0)| 00:00:01 | | | |
| 10 | PARTITION RANGE SINGLE | | 1 | 217 | 0 (0)| 00:00:01 | 1357 | 1357 | |
|* 11 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| CS_SALESTRANSACTION | 1 | 217 | 0 (0)| 00:00:01 | 1357 | 1357 | PRD121 |
|* 12 | INDEX RANGE SCAN | CS_SALESTRANSACTION_PK | 1 | | 0 (0)| 00:00:01 | 1357 | 1357 | PRD121 |
| 13 | PARTITION RANGE SINGLE | | 1 | 190 | 0 (0)| 00:00:01 | 1356 | 1356 | |
| 14 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| CS_TRANSACTIONASSIGNMENT | 1 | 190 | 0 (0)| 00:00:01 | 1356 | 1356 | PRD121 |
|* 15 | INDEX RANGE SCAN | CS_TRANSACTIONASSIGNMENT_PK | 1 | | 0 (0)| 00:00:01 | 1356 | 1356 | PRD121 |
|* 16 | TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED | CS_GASALESTRANSACTION | 1 | 43 | 0 (0)| 00:00:01 | ROWID | ROWID | PRD121 |
|* 17 | INDEX RANGE SCAN | GASALESTRANSACTION_IDX | 3 | | 0 (0)| 00:00:01 | | | PRD121 |
| 18 | PARTITION RANGE SINGLE | | 1 | | 2 (0)| 00:00:01 | 8 | 8 | |
| 19 | PARTITION LIST ALL | | 1 | | 2 (0)| 00:00:01 | 1 | 268 | |
|* 20 | INDEX RANGE SCAN | OD_CREDIT_UTVALUE | 1 | | 2 (0)| 00:00:01 | 1347 | 1614 | PRD121 |
|* 21 | TABLE ACCESS BY LOCAL INDEX ROWID | CS_CREDIT | 1 | 53 | 3 (0)| 00:00:01 | 1 | 1 | PRD121 |
| 22 | TABLE ACCESS FULL | ADTV_FRS_DISPUTES | 27011 | 1213K| 341 (0)| 00:00:01 | | | PRD121 |
|* 23 | TABLE ACCESS BY INDEX ROWID | ADTV_FRS_CONTROL | 1 | 15 | 1 (0)| 00:00:01 | | | PRD121 |
|* 24 | INDEX UNIQUE SCAN | ADTV_FRS_CONTROL_PK | 1 | | 0 (0)| 00:00:01 | | | PRD121 |
| 25 | PARTITION LIST SINGLE | | 1 | 48 | 1 (0)| 00:00:01 | 2 | 2 | |
|* 26 | TABLE ACCESS BY LOCAL INDEX ROWID | CS_EVENTTYPE | 1 | 48 | 1 (0)| 00:00:01 | 2 | 2 | PRD121 |
|* 27 | INDEX UNIQUE SCAN | CS_EVENTTYPE_PK | 1 | | 0 (0)| 00:00:01 | 2 | 2 | PRD121 |
| 28 | PARTITION LIST SINGLE | | 1 | | 0 (0)| 00:00:01 | 2 | 2 | |
|* 29 | INDEX UNIQUE SCAN | CS_CREDITTYPE_PK | 1 | | 0 (0)| 00:00:01 | 2 | 2 | PRD121 |
|* 30 | TABLE ACCESS BY LOCAL INDEX ROWID | CS_CREDITTYPE | 1 | 49 | 1 (0)| 00:00:01 | 2 | 2 | PRD121 |
------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("GENERICATTRIBUTE13"="A1"."ACTIVITY_ID" AND "LINENUMBER"="A1"."ITEM_ID")
11 - filter("SUBLINENUMBER"<>2)
12 - access("TENANTID"='ADTV' AND "PROCESSINGUNITSEQ"=3.82805968326498E16)
15 - access("TENANTID"='ADTV' AND "PROCESSINGUNITSEQ"=3.82805968326498E16 AND "COMPENSATIONDATE"="COMPENSATIONDATE" AND
"SALESTRANSACTIONSEQ"="SALESTRANSACTIONSEQ")
16 - filter("COMPENSATIONDATE"="COMPENSATIONDATE" AND "PAGENUMBER"=4)
17 - access("SALESTRANSACTIONSEQ"="SALESTRANSACTIONSEQ" AND "TENANTID"='ADTV')
20 - access("TENANTID"='ADTV' AND "PROCESSINGUNITSEQ"=3.82805968326498E16)
21 - filter("SALESTRANSACTIONSEQ"="SALESTRANSACTIONSEQ" AND "SALESORDERSEQ"="SALESORDERSEQ")
23 - filter(UPPER("A8"."STATUS")='NEW')
24 - access("A1"."CASE_NO"="A8"."CASE_NO")
26 - filter("EVENTTYPEID"='PROTECTIONPLAN CHARGEBACK' OR "EVENTTYPEID"='PROTECTIONPLAN CHARGEBACK-FRS' OR "EVENTTYPEID"='PROTECTIONPLAN
INCENTIVE' OR "EVENTTYPEID"='PROTECTIONPLAN INCENTIVE-FRS' OR "EVENTTYPEID"='PROTECTIONPLAN KICKER' OR "EVENTTYPEID"='PROTECTIONPLAN KICKER-FRS' OR
"EVENTTYPEID"='UNIVERSAL BILLER' OR "EVENTTYPEID"='UNIVERSAL BILLER-FRS' OR "EVENTTYPEID"='WORK ORDER' OR "EVENTTYPEID"='WORK ORDER-FRS')
27 - access("TENANTID"='ADTV' AND "EVENTTYPESEQ"="DATATYPESEQ" AND "REMOVEDATE"=TO_DATE(' 2200-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
29 - access("TENANTID"='ADTV' AND "CREDITTYPESEQ"="DATATYPESEQ" AND "REMOVEDATE"=TO_DATE(' 2200-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
30 - filter("CREDITTYPEID"="A1"."CREDIT_TYPE" OR "CREDITTYPEID" LIKE "A1"."CREDIT_TYPE"||'%FRS')
Note
-----
- fully remote statement
- dynamic statistics used: dynamic sampling (level=7)
CodePudding user response：
should we use left join keyword for all other tables after ga table or we can use as it is in the query. Is there any performance issues with this query?
LEFT OUTER JOIN, to give it its full name, is a two part thing
OUTER JOIN is a special case where "if the join fails, permit the solid side table/resultset to exist in the output and fill the partial side with NULLs"
The LEFT is a direction to the database as to which side shall be considered "solid". All the rows from the solid side are present at least once.
Absent any parentheses or sub queries driving execution direction:
SELECT *
FROM
a
LEFT OUTER JOIN b ON ...
a is the left; a is thus the solid side. All rows from a will be present. Rows from b may be present or null if the join predicates matched no rows
Once this is done this whole resultset of "a and b, nulls, warts and all" will become "the left side" for subsequent joins
SELECT *
FROM
a
LEFT OUTER JOIN b
some_kind_of JOIN c
Is effectively the same as:
SELECT *
FROM
(
SELECT *
FROM
a
LEFT OUTER JOIN b
) newLeft
some_kind_of JOIN c
Remember, the OUTER specifier permits the join to fail and still keeps the declared solid side rows
Whether you can use INNER or LEFT/RIGHT OUTER to join c in depends on what you're joining it to
If you're joining it to, say, a column from a then it could be fine to use INNER or OUTER - you'd use whatever you'd use if b wasn't even in the picture.
- Will the join from a to c fail sometimes and you still want the rows from a? Use an OUTER.
- Will it never fail, or do you not want any rows that do fail? Use an INNER.
However, if you're joining it to a column that was provided by b then you probably are going to want to use some OUTER join, otherwise there will have been no point making the query do a left outer join b - rows from bb will definitely have a NULL where the join failed but you wanted to keep those ones.. If you then INNER JOIN c to some column from b, that was NULL because the join failed, then the row will disappear from the output. Nothing is ever equal to NULL, so the INNER JOIN to c on the NULL in the column from b. In effect the INNER JOIN undoes all that good work done keeping a's data, by the OUTER join that joined b's data
Doing
a
LEFT JOIN b ON a.b_id = b.id
LEFT JOIN c ON b.c_id = c.id
allows those rows from a-join-b where b.c_id is null (because the join failed) to stay in the output (because it's an outer join to C, not an inner one)..
..
Generally we inner join everything we can, then switch to left joining everything else because it makes the queries easier to follow. In that "if c is being inner joined to a" scenario we would perhaps:
a
INNER JOIN c on a.c_id = c.id
LEFT JOIN b on a.b_id = b.id
Rather than:
a
LEFT JOIN b on a.b_id = b.id
INNER JOIN c on a.c_id = c.id
If a table is being joined to a table that was left joined, left join it too. Avoid RIGHT join because it goes against the evaluation direction of SQL and makes things harder to reason about; any time you think about using a right join, turn it around and rewrite it as a left.
Don't forget to use sub queries too. If you want every a joined to b which is joined to c only if both b and c sides match, it's probably clearest to:
a
LEFT JOIN (
SELECT * FROM b INNER JOIN c ON b.c_id = c.id
) b_and_c
Try to see your SQL as developing some growing-wider-with-every-join resultset that, at every join, becomes the new left side