Re: Merge join cartesian

Hello !

You don't refere the table bl_trans in the from clause. And you don't have any join clause with the table bl_trans. You also use an alias "bt.h_order_number" wich looks to me=20 as not beeing alias for any table.

Hope it helps

Best regards
Dias Costa

At 02:45 PM 8/1/00 -0800, you wrote:
>Hello all,
>I have a particular query down below which is doing a Merge join Cartesian=
=20
>on one of the tables below
>Can somebody please let me know how to solve this issue
>The Table Bl_trans has 10 million rows and the bl_order has 3 million rows=
=20
>and the bl_billing_date has only one row
>select /*+ / substr(bl.fulfilling_florist,1,8) ,
> substr(bl.sending_florist,1,8) ,
> substr(to_char(bl.delivery_date,'mmddyy'),1,6) ,
> substr(upper(bl.to_last_name),1,3) ,
> 'ordr' ,
> to_char(bl.order_value *100,'000000000') ,
> to_char(bl.h_order_number,'0000000') ,
> lpad(substr(bl.internal_po,1,10),10,0) ,
> to_char(bl.inwire_seq_no,'00000') ,
> '00000000' ,
> bl.h_sequence_no
>from flowers.bl_order bl,
> bl_billing_dates dt
> where bl.h_order_number=3Dbt.h_order_number
>and MESSAGE_TYPE=3D6 AND bl.delivery_date
> between dt.from_date and dt.to_date
> and bl.message_type =3D 0
> and bl.wire_service =3D 'AFS'
>Down below is the Explain plan
>Execution Plan
>----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=3DCHOOSE (Cost=3D223955 Card=3D171=
 Byte
> s=3D33516)
>
> 1 0 NESTED LOOPS (Cost=3D223955 Card=3D171 Bytes=3D33516)
> 2 1 MERGE JOIN (CARTESIAN) (Cost=3D19211 Card=3D68248 Bytes=3D300=
2
> 912)
>
> 3 2 TABLE ACCESS (FULL) OF 'BL_BILLING_DATES' (Cost=3D1 Card
> =3D1 Bytes=3D18)
>
> 4 2 SORT (JOIN)
> 5 4 TABLE ACCESS (BY ROWID) OF 'BL_TRANS'
> 6 5 INDEX (RANGE SCAN) OF 'BLT_MT' (NON-UNIQUE)
> 7 1 TABLE ACCESS (BY ROWID) OF 'BL_ORDER'
> 8 7 INDEX (RANGE SCAN) OF 'BLO_ONUM' (NON-UNIQUE)
>
>
>
>
>Statistics
>----------------------------------------------------------
> 17 recursive calls
> 238 db block gets
> 180267 consistent gets
> 69442 physical reads
> 0 redo size
> 32982 bytes sent via SQL*Net to client
> 1413 bytes received via SQL*Net from client
> 30 SQL*Net roundtrips to/from client
> 2 sorts (memory)
> 1 sorts (disk)
> 298 rows processed
>
>Thanks in Advance
>Arun
>________________________________________________________________________
>Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com
>
>--=20
>Author: ARUN K C
> INET: arun_k_c_at_hotmail.com
>
>Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
>San Diego, California -- Public Internet access / Mailing Lists
>--------------------------------------------------------------------
>To REMOVE yourself from this mailing list, send an E-Mail message
>to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
>the message BODY, include a line containing: UNSUB ORACLE-L
>(or the name of mailing list you want to be removed from). You may
>also send the HELP command for other information (like subscribing).
>

J.M. Dias Costa
Laboratorio Nacional de Engenharia Civil Av Brasil 101

1700-066 Lisboa				dcosta_at_lnec.pt
Telef.: 351218443816 			dcosta_at_esoterica.pt
Fax.:   351218443015			tm 936201469	=09
				=09