how to control the join order?
- From: "biti_rainy" <biti_rainy@xxxxxxxxx>
- To: "oracle-l" <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 4 Nov 2004 19:34:27 +0800
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@xxxxxxxxxxx
a dba from alibaba(china)
--
//www.freelists.org/webpage/oracle-l
Other related posts:
