Re: how to control the join order?

From: biti_rainy <biti_rainy_at_itpub.net>
Date: Fri, 5 Nov 2004 00:04:31 +0800
Message-Id: <20041104160441.B977672E23A@turing.freelists.org>

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_at_hotmail.com
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_at_hotmail.com >a dba from alibaba(china) > > >-- >http://www.freelists.org/webpage/oracle-l > >.

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

Received on Thu Nov 04 2004 - 10:00:47 CST