This discussion is archived
11 Replies Latest reply: Mar 21, 2013 9:46 AM by 989009 RSS

How to change the table access order?

989009 Newbie
Currently Being Moderated
I am using oracle 10g 10.2.0.3.0

I have a select statement
SELECT /*+ leading v.Guest_Info v.Guest_Domain_Relation Guest_Address*/
*
  FROM (SELECT 
         Cdr.Guest_Domain_Relation_Id,
         Ci.Guest_Id        
          FROM Guest_Info         Ci,
               Guest_Domain_Relation Cdr
         WHERE Ci.Guest_Id = Cdr.Guest_Id) v,
       Guest_Address Ca
 WHERE v.Guest_Id = Ca.Guest_Id;
and Explain Plan shows
1     Plan hash value: 3785964411
2      
3     ---------------------------------------------------------------------------------------------------------
4     | Id  | Operation                     | Name                    | Rows  | Bytes | Cost (%CPU)| Time     |
5     ---------------------------------------------------------------------------------------------------------
6     |   0 | SELECT STATEMENT              |                         |  1806 |   199K|     9  (23)| 00:00:01 |
7     |*  1 |  HASH JOIN                    |                         |  1806 |   199K|     9  (23)| 00:00:01 |
8     |   2 |   MERGE JOIN                  |                         |   482 | 48682 |     6  (17)| 00:00:01 |
9     |   3 |    TABLE ACCESS BY INDEX ROWID| Guest_ADDRESS          |   919 | 87305 |     4   (0)| 00:00:01 |
10     |   4 |     INDEX FULL SCAN           | XAK1Guest_ADDRESS      |   919 |       |     1   (0)| 00:00:01 |
11     |*  5 |    SORT JOIN                  |                         |   482 |  2892 |     2  (50)| 00:00:01 |
12     |   6 |     INDEX FULL SCAN           | XPKGuest_INFO          |   482 |  2892 |     1   (0)| 00:00:01 |
13     |   7 |   VIEW                        | index$_join$_003        |  3493 | 41916 |     3   (0)| 00:00:01 |
14     |*  8 |    HASH JOIN                  |                         |       |       |            |          |
15     |   9 |     INDEX FAST FULL SCAN      | XAK1Guest_DOMAIN_Relation |  3493 | 41916 |     1   (0)| 00:00:01 |
16     |  10 |     INDEX FAST FULL SCAN      | XPKGuest_DOMAIN_Relation  |  3493 | 41916 |     1   (0)| 00:00:01 |
17     ---------------------------------------------------------------------------------------------------------
18      
19     Predicate Information (identified by operation id):
20     ---------------------------------------------------
21      
Is there Anyway I can have Guess_Domain_Relation table join to Guest_info table first than join to Guess_Address?

I have tried couple ways to rewrite the query however it came out no luck

Thanks

