10 Replies Latest reply: Oct 9, 2013 6:51 AM by Raunaq RSS

    Explain plan

    Raunaq

      I have two hash plans ,

       

      FIRST ONE

       

      |   0 | SELECT STATEMENT 

      |                 |  8893K|  2434M|   |   299K  (1)| 00:00:11 |   |   |
      |*  1 |  HASH JOIN RIGHT SEMI |                 |  8893K|  2434M|   740M|   299K  (1)| 00:00:11 |   |   |
      |   2 |   INDEX FAST FULL SCAN| UK_FILTER_ITEM_LANG |29M|   398M|   | 31477   (1)| 00:00:02 |   |   |
      |   3 |   PARTITION RANGE ALL |                 |  9014K|  2346M|   |   109K  (1)| 00:00:04 | 1 |1048575|
      |   4 |TABLE ACCESS FULL  | FILTER_ITEM_LANG_M  |  9014K|  2346M|   |   109K  (1)| 00:00:04 | 1 |1048575|

       

      SECOND ONE

       

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

      | Id  | Operation             | Name                 | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |

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

      |   0 | SELECT STATEMENT      |                      |  8894K|  2434M|       |   251K  (1)| 00:00:10 |       |       |

      |*  1 |  HASH JOIN RIGHT SEMI |                      |  8894K|  2434M|   223M|   251K  (1)| 00:00:10 |       |       |

      |   2 |   INDEX FAST FULL SCAN| MUK_FILTER_ITEM_LANG |  9014K|   120M|       |  9248   (1)| 00:00:01 |       |       |

      |   3 |   PARTITION RANGE ALL |                      |  9014K|  2346M|       |   109K  (1)| 00:00:04 |     1 |1048575|

      |   4 |    TABLE ACCESS FULL  | FILTER_ITEM_LANG_M   |  9014K|  2346M|       |   109K  (1)| 00:00:04 |     1 |1048575|

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

       

      Can any body explain the difference , i am not good in reading explain plans?

       

      Whats basically the difference between them?

       

       

      I am on 11g

        • 1. Re: Explain plan
          Hoek

          Basically there's not really a difference, except the way they're formatted.

          • 2. Re: Explain plan
            Raunaq

            Thanks

             

            And between these two

             

            | Id  | Operation            | Name                | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |

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

            |   0 | SELECT STATEMENT     |                     |     1 |   273 |    25M  (1)| 00:15:14 |       |       |

            |*  1 |  FILTER              |                     |       |       |            |          |       |       |

            |   2 |   PARTITION RANGE ALL|                     |  9014K|  2346M|   109K  (1)| 00:00:04 |     1 |1048575|

            |   3 |    TABLE ACCESS FULL | FILTER_ITEM_LANG_M  |  9014K|  2346M|   109K  (1)| 00:00:04 |     1 |1048575|

            |*  4 |   INDEX RANGE SCAN   | UK_FILTER_ITEM_LANG |     1 |    14 |     3   (0)| 00:00:01 |       |       |

             

             

             

            SECOND ONE

             

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

            | Id  | Operation             | Name                 | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |

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

            |   0 | SELECT STATEMENT      |                      |  8894K|  2434M|       |   251K  (1)| 00:00:10 |       |       |

            |*  1 |  HASH JOIN RIGHT SEMI |                      |  8894K|  2434M|   223M|   251K  (1)| 00:00:10 |       |       |

            |   2 |   INDEX FAST FULL SCAN| MUK_FILTER_ITEM_LANG |  9014K|   120M|       |  9248   (1)| 00:00:01 |       |       |

            |   3 |   PARTITION RANGE ALL |                      |  9014K|  2346M|       |   109K  (1)| 00:00:04 |     1 |1048575|

            |   4 |    TABLE ACCESS FULL  | FILTER_ITEM_LANG_M   |  9014K|  2346M|       |   109K  (1)| 00:00:04 |     1 |1048575|

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

            • 3. Re: Explain plan
              Hoek

              Rows (Cardinality/expected resultset) are completely different.

              Query Optimizer Concepts

              • 4. Re: Explain plan
                Purvesh K

                Consuming different Temp space and Cost too. Right?

                 

                Also, the Cardinality looks different 8893K in first vs 8894K.

                • 5. Re: Explain plan
                  Raunaq

                  Also , one thing is that the first condition is not using a hash join can you explain why?

                   

                  Here is the query

                   

                  FIRST ONE

                   

                  SELECT * FROM t1 s WHERE (col1, col2, NVL(col3,-1)) IN (SELECT col1, col2, NVL(col3,-1) FROM t2 s)

                   

                  SECOND ONE

                   

                  SELECT * FROM t1 o WHERE EXISTS (SELECT 1 FROM t2 i WHERE i.col1 = o.col1 AND i.col2 = o.col2 AND ( i.col3 = o.col3 OR ( i.col3 IS NULL AND o.col3 IS NULL ) ) )

                  • 6. Re: Explain plan
                    Raunaq

                    Well it seems the hash join does not work when there is a non-equi join

                     

                    Optimization of Joins

                    • 7. Re: Explain plan
                      Hoek

                      If you're on 11g, then don't read documentation from the previous century (Release 8.1.5) , but use the correct version:

                      Oracle Database Online Documentation 11g Release 2 (11.2)

                      • 8. Re: Explain plan
                        Raunaq

                        Oops , i got it as a google search result.

                         

                        But anyways i guess i am right?

                        • 9. Re: Explain plan
                          Hoek

                          Not necessarily, since Optimizer (behaviour) in 8.1.5 is different than 11.2.0.3, but in this particular case, things look like they still behave the same in 11.2:

                          The Query Optimizer

                          • 10. Re: Explain plan
                            Raunaq

                            Yes , i meant for this case only.

                             

                            Thanks