Re: Merge join cartesian

From: arun <arun_k_c_at_hotmail.com>
Date: Wed, 2 Aug 2000 07:17:08 -0400
Message-Id: <10577.113572@fatcity.com>

This is a multi-part message in MIME format.

------=_NextPart_000_000A_01BFFC51.AB48D1D0 Content-Type: text/plain;

charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

RE: Merge join cartesianthere is an sql called plustrace.sql in the = sqlplus/admin directory this has to be run by sys after running this = grant the plustrace role to the user who wants to get the statistics. once this is done go to the sqlplus and in the options menu click on = auto trace as on
then you will get these statistics and at the same time the plan_table = also should be existing on the users schema for this you will have to = run utlxplan.sql which is available in the rdbms/admin directory.

Original Message -----=20 From: Siva_Chintalapati=20 To: Multiple recipients of list ORACLE-L=20 Sent: Wednesday, August 02, 2000 7:07 AM Subject: RE: Merge join cartesian

Hi,=20
How toanalize in this way.How will we get the output as=20

Statistics=20

    ----------------------------------------------------------=20
             17  recursive calls=20
            238  db block gets=20
         180267  consistent gets=20
          69442  physical reads=20
              0  redo size=20
          32982  bytes sent via SQL*Net to client=20
           1413  bytes received via SQL*Net from client=20
             30  SQL*Net roundtrips to/from client=20
              2  sorts (memory)=20
              1  sorts (disk)=20

          298  rows processed=20
  .=20
          Can U pl give me the syntax.=20

Regards=20
Siva=20

----------=20
From: ARUN K C[SMTP:arun_k_c_at_hotmail.com]=20 Reply To: ORACLE-L_at_fatcity.com=20 Sent: Wednesday, August 02, 2000 4:15 AM=20

    To:     Multiple recipients of list ORACLE-L=20
    Subject:        Merge join cartesian=20

Hello all,=20
I have a particular query down below which is doing a Merge join = Cartesian=20

