Skip to Main Content
  • Questions

  • "Merge Join Cartesian" only if I use a column alias

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Roberto.

Asked: July 12, 2019 - 10:12 am UTC

Last updated: July 30, 2019 - 10:13 am UTC

Version: Oracle 11.2 + 12.1

Viewed 1000+ times

You Asked

Hi
it's almost one week that i struggle with a simple query.
The main problem is that query runned on a specific db instance of a server (the db istance that i usually use every day)
produce a merge join cartesian in Explain Plan output, while if i run that query from another db istance \ sever the execution plan show me a different access and in this case the query after 10 minute throw me a output.

This is the query:
--from Oracle 11.2.0.4.0
WITH first_event_year AS (
SELECT  DISTINCT key_paz,
        first_value(RI) over(PARTITION BY key_paz ORDER BY t_erog) AS fri
FROM    (
                SELECT key_ass, t_erog, ROWID AS ri
                FROM SC0029  /*synonim for a table out of the db istance the query is running (Oracle 12.1.0.2.0)*/
                WHERE anno = '2017'
                AND ( c_prest = '87.37.1' OR c_catamb  = 'C00270100' ) 
                AND C_REGAPP = '070'
                AND TIP_PRES = 'S'
        ) s,
        CORRELAZIONE K  /*synonim for a table out of the db istance the query is running (same db istance of SC0029)*/
WHERE S.key_ass = K.key_ass
)
--main query
SELECT  asl_dedotta,
        COUNT(*)
FROM    (
            SELECT  s2.c_comres  /*I get in this case only a field, but i could retrieve more fieds*/
            FROM    SC0029 s2, /*synonim for a table out of the db istance the query is running*/
                    first_event_year P
            WHERE s2.ROWID = p.fri
        ) Y,
        (
            SELECT codice, asl_dedotta
            FROM comuni     -- local table
            WHERE codreg = '070'
        ) C
WHERE Y.c_comres = C.codice
GROUP BY C.asl_dedotta
ORDER BY C.asl_dedotta

Considering only the temporary table (first_event_year), if I run the explain plan tool I can see nothing strange and the output is generated in about 10 minutes.
I'm talking about 60,000 records from a table (SC0029) populated by about 200 milion of records (20M X year from 2010)
But if i run the explain plan tool of the whole statements i see that

