1 2 Previous Next 16 Replies Latest reply: Sep 23, 2013 10:40 AM by Phelit RSS

    Cartesian Product in Select

    Phelit

          Hi all!

      I need help.

      I have this function that launches a Select. My Oracle Grid tells me, when I tune the Select of this function, that there is a Cartesian Product. I understand what a Cartesian Product is, but I fail to find it. Can you help me by pointing out WHERE is the misteke? Thank you!!

       

      P.s. Oracle EE 10.2.0.4 on windows Server 2003 R2 SP2 64Bit

       

      [code]

      create or replace

      FUNCTION          "ANA_GETVERBALE"

      (

        v_VerbaleId      IN NUMBER DEFAULT NULL,

        v_Sezione        IN VARCHAR2 DEFAULT NULL,

        v_NumeroVerbale  IN VARCHAR2 DEFAULT NULL,

        v_DataVerbale    IN DATE DEFAULT NULL,

        v_TargaVeicolo   IN VARCHAR2 DEFAULT NULL,

        v_Serie          IN VARCHAR2 DEFAULT NULL,

        v_LoggedUser IN VARCHAR2 DEFAULT NULL

      )

      RETURN SYS_REFCURSOR

      AS

         cv_1 SYS_REFCURSOR;

      BEGIN

        OPEN cv_1 FOR

          SELECT

            ANA_M.VerbaleId,

            ANA_M.Sezione,

            ANA_M.NumeroVerbale,

            ANA_M.DataVerbale,

            ANA_M.TargaVeicolo,

            ANA_M.Serie,

            SCH_C.StatoCartellinoId,

            LOV_StatoCartellino.ListOfValueName StatoCartellino,

            sch_c.cartellinoid

          FROM ANA_Materia_Verbale ANA_M

          INNER JOIN SCH_Cartellini SCH_C ON SCH_C.SoggettoId=ana_m.verbaleid AND SCH_C.TipoSoggettoId = SIS_CONSTANTS_PKG.VB

          INNER JOIN SIC_PROFILO_STATO SIC_PSC ON SIC_PSC.STATOID=SCH_C.STATOCARTELLINOID

          INNER JOIN SIC_PROFILI_USERS SIC_PUC ON SIC_PUC.PROFILOID=SIC_PSC.PROFILOID AND SIC_PUC.PERSONALID=v_LoggedUser

          LEFT JOIN TYP_ListOfValues LOV_StatoCartellino  ON LOV_StatoCartellino.ListOfValueId = SCH_C.StatoCartellinoId

          WHERE ( v_VerbaleId = SIS_CONSTANTS_PKG.AnyBigint OR ANA_M.VerbaleId = v_VerbaleId )

            AND ( v_Sezione = SIS_CONSTANTS_PKG.AnyString OR REGEXP_LIKE(ANA_M.Sezione, '^' || v_Sezione || '$', 'i') )

            AND ( v_serie = SIS_CONSTANTS_PKG.AnyString OR REGEXP_LIKE(ANA_M.Serie, '^' || v_Serie || '$', 'i') )

            AND ( v_NumeroVerbale = SIS_CONSTANTS_PKG.AnyString OR ANA_M.NumeroVerbale = v_NumeroVerbale )

            AND ( v_DataVerbale = SIS_CONSTANTS_PKG.AnyDateTime OR ANA_M.DataVerbale = v_DataVerbale )

            AND ( v_TargaVeicolo = SIS_CONSTANTS_PKG.AnyString OR REGEXP_LIKE(ANA_M.TargaVeicolo,'^' || v_TargaVeicolo || '$', 'i') );

        RETURN cv_1;

      END;

      [/code]

        • 1. Re: Cartesian Product in Select
          BluShadow

          Not sure what's telling you that, but essentially it would mean that you are not joining your tables using all the key columns as required, so a table may have more than one column to uniquely identify a row, and you're only joining on one column, in which case it will duplicate rows in the result.

           

          As we don't have your table structures or knowledge of your primary/foreign keys on those tables, we cannot identify the exact issue.

          • 2. Re: Cartesian Product in Select
            Ramin Hashimzadeh

            I understand what a Cartesian Product is, but I fail to find it.

            Hi Phelit,

            If you and your Oracle Grid (i don't know who is he/she) are understand what is Cartesian Product, please explain for us without searching in google. We are waiting for your explanation.

            • 3. Re: Cartesian Product in Select
              Cherif bh

              Hi ,

               

              As said by BluShadow it is difficult to identify the origin of this issue as we don't know the tables and its constraints structure.

              Could you please try to send us  the execution plan of the below query: this may help

               

                SELECT

                    ANA_M.VerbaleId,

                    ANA_M.Sezione,

                    ANA_M.NumeroVerbale,

                    ANA_M.DataVerbale,

                    ANA_M.TargaVeicolo,

                    ANA_M.Serie,

                    SCH_C.StatoCartellinoId,

                    LOV_StatoCartellino.ListOfValueName StatoCartellino,

                    sch_c.cartellinoid

                  FROM ANA_Materia_Verbale ANA_M

                  INNER JOIN SCH_Cartellini SCH_C ON SCH_C.SoggettoId=ana_m.verbaleid AND SCH_C.TipoSoggettoId = SIS_CONSTANTS_PKG.VB

                  INNER JOIN SIC_PROFILO_STATO SIC_PSC ON SIC_PSC.STATOID=SCH_C.STATOCARTELLINOID

                  INNER JOIN SIC_PROFILI_USERS SIC_PUC ON SIC_PUC.PROFILOID=SIC_PSC.PROFILOID AND SIC_PUC.PERSONALID=v_LoggedUser

                  LEFT JOIN TYP_ListOfValues LOV_StatoCartellino  ON LOV_StatoCartellino.ListOfValueId = SCH_C.StatoCartellinoId

                  WHERE ( v_VerbaleId = SIS_CONSTANTS_PKG.AnyBigint OR ANA_M.VerbaleId = v_VerbaleId )

                    AND ( v_Sezione = SIS_CONSTANTS_PKG.AnyString OR REGEXP_LIKE(ANA_M.Sezione, '^' || v_Sezione || '$', 'i') )

                    AND ( v_serie = SIS_CONSTANTS_PKG.AnyString OR REGEXP_LIKE(ANA_M.Serie, '^' || v_Serie || '$', 'i') )

                    AND ( v_NumeroVerbale = SIS_CONSTANTS_PKG.AnyString OR ANA_M.NumeroVerbale = v_NumeroVerbale )

                    AND ( v_DataVerbale = SIS_CONSTANTS_PKG.AnyDateTime OR ANA_M.DataVerbale = v_DataVerbale )

                    AND ( v_TargaVeicolo = SIS_CONSTANTS_PKG.AnyString OR REGEXP_LIKE(ANA_M.TargaVeicolo,'^' || v_TargaVeicolo || '$', 'i') );

               

               

              Thanks

              Cherif

              • 4. Re: Cartesian Product in Select
                Ramin Hashimzadeh

                i only suspect for :

                     INNER JOIN SIC_PROFILO_STATO SIC_PSC

                        ON SIC_PSC.STATOID = SCH_C.STATOCARTELLINOID

                 

                      LEFT JOIN TYP_ListOfValues LOV_StatoCartellino

                        ON LOV_StatoCartellino.ListOfValueId = SCH_C.StatoCartellinoId

                Just my suspicion.

                 

                ----

                Ramin Hashimzade

                • 5. Re: Cartesian Product in Select
                  jey84

                  Hai

                   

                  FROM ANA_Materia_Verbale ANA_M

                   

                      INNER JOIN SCH_Cartellini SCH_C ON SCH_C.SoggettoId=ana_m.verbaleid AND SCH_C.TipoSoggettoId = SIS_CONSTANTS_PKG.VB

                   

                      INNER JOIN SIC_PROFILO_STATO SIC_PSC ON SIC_PSC.STATOID=SCH_C.STATOCARTELLINOID

                   

                      INNER JOIN SIC_PROFILI_USERS SIC_PUC ON SIC_PUC.PROFILOID=SIC_PSC.PROFILOID AND SIC_PUC.PERSONALID=v_LoggedUser

                   

                      LEFT JOIN TYP_ListOfValues LOV_StatoCartellino  ON LOV_StatoCartellino.ListOfValueId = SCH_C.StatoCartellinoId

                   

                  Here You used more than 5 tables. All the 5 tables are not in join. Please check that.

                   

                  ex:

                  A = {1,2}; B = {3,4}
                  A × B = {1,2} × {3,4} = {(1,3), (1,4), (2,3), (2,4)}
                  B × A = {3,4} × {1,2} = {(3,1), (3,2), (4,1), (4,2)}
                  • 6. Re: Cartesian Product in Select
                    user458652

                    Hi

                     

                    Could you please share all the tables key constraints like Primary and Foreign keys and also provide screen shot of explain plan , which you can generate by selecting the query and pressing Ctrl+E in toad, So that we can give the exact solution.

                     

                    Regards

                    Javed

                    • 7. Re: Cartesian Product in Select
                      BluShadow

                      I personally wouldn't post Toad explain plans on the forum, as they are not the standard Oracle format and can be difficult for many people to follow.  Far better to post an explain plan from within SQL*Plus.

                       

                      and p.s. you're also assuming the OP has got Toad in the first place.

                      • 8. Re: Cartesian Product in Select
                        John Spencer

                        Like Blushadow, I'm not sure what is telling you that, but if it is reading an execution plan then it is possible that the optimizer has decided to do a cartesian join as part of the optimization of the query.

                         

                        Under certain circumstances, for example the optimizer decides that a particular step in the plan will generate only a single row, then it might decide that subsequent joins using that row source can be done as cartesian joins.

                         

                        John

                        • 9. Re: Cartesian Product in Select
                          Phelit

                          So,

                          here what you asked. I just considered the Select without the "WHERE" conditions so to make it simpler to read.

                           

                          So the query is:

                           

                          SELECT

                                ANA_M.VerbaleId,

                                ANA_M.Sezione,

                                ANA_M.NumeroVerbale,

                                ANA_M.DataVerbale,

                                ANA_M.TargaVeicolo,

                                ANA_M.Serie,

                                SCH_C.StatoCartellinoId,

                                LOV_StatoCartellino.ListOfValueName StatoCartellino,

                                sch_c.cartellinoid

                              FROM ANA_Materia_Verbale ANA_M

                              INNER JOIN SCH_Cartellini SCH_C ON SCH_C.SoggettoId=ana_m.verbaleid AND SCH_C.TipoSoggettoId = 38530

                              INNER JOIN SIC_PROFILO_STATO SIC_PSC ON SIC_PSC.STATOID=SCH_C.STATOCARTELLINOID

                              INNER JOIN SIC_PROFILI_USERS SIC_PUC ON SIC_PUC.PROFILOID=SIC_PSC.PROFILOID AND SIC_PUC.PERSONALID='admin'

                              LEFT JOIN TYP_ListOfValues LOV_StatoCartellino  ON LOV_StatoCartellino.ListOfValueId = SCH_C.StatoCartellinoId

                           

                          The explain plan is:

                           

                          PLAN_TABLE_OUTPUT                                                              

                          --------------------------------------------------------------------------------

                          Plan hash value: 708419266                                                     

                                                                                                                                                                                         

                          | Id  | Operation                       | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |                          

                                                                                                         

                          |   0 | SELECT STATEMENT                |                      | 88310 |  8020K|   781   (4)| 00:00:10 |        |      |            |                          

                                                                                                         

                          |   1 |  PX COORDINATOR                 |                      |       |       |            |          |        |      |            |                          

                                                                                                         

                          |   2 |   PX SEND QC (RANDOM)           | :TQ10004             | 88310 |  8020K|   781   (4)| 00:00:10 |  Q1,04 | P->S | QC (RAND)  |                          

                                                                                                         

                          |*  3 |    HASH JOIN                    |                      | 88310 |  8020K|   781   (4)| 00:00:10 |  Q1,04 | PCWP |            |                          

                                                                                                         

                          |   4 |     PX RECEIVE                  |                      | 88310 |  4398K|   439   (4)| 00:00:06 |  Q1,04 | PCWP |            |                          

                                                                                                         

                          |   5 |      PX SEND HASH               | :TQ10003             | 88310 |  4398K|   439   (4)| 00:00:06 |  Q1,03 | P->P | HASH       |                          

                                                                                                         

                          |*  6 |       HASH JOIN                 |                      | 88310 |  4398K|  439   (4)| 00:00:06 |  Q1,03 | PCWP |            |                          

                                                                                                         

                          |   7 |        PX RECEIVE               |                      |   394 |  3546 |     2   (0)| 00:00:01 |  Q1,03 | PCWP |            |                          

                                                                                                         

                          |   8 |         PX SEND HASH            | :TQ10002             |   394 |  3546 |     2   (0)| 00:00:01 |  Q1,02 | P->P | HASH       |                          

                                                                                                         

                          |   9 |          PX BLOCK ITERATOR      |                      |   394 |  3546 |     2   (0)| 00:00:01 |  Q1,02 | PCWC |            |                          

                                                                                                         

                          |  10 |           INDEX FAST FULL SCAN  | PK_SIC_PROFILO_STATO |   394 |  3546 |     2   (0)| 00:00:01 |  Q1,02 | PCWP |            |                          

                                                                                                         

                          |  11 |        BUFFER SORT              |                      |       |       |            |          |  Q1,03 | PCWC |            |                          

                                                                                                         

                          |  12 |         PX RECEIVE              |                      |   129K|  5295K|   431   (3)| 00:00:06 |  Q1,03 | PCWP |            |                          

                                                                                                         

                          |  13 |          PX SEND HASH           | :TQ10000             |   129K|  5295K|   431   (3)| 00:00:06 |        | S->P | HASH       |                          

                                                                                                         

                          |* 14 |           HASH JOIN RIGHT OUTER |                      |   129K|  5295K|   431   (3)| 00:00:06 |        |      |            |                          

                                                                                                         

                          |  15 |            TABLE ACCESS FULL    | TYP_LISTOFVALUES     |  1894 | 22728 |     6   (0)| 00:00:01 |        |      |            |                          

                                                                                                         

                          |  16 |            MERGE JOIN CARTESIAN |                      |   129K|  3782K|   423   (2)| 00:00:06 |        |      |            |                          

                                                                                                         

                          |* 17 |             INDEX FAST FULL SCAN| PK_SIC_PROFILI_USERS |     1 |    10 |     2   (0)| 00:00:01 |        |      |            |                          

                                                                                                         

                          |  18 |             BUFFER SORT         |                      |   129K|  2521K|   421   (2)| 00:00:06 |        |      |            |                          

                                                                                                         

                          |* 19 |              TABLE ACCESS FULL  | SCH_CARTELLINI       |   129K|  2521K|   421   (2)| 00:00:06 |        |      |            |                          

                                                                                                         

                          |  20 |     BUFFER SORT                 |                      |       |       |            |          |  Q1,04 | PCWC |            |                          

                                                                                                         

                          |  21 |      PX RECEIVE                 |                      |   216K|  8877K|   335   (2)| 00:00:05 |  Q1,04 | PCWP |            |                          

                                                                                                         

                          |  22 |       PX SEND HASH              | :TQ10001             |   216K|  8877K|   335   (2)| 00:00:05 |        | S->P | HASH       |                          

                                                                                                         

                          |  23 |        TABLE ACCESS FULL        | ANA_MATERIA_VERBALE  |   216K|  8877K|   335   (2)| 00:00:05 |        |      |            |                                                                                                          

                                                                                                         

                           

                           

                           

                           

                           

                          Predicate Information (identified by operation id):                            

                          ---------------------------------------------------                            

                                                                                                         

                             3 - access("SCH_C"."SOGGETTOID"="ANA_M"."VERBALEID")                        

                             6 - access("SIC_PUC"."PROFILOID"="SIC_PSC"."PROFILOID" AND "SIC_PSC"."STATOID"="SCH_C"."STATOCARTELLINOID")                                                 

                            14 - access("LOV_STATOCARTELLINO"."LISTOFVALUEID"(+)="SCH_C"."STATOCARTELLINOID")                                                                            

                            17 - filter("SIC_PUC"."PERSONALID"='admin')                                  

                            19 - filter("SCH_C"."TIPOSOGGETTOID"=38530)         

                           

                          The tables and constraints are as follows:

                           

                          TABLE ANA_MATERIA_VERBALE

                          ( "VERBALEID" NUMBER(19),

                          "SEZIONE" VARCHAR2(50),

                          "NUMEROVERBALE" VARCHAR2(50),

                          "DATAVERBALE" DATE,

                          "TARGAVEICOLO" VARCHAR2(10),

                          "SERIE" VARCHAR2(20),

                          CONSTRAINT "PK_ANA_MATERIA_VERBALE"

                          PRIMARY KEY ("VERBALEID") VALIDATE )

                           

                          TABLE SCH_CARTELLINI

                          ( "CARTELLINOID" NUMBER(19),

                          "SOGGETTOID" NUMBER(19),

                          "TIPOSOGGETTOID" NUMBER(19),

                          "MACERO" NUMBER(1) DEFAULT (0) ,

                          "PROGRESSIVO"NUMBER(10),

                          "PROGRESSIVOANNO" NUMBER(5),

                          "PROTOCOLLO" VARCHAR2(50),

                          "PROTOCOLLOANNO" NUMBER(5),

                          "STATOCARTELLINOID" NUMBER(19) DEFAULT NULL ,

                          "STORICO" NUMBER(1),

                          "DATACONSERVAZIONE" DATE,

                          "CONSERVAZIONEID" NUMBER(19),

                          "NOMECONSERVAZIONE" VARCHAR2(2000),

                          CONSTRAINT "FK_SCH_CARTELLINI_TYP_LISTOFVA"

                          FOREIGN KEY ("TIPOSOGGETTOID")

                          REFERENCES "DOCARK"."TYP_LISTOFVALUES" ("LISTOFVALUEID") VALIDATE ,

                          CONSTRAINT "FK_SCH_CARTELLINI_TYP_LISTOFV2"

                          FOREIGN KEY ("STATOCARTELLINOID")

                          REFERENCES"DOCARK"."TYP_LISTOFVALUES" ("LISTOFVALUEID") VALIDATE ,

                          CONSTRAINT "PK_SCH_CARTELLINI"

                          PRIMARY KEY ("CARTELLINOID") VALIDATE )

                           

                          TABLE TYP_LISTOFVALUES

                          ( "LISTOFVALUEID" NUMBER(19),

                          "DOMAINID" NUMBER(19),

                          "LISTOFVALUEORDER" NUMBER(19) DEFAULT (1) ,

                          "LISTOFVALUENAME" VARCHAR2(100),

                          "LISTOFVALUEVISIBLE" NUMBER(1) DEFAULT (1) ,

                          "LISTOFVALUEENABLED" NUMBER(1) DEFAULT (1) ,

                          "LISTOFVALUEUPDATABLE" NUMBER(1) DEFAULT (1) ,

                          "VALIDFROMDATE" DATE, "VALIDTODATE"DATE,

                          "PARENTLISTOFVALUEID" NUMBER(19),

                          "LISTOFVALUEDESCRIPTION" VARCHAR2(255),

                          "LISTOFVALUEVALUE" VARCHAR2(255),

                          "LISTOFVALUEIDPS2000" VARCHAR2(10),

                          "LISTOFVALUEIDGPP"VARCHAR2(120),

                          CONSTRAINT "FK_TYP_LISTOFVALUES_TYP_DOMAIN"

                          FOREIGN KEY ("DOMAINID")

                          REFERENCES "DOCARK"."TYP_DOMAINS" ("DOMAINID") VALIDATE ,

                          CONSTRAINT"FK_TYP_LISTOFVALUES_TYP_LISTOF"

                          FOREIGN KEY ("PARENTLISTOFVALUEID")

                          REFERENCES "DOCARK"."TYP_LISTOFVALUES" ("LISTOFVALUEID") VALIDATE ,

                          CONSTRAINT "PK_TYP_LISTOFVALUES"

                          PRIMARY KEY ("LISTOFVALUEID") VALIDATE )

                           

                          TABLE SIC_PROFILO_STATO

                          ( "PROFILOID" NUMBER(19),

                          "STATOID" NUMBER(19),

                          "DOMINIOID" NUMBER(19),

                          "ACCESSTYPE" NUMBER(5),

                          "PROFILOSTATOID" NUMBER(19),

                          CONSTRAINT "PK_SIC_PROFILO_STATO"

                          PRIMARY KEY ("PROFILOID", "STATOID", "DOMINIOID") VALIDATE ,

                          CONSTRAINT "UK_SIC_PROFILO_STATO"

                          UNIQUE ("PROFILOSTATOID") VALIDATE )

                           

                          TABLE SIC_PROFILI_USERS

                          ( "PROFILOID" NUMBER(19),

                          "PERSONALID" VARCHAR2(10),

                          CONSTRAINT "FK_SIC_PROFILI_USERS_SIC_PROFI"

                          FOREIGN KEY ("PROFILOID")

                          REFERENCES"DOCARK"."SIC_PROFILI" ("PROFILOID") VALIDATE ,

                          CONSTRAINT "FK_SIC_PROFILI_USERS_SIC_USERS"

                          FOREIGN KEY ("PERSONALID")

                          REFERENCES "DOCARK"."SIC_USERS" ("PERSONALID") VALIDATE ,

                          CONSTRAINT "PK_SIC_PROFILI_USERS"

                          PRIMARY KEY ("PROFILOID", "PERSONALID") VALIDATE )

                           

                           

                           

                          Let me know if you need anything else! And thank you all!

                           

                           

                          • 10. Re: Cartesian Product in Select
                            BluShadow

                            So what was it that was complaining about the cartesian product?

                            Or is it just the fact that you've seen "MERGE JOIN CARTESIAN" in your explain plan?

                             

                            If so, read this...

                             

                            http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:4105951726381

                            • 11. Re: Cartesian Product in Select
                              Phelit


                              ....ouch... well that explains a LOT!! The real problem was the Oracle Grid SQL_TUNING that warned me that the SELECT contained a CARTESIAN PRODUCT:

                               

                              Ristruttura SQLAn expensive cartesian product operation was found at line ID 4 of the execution plan.Consider removing the disconnected table or view from this statement or add a join condition which refers to it.A cartesian product should be avoided whenever possible because it is an expensive operation and might produce a large amount of data.

                               

                              with this EXECUTION PLAN:

                               

                               

                              OperazioneID rigaOggettoObject typeOrdineRigheDimensione (KB)CostoTempo (sec)Costo CPUCosto I/O

                              https://dbced7.dcs.local:1159/em/cabo/images/cache/chsd.gifSELECT STATEMENT

                              01310,0914606116.852.560452

                              https://dbced7.dcs.local:1159/em/cabo/images/cache/chsd.gifNESTED LOOPS OUTER

                              11210,0914606116.852.560452

                              https://dbced7.dcs.local:1159/em/cabo/images/cache/chsd.gifNESTED LOOPS

                              2910,0794596116.843.328451

                              https://dbced7.dcs.local:1159/em/cabo/images/cache/chsd.gifNESTED LOOPS

                              3720,1414576116.826.984449

                              https://dbced7.dcs.local:1159/em/cabo/images/cache/chsd.gifMERGE JOIN CARTESIAN

                              4420,1024516116.781.136443

                              INDEX FAST FULL SCAN

                              5DOCARK.PK_SIC_PROFILI_USERSINDEX (UNIQUE)110,0102181.9032

                              https://dbced7.dcs.local:1159/em/cabo/images/cache/chsd.gifBUFFER SORT

                              6320,0824496116.699.232441

                              TABLE ACCESS FULL

                              7DOCARK.ANA_MATERIA_VERBALETABLE220,0824496116.699.232441

                              https://dbced7.dcs.local:1159/em/cabo/images/cache/chsd.gifTABLE ACCESS BY INDEX ROWID

                              8DOCARK.SCH_CARTELLINITABLE610,0203122.9243

                              INDEX RANGE SCAN

                              9DOCARK.IDX_SCH_CARTELLINI_SOG_TIPOINDEX512115.4932

                              INDEX RANGE SCAN

                              10DOCARK.PK_SIC_PROFILO_STATOINDEX (UNIQUE)810,009118.1711

                              https://dbced7.dcs.local:1159/em/cabo/images/cache/chsd.gifTABLE ACCESS BY INDEX ROWID

                              11DOCARK.TYP_LISTOFVALUESTABLE1110,012119.2311

                              INDEX UNIQUE SCAN

                              12DOCARK.PK_TYP_LISTOFVALUESINDEX (UNIQUE)101011.9000

                               

                               

                              So I started trying to REMOVE the Cartesian Product. But first I had to find it! So apparently I shouldn't worry too much...

                               

                              The real problem is that I have to work with funtions and DBs created by others, so I am not sure what to expect from most of the Functions. Apparently this execution is correct no matter what that the Tunig Advisor says!

                               

                              Thanks!

                              • 12. Re: Cartesian Product in Select
                                BluShadow

                                Indeed, as one side of that MERGE JOIN CARTESIAN is just a single row, it's hardly going to be expensive, and there isn't really a cartesian product generated from it.

                                That doesn't mean that it's always ok, but in your case, as in the case shown on Tom's blog, it's not an expensive join.

                                • 13. Re: Cartesian Product in Select
                                  Cherif bh

                                  Hi ,

                                   

                                  Could you please the hint ordered

                                   

                                  SELECT /*+ ordered */

                                        ANA_M.VerbaleId,

                                        ANA_M.Sezione,

                                        ANA_M.NumeroVerbale,

                                        ANA_M.DataVerbale,

                                        ANA_M.TargaVeicolo,

                                        ANA_M.Serie,

                                        SCH_C.StatoCartellinoId,

                                        LOV_StatoCartellino.ListOfValueName StatoCartellino,

                                        sch_c.cartellinoid

                                      FROM ANA_Materia_Verbale ANA_M

                                      INNER JOIN SCH_Cartellini SCH_C ON SCH_C.SoggettoId=ana_m.verbaleid AND SCH_C.TipoSoggettoId = 38530

                                      INNER JOIN SIC_PROFILO_STATO SIC_PSC ON SIC_PSC.STATOID=SCH_C.STATOCARTELLINOID

                                      INNER JOIN SIC_PROFILI_USERS SIC_PUC ON SIC_PUC.PROFILOID=SIC_PSC.PROFILOID AND SIC_PUC.PERSONALID='admin'

                                      LEFT JOIN TYP_ListOfValues LOV_StatoCartellino  ON LOV_StatoCartellino.ListOfValueId = SCH_C.StatoCartellinoId;



                                  Or try this


                                  SELECT

                                        ANA_M.VerbaleId,

                                        ANA_M.Sezione,

                                        ANA_M.NumeroVerbale,

                                        ANA_M.DataVerbale,

                                        ANA_M.TargaVeicolo,

                                        ANA_M.Serie,

                                        SCH_C.StatoCartellinoId,

                                        LOV_StatoCartellino.ListOfValueName StatoCartellino,

                                        sch_c.cartellinoid

                                      FROM ANA_Materia_Verbale ANA_M

                                      INNER JOIN SCH_Cartellini SCH_C ON SCH_C.SoggettoId=ana_m.verbaleid AND SCH_C.TipoSoggettoId = 38530

                                     INNER JOIN

                                     ( SELECT SIC_PSC.STATOID FROM  SIC_PROFILO_STATO SIC_PSC1

                                      INNER JOIN SIC_PROFILI_USERS SIC_PUC ON SIC_PUC.PROFILOID=SIC_PSC1.PROFILOID AND SIC_PUC.PERSONALID='admin'

                                      )   SIC_PSC ON SIC_PSC.STATOID=SCH_C.STATOCARTELLINOID

                                      LEFT JOIN TYP_ListOfValues LOV_StatoCartellino  ON LOV_StatoCartellino.ListOfValueId = SCH_C.StatoCartellinoId









                                  • 14. Re: Cartesian Product in Select
                                    Phelit


                                    It actually takes away the Merge Join Cartesian... but the select takes nearly double the time!

                                    1 2 Previous Next