Re: how to control the join order?
- From: "biti_rainy" <biti_rainy@xxxxxxxxx>
- To: "oracle-l" <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 5 Nov 2004 00:04:31 +0800
hi,all
got it:
explain plan for
select /*+ordered use_hash(t o) no_merge(t)*/ o.subject
from
(select /*+ordered use_hash( m mad) use_hash(m al) */
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(+) ) t,
offer o
where 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;
SQL> SQL>
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc|
Cost |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 115K| 7873K| |
4440K|
|* 1 | HASH JOIN | | 115K| 7873K| 100M|
4440K|
| 2 | VIEW | | 4374K| 50M| |
4402K|
| 3 | NESTED LOOPS OUTER | | 4374K| 383M| |
4402K|
|* 4 | HASH JOIN OUTER | | 4374K| 367M| 367M|
27992 |
|* 5 | TABLE ACCESS FULL| MEMBER | 4374K| 317M| |
22141 |
|* 6 | TABLE ACCESS FULL| MEMBER_ACTIVE_DEGREE | 1 | 12 | |
2 |
|* 7 | INDEX RANGE SCAN | AREA_LIST_ID_F | 1 | 4 | |
1 |
|* 8 | TABLE ACCESS FULL | OFFER | 117K| 6627K| |
36324 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T"."LOGIN_ID"="O"."MEMBER_ID")
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' AND
"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')
7 - access("M"."CITY"=TO_CHAR("AREA_LIST"."ID"))
8 - 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')
Best regards
msn: biti_rainy@xxxxxxxxxxx
a dba from alibaba(china)
---- from the mail-----
>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
>
>.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