Plan
10 SELECT STATEMENT  ALL_ROWS
Cost: 587.360; Bytes: 21.310; Cardinality: 5; CPU Cost: 0; IO Cost: 0; Time: 00:00:00; 
Partition #: 0;       
 9 SORT ORDER BY  
 Projection: (#keys=1) NLSSORT("ASL_DEDOTTA",'nls_sort=''WEST_EUROPEAN''')[34], "ASL_DEDOTTA"[VARCHAR2,3], COUNT(*)[22]; 
 Cost: 587.360; Bytes: 21.310; Cardinality: 5; CPU Cost: 0; IO Cost: 0; Time: 00:00:00; 
 Partition #: 0;      
  8 HASH GROUP BY  
  Projection: (#keys=1) "ASL_DEDOTTA"[VARCHAR2,3], COUNT(*)[22]; 
  Cost: 587.360; Bytes: 21.310; Cardinality: 5; CPU Cost: 0; IO Cost: 0; Time: 00:00:00; 
  Partition #: 0;     
   7 NESTED LOOPS  
   Projection: (#keys=0) "ASL_DEDOTTA"[VARCHAR2,3]; 
   Cost: 672.152; Bytes: 1.467.641.548; Cardinality: 11.429.442; CPU Cost: 0; IO Cost: 0; Time: 00:00:00; 
   Partition #: 0;    
    5 <b>MERGE JOIN CARTESIAN</b>  
    Projection: (#keys=0) "P"."FRI"[ROWID,4000], "CODICE"[VARCHAR2,6], "ASL_DEDOTTA"[VARCHAR2,3]; 
    Cost: 634; Bytes: 35.058.980; Cardinality: 8.732; CPU Cost: 0; IO Cost: 0; Time: 00:00:00; 
    Partition #: 0;   
     2 VIEW ARS. 
     Projection: "P"."FRI"[ROWID,4000]; 
     Cost: 86; Bytes: 148.074; Cardinality: 37; CPU Cost: 0; IO Cost: 0; Time: 00:00:00; 
     Partition #: 0;  
      1 REMOTE SERIAL_FROM_REMOTE  DBFNEW.REGLIG
      EXPLAIN PLAN SET STATEMENT_ID='E0D58D52' INTO "ARS"."SQLN_EXPLAIN_PLAN"@! FOR SELECT DISTINCT "A1"."KEY_PAZ",FIRST_VALUE("A2".ROWID) OVER ( PARTITION BY "A1"."KEY_PAZ" ORDER BY "A2"."T_EROG" RANGE  BETWEEN  UNBOUNDED  PRECEDING  AND  CURRENT ROW ) FROM "SISG"."SC0029" "A2","SISG"."CORRELAZIONE" "A1" WHERE "A2"."KEY_ASS"="A1"."KEY_ASS" AND "A2"."ANNO"='2017' AND ("A2"."C_PREST"='87.37.1' OR "A2"."C_CATAMB"='C00270100') AND "A2"."C_REGAPP"='070' AND "A2"."TIP_PRES"='S'
      Projection: "KEY_PAZ"[NUMBER,22], FIRST_VALUE(ROWID) OVER ( PARTITION BY "KEY_PAZ" ORDER BY "T_EROG" RANGE  BETWEEN  UNBOUNDED  PRECEDING  AND  CURRENT ROW )[4000]; 
      Cost: 0; Bytes: 0; Cardinality: 0; CPU Cost: 0; IO Cost: 0; 
      Partition #: 0; 
     4 BUFFER SORT  
     Projection: (#keys=0) "CODICE"[VARCHAR2,6], "ASL_DEDOTTA"[VARCHAR2,3]; 
     Cost: 634; Bytes: 3.068; Cardinality: 236; CPU Cost: 0; IO Cost: 0; Time: 00:00:00; 
     Partition #: 0;  
      3 TABLE ACCESS FULL ARS.COMUNI 
      Filter: "CODREG"='070'; 
      Projection: "CODICE"[VARCHAR2,6], "ASL_DEDOTTA"[VARCHAR2,3]; 
      Cost: 15; Bytes: 3.068; Cardinality: 236; CPU Cost: 0; IO Cost: 0; Time: 00:00:00; 
      Partition #: 0; 
    6 REMOTE SERIAL_FROM_REMOTE SC0029 DBFNEW.REGLIG
    SELECT ROWID,"C_COMRES" FROM "SISG"."SC0029" "S2" WHERE "C_COMRES"=:1 AND ROWID=:2
    Projection: "S2".ROWID[ROWID,4000], "S2"."C_COMRES"[VARCHAR2,6]; 
    Cost: 67; Bytes: 323.323; Cardinality: 1.309; CPU Cost: 0; IO Cost: 0; Time: 00:00:00; 
    Partition #: 0;


With that "merge join cartesian" the query does not throw out any result in the useful time of a weekend!

Then I'have also tried to reverse the query from the other server view.
To do that I had to grant the select privilege to COMUNI table.
This is the "reversed" query:
WITH first_event_year AS (
SELECT  DISTINCT key_paz,
        first_value(RI) over(PARTITION BY key_paz ORDER BY t_erog) AS fri
FROM    (
                SELECT key_ass, t_erog, ROWID AS ri
                FROM SC0029  --LOCAL TABLE NOW
                WHERE anno = '2017'
                AND ( c_prest = '87.37.1' OR c_catamb  = 'C00270100' ) 
                AND C_REGAPP = '070'
                AND TIP_PRES = 'S'
        ) s,
        CORRELAZIONE K  --LOCAL TABLE NOW
WHERE S.key_ass = K.key_ass
)

SELECT  asl_dedotta,
        COUNT(*)
FROM    (
            SELECT  s2.c_comres
            FROM    SC0029 s2,
                    first_event_year P
            WHERE s2.ROWID = p.fri
        ) Y,
        (
            SELECT codice, asl_dedotta
            FROM ARS.comuni@MY_DBLINK     /*table throught dblink*/
            WHERE codreg = '070'
        ) C
WHERE Y.c_comres = C.codice
GROUP BY C.asl_dedotta
ORDER BY C.asl_dedotta.


Written in that way the query don't generate any merge join cartesian and ends in about 10 minutes.

But the VERY STRANGE thing (for me) that I discover after one week, is that if I take back the first query and I change the query row...
from:
first_value(RI) over(PARTITION BY key_paz ORDER BY t_erog) AS fri


to:
first_value(S.ROWID) over(PARTITION BY key_paz ORDER BY t_erog) AS fri


the same query doesn't generate any merge join cartesian end in about 10 minutes with this execution plan:
Plan
9 SELECT STATEMENT  ALL_ROWS
Cost: 16.204; Bytes: 1.360; Cardinality: 5; CPU Cost: 0; IO Cost: 0; Time: 00:00:00; 
Partition #: 0;      
 8 SORT ORDER BY  
 Projection: (#keys=1) NLSSORT("ASL_DEDOTTA",'nls_sort=''WEST_EUROPEAN''')[34], "ASL_DEDOTTA"[VARCHAR2,3], COUNT(*)[22]; 
 Cost: 16.204; Bytes: 1.360; Cardinality: 5; CPU Cost: 0; IO Cost: 0; Time: 00:00:00; 
 Partition #: 0;     
  7 HASH GROUP BY  
  Projection: (#keys=1) "ASL_DEDOTTA"[VARCHAR2,3], COUNT(*)[22]; 
  Cost: 16.204; Bytes: 1.360; Cardinality: 5; CPU Cost: 0; IO Cost: 0; Time: 00:00:00; 
  Partition #: 0;    
   6 HASH JOIN  
   Access: "P"."FRI"=("S2".ROWID); 
   Projection: (#keys=1) "ASL_DEDOTTA"[VARCHAR2,3]; 
   Cost: 15.957; Bytes: 621.761.536; Cardinality: 2.285.888; CPU Cost: 0; IO Cost: 0; Time: 00:00:00; 
   Partition #: 0;   
    2 VIEW ARS. 
    Projection: "P"."FRI"[ROWID,10]; 
    Cost: 86; Bytes: 444; Cardinality: 37; CPU Cost: 0; IO Cost: 0; Time: 00:00:00; 
    Partition #: 0;  
     1 REMOTE SERIAL_FROM_REMOTE  DBFNEW.REGLIG
     EXPLAIN PLAN SET STATEMENT_ID='E0D593CE' INTO "ARS"."SQLN_EXPLAIN_PLAN"@! FOR SELECT DISTINCT "A1"."KEY_PAZ",FIRST_VALUE("A2".ROWID) OVER ( PARTITION BY "A1"."KEY_PAZ" ORDER BY "A2"."T_EROG" RANGE  BETWEEN  UNBOUNDED  PRECEDING  AND  CURRENT ROW ) FROM "SISG"."SC0029" "A2","SISG"."CORRELAZIONE" "A1" WHERE "A2"."KEY_ASS"="A1"."KEY_ASS" AND "A2"."ANNO"='2017' AND ("A2"."C_PREST"='87.37.1' OR "A2"."C_CATAMB"='C00270100') AND "A2"."C_REGAPP"='070' AND "A2"."TIP_PRES"='S'
     Projection: "KEY_PAZ"[NUMBER,22], FIRST_VALUE("S".ROWID) OVER ( PARTITION BY "KEY_PAZ" ORDER BY "T_EROG" RANGE  BETWEEN  UNBOUNDED  PRECEDING  AND  CURRENT ROW )[10]; 
     Cost: 0; Bytes: 0; Cardinality: 0; CPU Cost: 0; IO Cost: 0; 
     Partition #: 0; 
    5 NESTED LOOPS  
    Projection: (#keys=0) "ASL_DEDOTTA"[VARCHAR2,3], "S2".ROWID[ROWID,4000]; 
    Cost: 62.406; Bytes: 1.606.300.020; Cardinality: 6.178.077; CPU Cost: 0; IO Cost: 0; Time: 00:00:00; 
    Partition #: 0;  
     3 TABLE ACCESS FULL ARS.COMUNI 
     Filter: "CODREG"='070'; 
     Projection: "CODICE"[VARCHAR2,6], "ASL_DEDOTTA"[VARCHAR2,3]; 
     Cost: 16; Bytes: 3.068; Cardinality: 236; CPU Cost: 0; IO Cost: 0; Time: 00:00:00; 
     Partition #: 0; 
     4 REMOTE SERIAL_FROM_REMOTE SC0029 DBFNEW.REGLIG
     SELECT ROWID,"C_COMRES" FROM "SISG"."SC0029" "S2" WHERE "C_COMRES"=:1
     Projection: "S2".ROWID[ROWID,4000], "S2"."C_COMRES"[VARCHAR2,6]; 
     Cost: 67; Bytes: 6.465.966; Cardinality: 26.178; CPU Cost: 0; IO Cost: 0; Time: 00:00:00; 
     Partition #: 0;


How can it be that an column alias change so heavy the execution plan?

Than you in advance

and Chris said...

In the first query, the database joins first_event_year to comuni.

But there's no join between these! So it has to use a merge join.

Whereas in the second query, it joins comuni to SC0029. Which does have a join predicate. Then links to the first_event_year subquery.

Exactly why this happens I'm not sure!

Going over a database link has many limitations. Without the alias, I suspect the database is no longer able to infer that FRI returns the ROWID from the subquery. Leading to the merge join.

In any case, I see you access SC0029 twice.

Is this really necessary?

If you can change the query so you only access it once, not only is your query likely to be quicker due to reducing the number of table accesses, there's less chance for the optimizer to get the plan wrong!

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

The way I approach the problem (rowid) is generally correct or does it have weaknesses?

Roberto Oneto, July 29, 2019 - 4:28 pm UTC

Thanks for your reply.
To retrieve the first event of the year for every patient (key_paz) I use rowid, because table SC0029 unfortunately does not have a primary key.
I also use rowid because I often need to get several field related to that particular record.
This is also why I access table SC0029 twice.
The first time is to retrieve the pointer to the record (rowid) and the second time is to retrieve all the fields I need.

I understand the dblink introduces some indeterminacy. (it will be my duty to look for documentation to understand this kind of limits)
In the past, however, I have had no such problems.

Before addressing you I've read a couple of articles on the web which led me to think that the problem was in the statistics and optimizer. (objects that I can't control 'cause I'm not a dba).

On the other hand, the dba-team have not been able to give me a good explanation about the phenomenon and have reassured me that the optimizer has no problems.

Apart from this, the approach I have in retrieving the rowid in the absence of primary key is valid, or it has some weaknesses?

Could some optimizer hints be useful for this purpose?

Thank you very much for your help
Chris Saxon
July 30, 2019 - 10:13 am UTC

Ahh, the joys of low-quality data :(

You should be able to avoid the self-join by doing something like:

- Using row_number to find the "first" row for each key_paz
- Filtering the output of this to those where rn = 1

e.g.:

WITH first_event_year AS (
  SELECT  key_paz,
          row_number() over(PARTITION BY key_paz ORDER BY t_erog, s.rowid) AS rn,
          <other cols>
  FROM    (
                  SELECT key_ass, t_erog, ROWID AS ri
                  FROM SC0029  
                  WHERE anno = '2017'
                  AND ( c_prest = '87.37.1' OR c_catamb  = 'C00270100' ) 
                  AND C_REGAPP = '070'
                  AND TIP_PRES = 'S'
          ) s,
          CORRELAZIONE K 
  WHERE S.key_ass = K.key_ass
)
--main query
SELECT  asl_dedotta,
        COUNT(*)
FROM    (
            SELECT  p.cols
            FROM    first_event_year P
            WHERE   p.rn = 1
        ) Y, ....


Or even just checking rowid = first rowid in the outer query. Note the lack of distinct, so you can return all the columns you need in the subquery. Either way you'll avoid one table access, which is a win :)

There are challenges with tuning queries over DB links; I discussed these in an Office Hours session a while back:

https://asktom.oracle.com/pls/apex/f?p=100:551:::NO:RP,551:P551_CLASS_ID:5684&cs=160B78AE8C60F30927818F88F3AF73EEB
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.