11 Replies Latest reply: Jul 19, 2013 2:31 AM by Pacmann RSS

    Explain Plan

    Suri

      Hi Experts,

      Version : 11g R2

       

      When I see explain plan for the below statement, optimizer is not at all using departments  table while retreiving the ouput. How come it is possible ?

       

      select *
      from employees e
      where e.department_id in ( select department_id from departments d where d.department_id = 10 );

      Execution Plan
      ----------------------------------------------------------
      Plan hash value: 2056577954

      -------------------------------------------------------------------------------------------------
      | Id  | Operation                   | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
      -------------------------------------------------------------------------------------------------
      |   0 | SELECT STATEMENT            |                   |     1 |    69 |     2   (0)| 00:00:01 |
      |   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES         |     1 |    69 |     2   (0)| 00:00:01 |
      |*  2 |   INDEX RANGE SCAN          | EMP_DEPARTMENT_IX |     1 |       |     1   (0)| 00:00:01 |
      -------------------------------------------------------------------------------------------------

      Predicate Information (identified by operation id):
      ---------------------------------------------------

         2 - access("E"."DEPARTMENT_ID"=10)

        • 1. Re: Explain Plan
          SomeoneElse

          Everything it needs is in the index.  No need to access the table.

          • 2. Re: Explain Plan
            Suri

            Hi,

             

            But the index is related to EMPLOYEES table right. How departments table value will available in that index. Sorry I'm not good in tuning concepts.

             

            Suri

             


            • 3. Re: Explain Plan
              SomeoneElse

              You reference only 1 column from departments, the department_id.  My guess is that's the primary key on that table.

               

              So if it can get department_id from the index, why hit the table?

              • 4. Re: Explain Plan
                Etbin

                The optimizer chose the emp_department_ix index knowing department_id is not null and the index used is smaller than departments table where no other columns are needed from.

                Your inline query is of the type if-you-guess-how-many-eggs-I-have-in-the-basket-I-will-give-you-all-ten-eggs-I-have-in-the-basket.


                Regards


                Etbin


                maybe 12c can rewrite it to


                select *

                  from employees

                where department_id = 10

                 

                Message was edited by: Etbin possible optimization added

                • 5. Re: Explain Plan
                  John Spencer

                  Etbin wrote:

                   

                  The optimizer chose the emp_department_ix index knowing department_id is not null and the index used is smaller than departments table where no other columns are needed from.

                  Your inline query is of the type if-you-guess-how-many-eggs-I-have-in-the-basket-I-will-give-you-all-ten-eggs-I-have-in-the-basket.


                  Regards


                  Etbin


                  maybe 12c can rewrite it to


                  select *

                    from employees

                  where department_id = 10

                   

                  Message was edited by: Etbin possible optimization added

                  I'm sure that 12c can rewrite it like that because the OP's 11gR2 did exactly that.  Look at the predicate om the index range scan, 2 - access("E"."DEPARTMENT_ID"=10).  The index is on the employees table, not on the departments table.  Since department_id is declared not null and there is an FK relationship defined between e,ployees and departments the optimizer eliminated the departments table altogether.  I don't have the hr schema on a 10g database to test ti , but I'm pretty sure that it would do the same thing.

                   

                  John

                  • 6. Re: Explain Plan
                    Etbin

                    You're right!

                    Seems the tautology alert fired before I could read the plan entirely.

                    Thinking about https://forums.oracle.com/message/11013117#11013117 I just added the supposition (had some other things to do)

                     

                    Regards

                     

                    Etbin

                    • 7. Re: Explain Plan
                      Suri

                      Hi John,

                       

                      Thanks for understanding my question. I'm having the same question.

                       

                      Hi Gurus,

                       

                        Optimizer is not at all using Departments table to get the desired output in the explain plan which I have posted.  Can some one explain me how is it possible. I dont think index will have all the departments(department_id) value will store in  EMP_DEPARTMENT_IX. ( this index is on employees table)

                       


                      Thanks,

                      Suri

                      • 8. Re: Explain Plan
                        Raunaq

                        I have similar doubts regarding index.....

                         

                        What tables are created internally when we create an index on a column... ?

                         

                        Can anybody explain?

                        • 9. Re: Explain Plan
                          Etbin

                          I think I understand your doubt: departments without employees might exist.

                          But you request just rows from employees table - but no rows can be returned in this particular case (department without employees)

                          So an index on employees table containing department_id is sufficient to determine the rows to be returned as the optimizer knows that all employees rows are indexed (most probably there is a primary key) and seems to be capable of resolving tautologies.

                          The answer to your ( select department_id from departments d where d.department_id = 10 ) i.e. the value of department_id is known to be 10 if there is one/if it exists and evidence was provided, it was resolved at compile time.


                          Regards


                          Etbin

                          • 10. Re: Explain Plan
                            Pacmann

                            Hi,

                             

                            It is table elimination, and these  optimizer improvements have been introduce with 10gR2.

                             

                            Read this :

                            Inside the Oracle Optimizer - Removing the black magic: Why are some of the tables in my query missing from the plan?

                             

                            "Starting in 11gR1, the optimizer will also eliminate tables that are semi-joined or anti-joined. "

                            • 11. Re: Explain Plan
                              Manik

                              JUST read this beautiful article published by TOM... You will get your answer for sure..  (read it till the end.. )

                               

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

                               

                              It is interesting to note how important constraints are for query optimization. Many people think of constraints as a data integrity thing, and it’s true—they are. But constraints are used by the optimizer as well when determining the optimal execution plan. The optimizer takes as inputs

                               

                              • The query to optimize
                              • All available database object statistics
                              • System statistics, if available (CPU speed, single-block I/O speed, and so on—metrics about the physical hardware)
                              • Initialization parameters
                              • Constraints

                               

                              http://www.oracle.com/technetwork/issue-archive/2009/09-may/o39asktom-096149.html

                               

                              Cheers,

                              Manik.