Explain plan - is there a way to find order of join tables

From: <dbaplusplus_at_hotmail.com>
Date: 27 Mar 2006 10:11:46 -0800
Message-ID: <1143483106.751048.230900@t31g2000cwb.googlegroups.com>

Orace 9i, HP UNX 11i

I have gotten an explian plan using v$sql_plan using dbms_xplan.display table function (I also tried set autotrace on woith same results..).

Plan shows me various nested loops (for vaious joins) but is there any way for me to find the order of join tables, i.e., which table was starting driving table (e.g., tablea), which table it was joined with (e.g, tableb), which table was tableb joined with (table c) etc. Without this information it is quite hard

select all os_packet_route_docs_only.r_object_id, os_packet_rout e_docs_only.r_modify_date from os_packet_route_docs_only_sp os_p acket_route_docs_only where (os_packet_route_docs_only.subject=' true' and os_packet_route_docs_only.r_object_id not in (select a ll p.r_object_id from os_packet_route_docs_only_sp p, dmr_conten t_sp a, dm_format_sp b where (a.r_object_id in (select r_object_ id from dmr_content_r where parent_id=p.r_object_id) and b.name= 'pdf' and a.format=b.r_object_id) and (p.i_has_folder = 1 and p. i_is_deleted = 0) )) and (os_packet_route_docs_only.i_has_folder = 1 and os_packet_route_docs_only.i_is_deleted = 0)

| 0 | SELECT STATEMENT | |
| | |
| 1 | NESTED LOOPS ANTI | | 1
| 78 | 541 (0)|
| 2 | NESTED LOOPS | | 1
| 68 | 269 (0)|
| 3 | NESTED LOOPS | | 2660
| 90440 | 3 (0)|
| 4 | INDEX FULL SCAN | D_1F00C73D80003D13 | 2660
| 45220 | 19 (0)|

|*  5 |     INDEX UNIQUE SCAN             | D_1F00C73D80003D05  |     1


|    17 |            |

|*  6 |    TABLE ACCESS BY INDEX ROWID    | DM_SYSOBJECT_S      |     1


|    34 |     2  (50)|

|*  7 |     INDEX UNIQUE SCAN             | D_1F00C73D80000108  |     1


|       |            |

|*  8 |   VIEW                            |                     |     1


|    10 |            |

|   9 |    NESTED LOOPS                   |                     |     1

|   150 |   272   (0)|

|  10 |     NESTED LOOPS                  |                     |     1

|   125 |   271   (0)|

|  11 |      NESTED LOOPS                 |                     |     1

| 91 | 270 (0)|

  12 |       NESTED LOOPS                |                     |     1


|    57 |   269   (0)|

|  13 |        NESTED LOOPS               |                     |  2660

| 90440 |     3   (0)|

|  14 |         INDEX FULL SCAN           | D_1F00C73D80003D13  |  2660

| 45220 |    19   (0)|

|* 15 |         INDEX UNIQUE SCAN         | D_1F00C73D80003D05  |     1


|    17 |            |

|* 16 |        TABLE ACCESS BY INDEX ROWID| DM_SYSOBJECT_S      |     1


|    23 |     2  (50)|

|* 17 |         INDEX UNIQUE SCAN         | D_1F00C73D80000108  |     1


|       |            |

|  18 |       TABLE ACCESS BY INDEX ROWID | DMR_CONTENT_R       |     2

|    68 |     2  (50)|

|* 19 |        INDEX RANGE SCAN           | D_1F00C73D80000005  |     2


|       |     1   (0)|

|  20 |      TABLE ACCESS BY INDEX ROWID  | DMR_CONTENT_S       |     1

|    34 |     2  (50)|

|* 21 |       INDEX UNIQUE SCAN           | D_1F00C73D80000159  |     1


|       |            |

|* 22 |     TABLE ACCESS BY INDEX ROWID   | DM_FORMAT_S         |     1


|    25 |     2  (50)|

|* 23 |      INDEX UNIQUE SCAN            | D_1F00C73D80000167  |     1


|       |            |

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

Predicate Information (identified by operation id):

5 - access("R_OBJECT_ID"="R_OBJECT_ID")
6 - filter("SUBJECT"='true' AND "I_HAS_FOLDER"=1 AND "I_IS_DELETED"=0)
7 - access("R_OBJECT_ID"="R_OBJECT_ID")

filter("R_OBJECT_ID"="R_OBJECT_ID")
8 - filter("R_OBJECT_ID"="$nso_col_1")

15 - access("R_OBJECT_ID"="R_OBJECT_ID")
16 - filter("I_HAS_FOLDER"=1 AND "I_IS_DELETED"=0)
17 - access("R_OBJECT_ID"="R_OBJECT_ID")

filter("R_OBJECT_ID"="R_OBJECT_ID")

19 - access("PARENT_ID"="R_OBJECT_ID")
21 - access("R_OBJECT_ID"="R_OBJECT_ID")
22 - filter("NAME"='pdf')
23 - access("FORMAT"="R_OBJECT_ID")
--------------------------------

Thanks. Received on Mon Mar 27 2006 - 12:11:46 CST