on one of the tables below=20
Can somebody please let me know how to solve this issue=20 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=20 select /*+ / substr(bl.fulfilling_florist,1,8) ,=20

           substr(bl.sending_florist,1,8) ,=20
           substr(to_char(bl.delivery_date,'mmddyy'),1,6) ,=20
           substr(upper(bl.to_last_name),1,3) ,=20
           'ordr' ,=20
           to_char(bl.order_value *100,'000000000') ,=20
           to_char(bl.h_order_number,'0000000')  ,=20
           lpad(substr(bl.internal_po,1,10),10,0) ,=20
           to_char(bl.inwire_seq_no,'00000') ,=20
           '00000000' ,=20
           bl.h_sequence_no=20
    from flowers.bl_order bl,=20
         bl_billing_dates dt=20
        where bl.h_order_number=3Dbt.h_order_number=20
    and  MESSAGE_TYPE=3D6 AND bl.delivery_date=20
                                    between dt.from_date and dt.to_date=20
                                    and bl.message_type =3D 0=20
                                    and bl.wire_service =3D 'AFS'=20

Down below is the Explain plan=20
Execution Plan=20

    ----------------------------------------------------------=20
       0      SELECT STATEMENT Optimizer=3DCHOOSE (Cost=3D223955 =
Card=3D171 Byte=20
              s=3D33516)=20

       1    0   NESTED LOOPS (Cost=3D223955 Card=3D171 Bytes=3D33516)=20
       2    1     MERGE JOIN (CARTESIAN) (Cost=3D19211 Card=3D68248 =
Bytes=3D3002=20
              912)=20

       3    2       TABLE ACCESS (FULL) OF 'BL_BILLING_DATES' (Cost=3D1 =
Card=20
              =3D1 Bytes=3D18)=20

       4    2       SORT (JOIN)=20
       5    4         TABLE ACCESS (BY ROWID) OF 'BL_TRANS'=20
       6    5           INDEX (RANGE SCAN) OF 'BLT_MT' (NON-UNIQUE)=20
       7    1     TABLE ACCESS (BY ROWID) OF 'BL_ORDER'=20
       8    7       INDEX (RANGE SCAN) OF 'BLO_ONUM' (NON-UNIQUE)=20

Statistics=20

    ----------------------------------------------------------=20
             17  recursive calls=20
            238  db block gets=20
         180267  consistent gets=20
          69442  physical reads=20
              0  redo size=20
          32982  bytes sent via SQL*Net to client=20
           1413  bytes received via SQL*Net from client=20
             30  SQL*Net roundtrips to/from client=20
              2  sorts (memory)=20
              1  sorts (disk)=20
            298  rows processed=20

Thanks in Advance=20
Arun=20
=

________________________________________________________________________ =

Get Your Private, Free E-mail from MSN Hotmail at = http://www.hotmail.com=20

    --=20
    Author: ARUN K C=20
      INET: arun_k_c_at_hotmail.com=20

Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051=20 San Diego, California -- Public Internet access / Mailing = Lists=20

To REMOVE yourself from this mailing list, send an E-Mail message=20 to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in=20 the message BODY, include a line containing: UNSUB ORACLE-L=20 (or the name of mailing list you want to be removed from). You may=20 also send the HELP command for other information (like subscribing). =

------=_NextPart_000_000A_01BFFC51.AB48D1D0 Content-Type: text/html;

charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD><TITLE>RE: Merge join cartesian</TITLE>
<META http-equiv=3DContent-Type content=3D"text/html; =

charset=3Diso-8859-1">

<META content=3D"MSHTML 5.50.4134.600" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT face=3DArial size=3D2>there is an sql called plustrace.sql in =

the=20
sqlplus/admin directory this has to be run by sys after running this = grant the=20
plustrace role to the user who wants to get the statistics.</DIV> <DIV>once this is done go to the sqlplus and = in the=20
options menu click on auto trace as on</DIV> <DIV>then you will get these statistics and = at the same=20
time the plan_table also should be existing on the users schema for this = you=20
will have to run utlxplan.sql which is available in the rdbms/admin=20 directory.</DIV>
<DIV> </DIV>
<BLOCKQUOTE=20
style=3D"PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; = BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px"> <DIV style=3D"FONT: 10pt arial">----- Original Message ----- </DIV> <DIV=20
 style=3D"BACKGROUND: #e4e4e4; FONT: 10pt arial; font-color: = black">From:=20
 <A title=3DSiva_Chintalapati_at_satyam.com=20 href=3D"mailto:Siva_Chintalapati_at_satyam.com">Siva_Chintalapati</A> = </DIV>
 <DIV style=3D"FONT: 10pt arial">To: <A = title=3DORACLE-L_at_fatcity.com=20
 href=3D"mailto:ORACLE-L_at_fatcity.com">Multiple recipients of list = ORACLE-L</A>=20
 </DIV>
 <DIV style=3D"FONT: 10pt arial">Sent: Wednesday, August 02, = 2000 7:07=20
 AM</DIV>
 <DIV style=3D"FONT: 10pt arial">Subject: RE: Merge join = cartesian</DIV>
 <DIV> </DIV>
 Hi, How toanalize in this way.How will we get the = output=20
 as=20
 <UL>
 Statistics <FONT =

face=3DArial=20

size=3D2>----------------------------------------------------------</FONT=

>=20

=20

17  recursive calls         238  db = block=20

gets      =

180267  consistent gets       69442  physical = reads=20

= 0  redo=20

size      =20

32982  bytes sent via SQL*Net to client <FONT = face=3DArial=20

size=3D2>       1413  bytes = received via=20

SQL*Net from client          30  = SQL*Net=20

roundtrips to/from client           = 2  sorts=20

(memory)           = 1  sorts=20

(disk) </UL>
        =20 298  rows processed <FONT face=3DArial color=3D#0000ff =

size=3D2>.         <FONT =

face=3DArial color=3D#0000ff size=3D2>Can U pl give me the = syntax. 
 Regards = Siva <UL>
 ---------- = <FONT=20

face=3D"MS Sans Serif" size=3D1>From:   <FONT=20 face=3D"MS Sans Serif" size=3D1>ARUN K = C[SMTP:arun_k_c_at_hotmail.com]=20

Reply To:=20       ORACLE-L_at_fatcity.com <FONT face=3D"MS Sans = Serif"=20

size=3D1>Sent:   Wednesday,=20

August 02, 2000 4:15 AM To:     <FONT face=3D"MS Sans = Serif"=20

size=3D1>Multiple recipients of list ORACLE-L <FONT=20 face=3D"MS Sans Serif" size=3D1>Subject:=20        Merge=20

join cartesian 
Hello all, I=20

have a particular query down below which is doing a Merge join = Cartesian=20

on one of the tables = below=20

Can somebody please let me know how = to solve=20

this issue The Table Bl_trans = has 10=20

million rows and the bl_order has 3 million rows <FONT = face=3DArial=20

size=3D2>and the bl_billing_date has only one row <FONT = face=3DArial=20

size=3D2>select /*+ / substr(bl.fulfilling_florist,1,8) , = <FONT=20

face=3DArial size=3D2>      =20 substr(bl.sending_florist,1,8) ,       =20 substr(to_char(bl.delivery_date,'mmddyy'),1,6) , <FONT = face=3DArial=20

size=3D2>      =20 substr(upper(bl.to_last_name),1,3) ,        'ordr' , = <FONT=20

face=3DArial size=3D2>      =20 to_char(bl.order_value *100,'000000000') , <FONT = face=3DArial=20

size=3D2>      =20 to_char(bl.h_order_number,'0000000')  , <FONT = face=3DArial=20

size=3D2>      =20 lpad(substr(bl.internal_po,1,10),10,0) , <FONT = face=3DArial=20

size=3D2>      =20 to_char(bl.inwire_seq_no,'00000') ,        '00000000' , = <FONT=20

face=3DArial size=3D2>      =20 bl.h_sequence_no from = flowers.bl_order=20

bl,     =20 bl_billing_dates dt    =20

where bl.h_order_number=3Dbt.h_order_number <FONT = face=3DArial=20

size=3D2>and  MESSAGE_TYPE=3D6 AND bl.delivery_date=20        =20

    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <FONT face=3DArial =

size=3D2>between=20

dt.from_date and dt.to_date=20        =20

    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <FONT face=3DArial =

size=3D2>and=20

bl.message_type =3D 0 =

<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=20
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <FONT face=3DArial =

size=3D2>and=20

bl.wire_service =3D 'AFS' Down below is the=20

Explain plan Execution = Plan=20

<FONT face=3DArial=20
=

size=3D2>----------------------------------------------------------</FONT=

>=20

= 0     =20

SELECT STATEMENT Optimizer=3DCHOOSE (Cost=3D223955 Card=3D171 = Byte=20

=20 s=3D33516) 
    1    = 0  =20

NESTED LOOPS (Cost=3D223955 Card=3D171 Bytes=3D33516) = <FONT face=3DArial=20

size=3D2>   2    1     = MERGE JOIN=20
(CARTESIAN) (Cost=3D19211 Card=3D68248 Bytes=3D3002 <FONT = face=3DArial=20

size=3D2>          =

912)</FONT>=20
    </P>
    <P><FONT face=3DArial size=3D2>&nbsp;&nbsp; 3&nbsp;&nbsp;&nbsp;=20

2       TABLE ACCESS (FULL) OF=20 'BL_BILLING_DATES' (Cost=3D1 Card           =3D1 =

Bytes=3D18) 
    4   =20 2       SORT (JOIN) <FONT=20 face=3DArial size=3D2>   5   =20 4         TABLE ACCESS (BY = ROWID) OF=20
 'BL_TRANS'   =20 6   =20
 5           INDEX = (RANGE=20
 SCAN) OF 'BLT_MT' (NON-UNIQUE)    7    1     = TABLE=20
 ACCESS (BY ROWID) OF 'BL_ORDER'    8   =20 7       INDEX (RANGE SCAN) OF = 'BLO_ONUM'=20
 (NON-UNIQUE) Statistics <FONT =

face=3DArial=20

size=3D2>----------------------------------------------------------</FONT=

>=20

=20

17  recursive calls         238  db = block=20

gets      =

180267  consistent gets       69442  physical = reads=20

= 0  redo=20

size      =20

32982  bytes sent via SQL*Net to client <FONT = face=3DArial=20

size=3D2>       1413  bytes = received via=20

SQL*Net from client          30  = SQL*Net=20

roundtrips to/from client           = 2  sorts=20

(memory)           = 1  sorts=20

(disk)         298  rows=20 processed 
Thanks  in Advance = <FONT=20

face=3DArial size=3D2>Arun <FONT face=3DArial=20
=

size=3D2>________________________________________________________________=

________=20

Get Your Private, Free E-mail from = MSN Hotmail=20

at <A = target=3D_blank=20

=

href=3D"http://www.hotmail.com">http://www.hotmail.com</A> =

-- Author:=20

ARUN K C   INET:=20 arun_k_c_at_hotmail.com 
Fat City Network = Services    --=20

(858) 538-5051  FAX: (858) 538-5051 <FONT = face=3DArial=20

size=3D2>San Diego, =
California        --=20

Public Internet access / Mailing Lists <FONT face=3DArial =

size=3D2>----------------------------------------------------------------=

----=20

To REMOVE yourself from this mailing = list, send=20

an E-Mail message to:=20 ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and = in=20 Received on Wed Aug 02 2000 - 06:17:08 CDT