Merge join cartesian

From: ARUN K C <arun_k_c_at_hotmail.com>
Date: Tue, 01 Aug 2000 21:36:51 GMT
Message-Id: <10576.113529@fatcity.com>

Hello all,
I have a particular query down below which is doing a Merge join Cartesian 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 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=bt.h_order_number and MESSAGE_TYPE=6 AND bl.delivery_date

				between dt.from_date and dt.to_date
				and bl.message_type = 0
				and bl.wire_service = 'AFS'

Down below is the Explain plan
Execution Plan

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=223955 Card=171 Byte
          s=33516)

   1    0   NESTED LOOPS (Cost=223955 Card=171 Bytes=33516)
   2    1     MERGE JOIN (CARTESIAN) (Cost=19211 Card=68248 Bytes=3002
          912)

   3    2       TABLE ACCESS (FULL) OF 'BL_BILLING_DATES' (Cost=1 Card
          =1 Bytes=18)

   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 Received on Tue Aug 01 2000 - 16:36:51 CDT