| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Merge join cartesian
This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.
------_=_NextPart_001_01BFFC67.38636F80
Content-Type: text/plain
Hi,
How toanalize in this way.How will we get the output as
> 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
.
Can U pl give me the syntax.
Regards
Siva
> ----------
> From: ARUN K C[SMTP:arun_k_c_at_hotmail.com]
> Reply To: ORACLE-L_at_fatcity.com
> Sent: Wednesday, August 02, 2000 4:15 AM
> To: Multiple recipients of list ORACLE-L
> Subject: Merge join cartesian
>
> 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
>
> --
> 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).
>
------_=_NextPart_001_01BFFC67.38636F80
Content-Type: text/html
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN"> <HTML> <HEAD> <META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =charset=3Dus-ascii">
<TITLE>RE: Merge join cartesian</TITLE> </HEAD> <BODY>
<P><FONT COLOR=3D"#0000FF" SIZE=3D2 FACE=3D"Arial">Hi,</FONT> <BR><FONT COLOR=3D"#0000FF" SIZE=3D2 FACE=3D"Arial">How toanalize in = this way.How will we get the output as</FONT>
<UL> <P><FONT SIZE=3D2 FACE=3D"Arial">Statistics</FONT> <BR><FONT SIZE=3D2 = FACE=3D"Arial">---------------------------------------------------------=-</FONT>
<P><FONT COLOR=3D"#0000FF" SIZE=3D2 FACE=3D"Arial">Regards</FONT> <BR><FONT COLOR=3D"#0000FF" SIZE=3D2 FACE=3D"Arial">Siva</FONT> </P> <UL> <P><FONT SIZE=3D1 FACE=3D"MS Sans Serif">----------</FONT> <BR><B><FONT SIZE=3D1 FACE=3D"MS Sans Serif">From:</FONT></B> = <FONT SIZE=3D1 FACE=3D"MS Sans Serif">ARUN K =C[SMTP:arun_k_c_at_hotmail.com]</FONT>
<P><FONT SIZE=3D2 FACE=3D"Arial">Hello all,</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">I have a particular query down below =
which is doing a Merge join Cartesian </FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">on one of the tables below</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">Can somebody please let me know how =
to solve this issue</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">The Table Bl_trans has 10 million =
rows and the bl_order has 3 million rows </FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">and the bl_billing_date has only one =
row</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">select /*+ / =
substr(bl.fulfilling_florist,1,8) ,</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial"> =
substr(bl.sending_florist,1,8) ,</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial"> =
substr(to_char(bl.delivery_date,'mmddyy'),1,6) ,</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial"> =
substr(upper(bl.to_last_name),1,3) ,</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial"> =
'ordr' ,</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial"> =
to_char(bl.order_value *100,'000000000') ,</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial"> =
to_char(bl.h_order_number,'0000000') ,</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial"> =
lpad(substr(bl.internal_po,1,10),10,0) ,</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial"> =
to_char(bl.inwire_seq_no,'00000') ,</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial"> =
'00000000' ,</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial"> =
bl.h_sequence_no</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">from flowers.bl_order bl,</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial"> =
bl_billing_dates dt</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial"> where =
bl.h_order_number=3Dbt.h_order_number</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">and MESSAGE_TYPE=3D6 AND =
bl.delivery_date</FONT>
<BR> =
= = <FONT SIZE=3D2 =FACE=3D"Arial">between dt.from_date and dt.to_date</FONT> <BR> =
= = <FONT SIZE=3D2 =FACE=3D"Arial">and bl.message_type =3D 0</FONT> <BR> =
= = <FONT SIZE=3D2 =FACE=3D"Arial">and bl.wire_service =3D 'AFS'</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">Down below is the Explain plan</FONT> <BR><FONT SIZE=3D2 FACE=3D"Arial">Execution Plan</FONT> <BR><FONT SIZE=3D2 = FACE=3D"Arial">---------------------------------------------------------=-</FONT>
<P><FONT SIZE=3D2 FACE=3D"Arial"> 1 =
0 NESTED LOOPS (Cost=3D223955 Card=3D171 =
Bytes=3D33516)</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial"> 2 =
1 MERGE JOIN (CARTESIAN) (Cost=3D19211 =
Card=3D68248 Bytes=3D3002</FONT>
<BR><FONT SIZE=3D2 =
FACE=3D"Arial"> =
912)</FONT>
</P>
<P><FONT SIZE=3D2 FACE=3D"Arial"> 3 =
2 TABLE ACCESS (FULL) OF =
'BL_BILLING_DATES' (Cost=3D1 Card</FONT>
<BR><FONT SIZE=3D2 =
FACE=3D"Arial"> =
=3D1 Bytes=3D18)</FONT>
</P>
<P><FONT SIZE=3D2 FACE=3D"Arial"> 4 =
2 SORT (JOIN)</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial"> 5 =
4 TABLE ACCESS (BY =
ROWID) OF 'BL_TRANS'</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial"> 6 =
5 INDEX =
(RANGE SCAN) OF 'BLT_MT' (NON-UNIQUE)</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial"> 7 =
1 TABLE ACCESS (BY ROWID) OF 'BL_ORDER'</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial"> 8 =
7 INDEX (RANGE SCAN) OF 'BLO_ONUM' =
(NON-UNIQUE)</FONT>
</P> <BR> <BR> <BR>
<P><FONT SIZE=3D2 FACE=3D"Arial">Statistics</FONT> <BR><FONT SIZE=3D2 =
FACE=3D"Arial">---------------------------------------------------------=-</FONT>
<P><FONT SIZE=3D2 FACE=3D"Arial">Thanks in Advance</FONT> <BR><FONT SIZE=3D2 FACE=3D"Arial">Arun</FONT> <BR><FONT SIZE=3D2 = FACE=3D"Arial">_________________________________________________________=_______________</FONT>
<P><FONT SIZE=3D2 FACE=3D"Arial">-- </FONT> <BR><FONT SIZE=3D2 FACE=3D"Arial">Author: ARUN K C</FONT> <BR><FONT SIZE=3D2 FACE=3D"Arial"> INET: =arun_k_c_at_hotmail.com</FONT>
<P><FONT SIZE=3D2 FACE=3D"Arial">Fat City Network =
Services -- (858) 538-5051 FAX: (858) =
538-5051</FONT>
<BR><FONT SIZE=3D2 FACE=3D"Arial">San Diego, =
California -- Public Internet =
access / Mailing Lists</FONT>
<BR><FONT SIZE=3D2 =
FACE=3D"Arial">---------------------------------------------------------=-----------</FONT>
 |
 |