6 Replies Latest reply: Sep 7, 2010 8:53 AM by John Spencer RSS

    driving table

    694427
      Hi ,
      How can i find which is driving table using plan_table.

      suppose for this query , how can i find which one oracle took as driving table .

      explain plan for select * from dept , emp where emp.deptno = dept.deptno
        • 1. Re: driving table
          NSK2KSN
          Table which is last one from left

          i..,e

          emp, dept

          dept is driving table

          dept, emp

          emp is driving table,
          • 2. Re: driving table
            bluefrog
            read up on the definition of a nested loop, i.e. (inner versus outer table - within the context of a loop), rather than the position of the table (i.e. left or right) in the SELECT statement.

            http://download.oracle.com/docs/cd/E11882_01/server.112/e10821/optimops.htm#PFGRF94639
            • 3. Re: driving table
              694427
              Can't we find using explain
              • 4. Re: driving table
                Toon Koppelaars
                The driving table in a query that joins two (or more) tables, is a concept that only applies if the join is executed using the nested-loop join algorithm.
                If the CBO decides to execute the join using either the hash-join, or sort-merge-join algoritms, then there is no such thing as a driving table.

                In the nested-loop join algorithm the outer-table (the one accessed first) is often referred to as the driving table.
                (show us the execution plan of your query, and we can tell you which one is the driving table, again, if applicable).

                The order in which the table-names are positioned in the FROM clause bears no relevance in this matter.

                Edited by: Toon Koppelaars on Sep 7, 2010 1:35 PM
                • 5. Re: driving table
                  Sven W.
                  user5003725 wrote:
                  Hi ,
                  How can i find which is driving table using plan_table.

                  suppose for this query , how can i find which one oracle took as driving table .

                  explain plan for select * from dept , emp where emp.deptno = dept.deptno
                  There are two different (but related) contexts in which the term "driving table" is used.

                  1) If you build a view over two joined tables. Then you can update some rows in one of the tables (depends on FK relation ship and the join criteria). This table is sometimes called the driving table. The data dictionary view ALL_UPDATABLE_COLUMNS shows which columns can be updated.

                  2) A select on two joined tables can lead to different execution plans. The most typical plans are a
                  * Nested loop => For each row of the "driving" table an scan on the detail table is made. This is fast if you need to read only a small part of the driving table.
                  * Hash Join => Both tables are ready full and the CBO creates a hash index to do the join comparison. This is usually very fast if you need to read the major part of both tables.

                  The issue about the second explaination of the term "driving table" is, that the execution plan of a query depends from many factors. The plan is not stable (apart from RBO).

                  In the OPs case the driving table would usually be EMP. Since the typical FK-Relationship states that emp is the detail(child) table while dept is the master(parent) table. But this defintion comes from the first interpretation of the "driving table" wordings.
                  • 6. Re: driving table
                    John Spencer
                    Toon Koppelaars wrote:
                    The driving table in a query that joins two (or more) tables, is a concept that only applies if the join is executed using the nested-loop join algorithm.
                    If the CBO decides to execute the join using either the hash-join, or sort-merge-join algoritms, then there is no such thing as a driving table.

                    In the nested-loop join algorithm the outer-table (the one accessed first) is often referred to as the driving table.
                    (show us the execution plan of your query, and we can tell you which one is the driving table, again, if applicable).

                    The order in which the table-names are positioned in the FROM clause bears no relevance in this matter.

                    Edited by: Toon Koppelaars on Sep 7, 2010 1:35 PM
                    Toon:

                    According to Jonathan Lewis here http://jonathanlewis.wordpress.com/2010/08/02/joins/

                    "all joins are nested loop joins with different startup costs"

                    John