how to control the join order?

From: biti_rainy <biti_rainy_at_itpub.net>
Date: Thu, 4 Nov 2004 19:34:27 +0800
Message-Id: <20041104113448.75B1D72C3FA@turing.freelists.org>

hi, all

i want to control the join order with hints(ordered and use_hash) but now i can not control it,how can i do?

in this case,i hope the table offer is the last one,but here it is the driver table

explain plan for
select /*+ordered use_hash( m mad) use_hash(m al) use_hash(m o) */ o.subject, al.parent_id ,0 as trust_score_but_renew,mad.im_week_active_degree,mad.new_tp_score

                   from member m,  member_active_degree mad , area_list al ,offer o 
                  where m.city=al.id(+)  and m.login_id=mad.login_id(+) and m.login_id = o.member_id
                  and    o.GMT_EXPIRE>trunc(sysdate) + 1 and o.status='published' and o.gmt_modified <= sysdate

and o.gmt_approved > to_date('2002-01-22', 'yyyy-mm-dd') and o.member_id not in ('buyerofnormal','buyerofbig','tfbuyer', 'wpoffer') ;

@?/rdbms/admin/utlxplp;

| Id  | Operation            |  Name                 | Rows  | Bytes |TempSpc| Cost  |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                       |   115K|    32M|       | 81935 |


|*  1 |  HASH JOIN           |                       |   115K|    32M|    20M| 81935 |

|*  2 |   TABLE ACCESS FULL  | OFFER                 |   117K|    19M|       | 36324 |

|*  3 |   HASH JOIN OUTER    |                       |  4374K|   508M|   525M| 36372 |

|*  4 |    HASH JOIN OUTER   |                       |  4374K|   475M|   367M| 27992 |

|*  5 |     TABLE ACCESS FULL| MEMBER                |  4374K|   317M|       | 22141 |

|*  6 |     TABLE ACCESS FULL| MEMBER_ACTIVE_DEGREE  |     1 |    38 |       |     2 |

|   7 |    TABLE ACCESS FULL | AREA_LIST             |  2696 | 21568 |       |     4 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

   1 - access("M"."LOGIN_ID"="O"."MEMBER_ID")
   2 - filter("O"."GMT_EXPIRE">TRUNC(SYSDATE@!)+1 AND "O"."STATUS"='published' AND
              "O"."GMT_MODIFIED"<=SYSDATE@! AND "O"."GMT_APPROVED">TO_DATE('2002-01-22 00:00:00',
              'yyyy-mm-dd hh24:mi:ss') AND "O"."MEMBER_ID"<>'buyerofnormal' AND
              "O"."MEMBER_ID"<>'buyerofbig' AND "O"."MEMBER_ID"<>'tfbuyer' AND
              "O"."MEMBER_ID"<>'wpoffer')
   3 - access("AL"."ID"(+)=TO_NUMBER("M"."CITY"))
   4 - access("M"."LOGIN_ID"="MAD"."LOGIN_ID"(+))
   5 - filter("M"."LOGIN_ID"<>'buyerofnormal' AND "M"."LOGIN_ID"<>'buyerofbig' AND
              "M"."LOGIN_ID"<>'tfbuyer' AND "M"."LOGIN_ID"<>'wpoffer')
   6 - filter("MAD"."LOGIN_ID"(+)<>'buyerofnormal' AND
              "MAD"."LOGIN_ID"(+)<>'buyerofbig' AND "MAD"."LOGIN_ID"(+)<>'tfbuyer' AND
              "MAD"."LOGIN_ID"(+)<>'wpoffer')

Note: cpu costing is off

Best regards
msn: biti_rainy_at_hotmail.com
a dba from alibaba(china)

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Nov 04 2004 - 06:19:05 CST