Edited by: Y.L on Mar 20, 2013 1:59 PM
  • 1. Re: How to change the table access order?
    TPD-Opitz-Consulting-com Expert
    Currently Being Moderated
    Y.L wrote:
    I am using oracle 10g 10.2.0.3.0

    I have a select statement
    SELECT /*+ leading v.Guest_Info v.Guest_Domain_Relation Guest_Address*/
    Is there Anyway I can have Guess_Domain_Relation table join to Guest_info table first than join to Guess_Address?
    You already defined the order in wich oracle should walk trough the tables. Just change the order of table it this first line the way you want it.

    http://lmgtfy.com/?q=oracle+table+order+hint

    bye
    TPD
  • 2. Re: How to change the table access order?
    989009 Newbie
    Currently Being Moderated
    But I think the explain plan didnt follow the order I wanted it to follow...
    I read the explain plan wrong?

    Explain plan goes from address->info->relation table

    Edited by: Y.L on Mar 20, 2013 3:06 PM
  • 3. Re: How to change the table access order?
    TPD-Opitz-Consulting-com Expert
    Currently Being Moderated
    In my experience cause of performance problems usully is oracles preference for <i>hash joins</i> .

    In such cases I use the <tt>use_nl</tt> hint wich solves my problems by 95%.

    try it.

    bye
    TPD
  • 4. Re: How to change the table access order?
    989009 Newbie
    Currently Being Moderated
    I have tried
    SELECT /*+ use_nl(v.Guest_Info v.Guest_Domain_Relation Guest_Address)*/
    *
      FROM (SELECT 
             Cdr.Guest_Domain_Relation_Id,
             Ci.Guest_Id        
              FROM Guest_Info         Ci,
                   Guest_Domain_Relation Cdr
             WHERE Ci.Guest_Id = Cdr.Guest_Id) v,
           Guest_Address Ca
     WHERE v.Guest_Id = Ca.Guest_Id;
    But explain plan stays the same
  • 5. Re: How to change the table access order?
    rp0428 Guru
    Currently Being Moderated
    >
    I have tried
    SELECT /*+ use_nl(v.Guest_Info v.Guest_Domain_Relation Guest_Address)*/
    *
      FROM (SELECT 
             Cdr.Guest_Domain_Relation_Id,
             Ci.Guest_Id        
              FROM Guest_Info         Ci,
                   Guest_Domain_Relation Cdr
             WHERE Ci.Guest_Id = Cdr.Guest_Id) v,
           Guest_Address Ca
     WHERE v.Guest_Id = Ca.Guest_Id;
    But explain plan stays the same
    >
    First - what PROBLEM are you trying to solve? That is, why do you care what order the tables are joined in?

    Second - why are you using such a convoluted query with a nested query to begin with? You have three simple tables and want two columns, neither of which is in the address table. Both columns are in the 'guest_domain_relation' table so why are you selecting 'guest_id from the 'guest_info' table?

    All that query is doing is selecting 'guest_id' and 'guest_domain_relation_id from the relation table if the guest_id is present in the other two tables.

    Keep it simple. Join the three tables together with NO hints and work from there. Query all of the data from the relation table.

    That gives you the simplest query and the most options. Those equi-joins could become IN clauses or EXISTS clauses if other things need to be tried.

    Post the resulting execution plan and the table and index ddl for all tables.
  • 6. Re: How to change the table access order?
    989009 Newbie
    Currently Being Moderated
    hi rp0428,

    Thanks for your reply...
    The problem I was trying to figure out is how to manipulate the join order from multiple tables...
    Sorry for the confusion here... The original query has more columns to be selected that was why I have to join three tables...
    And the reason I wrote a sub-select was I was trying make guest_domain_relation table join guest_info table first....
    Based on my understanding, the join order of tables could affect performance alot... just wondering does anyone know any better ways to make multiple tables to be joined in the way that developer specified...
  • 7. Re: How to change the table access order?
    rp0428 Guru
    Currently Being Moderated
    >
    The problem I was trying to figure out is how to manipulate the join order from multiple tables...
    >
    Manipulating the join order is not a PROBLEM - that is, at best, a possible SOLUTION to the problem you haven't told us about.

    What is the PROBLEM you are trying to solve?

    Why do you care what the join order is?

    Why do you think Oracle has chosen the wrong join order?

    If this is just an academic question just say so. But if you have a real problem then tell us what it is.
  • 8. Re: How to change the table access order?
    989009 Newbie
    Currently Being Moderated
    And my another problem here is.. I am not sure why my leading hint didnt work...
    The way I create index will affect how it works?
  • 9. Re: How to change the table access order?
    989009 Newbie
    Currently Being Moderated
    My apology...
    I did not have a 'PROBLEM'... just a question... I went through some posts from Richard Foote and Dion Cho about how to effectively use Index...
    Most of them I never thought of before... I am still new to Oracle.. Please do not get irriated if my question did not make any sense to you...


    Thanks
  • 10. Re: How to change the table access order?
    riedelme Expert
    Currently Being Moderated
    Y.L wrote:
    And my another problem here is.. I am not sure why my leading hint didnt work...
    Probably syntax. The leading hint uses parenthesis, like
    /*+
    select /*+ leading(table1, table2) */ *
      from table1, table2
    */
    Also remember that you must use the table alias name in hints for the identifier to be recognized

    You can also consider using the ORDERED hint (if you must use a hint) for more than one table - be careful, it is a LOT easier to make performance worse with this hint which uses the table order in the FROM clause from left to right
    /*+
    select /*+  ordered */ *
      from table1, table2, table3
    . . .
    */
    The way I create index will affect how it works?
    How the index was created will probably not affect how it works. Indexes can be created through constraints and directly with the CREATE INDEX command. How the indexes is created should not be as important as its defined characterstics: index type, columns, and so forth
  • 11. Re: How to change the table access order?
    989009 Newbie
    Currently Being Moderated
    Thanks so much for the help...
    You got it right, after I changed my table name in leading hint to the table alias name.. the hint worked...

    Thanks again for your generous help...

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points