[askdba] Re: SQL -HELP

  • From: "Patricia Eyenga" <patricia.e@xxxxxxxx>
  • To: <askdba@xxxxxxxxxxxxx>
  • Date: Thu, 16 Dec 2004 08:40:12 -0500

Can you send your merge statement?

-----Original Message-----
From: askdba-bounce@xxxxxxxxxxxxx [mailto:askdba-bounce@xxxxxxxxxxxxx] =
On
Behalf Of Rick_Cale@xxxxxxxxxxxxxx
Sent: Thursday, December 16, 2004 7:34 AM
To: askdba@xxxxxxxxxxxxx
Subject: [askdba] Re: SQL -HELP







Hi DBAs

I have a merge statement that works fine if rows exist in the table.  If =
the
table is empty then the MERGE does not work.  I assume it is due to =
since no
records MATCH/NOMATCH cannot be determine.  This seems like a flaw in =
this
statement.  Is this correct?

Thanks


Rick Cale
Database Administrator
Team Health, Inc.
865-293-5425
Rick_Cale@xxxxxxxxxxxxxx


=20

                      Abraham Kurian

                      <kurian24@xxxxxxx        To:
askdba@xxxxxxxxxxxxx                                                     =
=20
                      om>                      cc:

                      Sent by:                 Subject:  [askdba] Re: =
SQL
-HELP                                                   =20
                      askdba-bounce@fre

                      elists.org

=20

=20

                      12/07/2004 11:05

                      PM

                      Please respond to

                      askdba

=20

=20





Is Stats are updated ? what about the amount of data each table in =
holding ?

Regards
Kurian


On Tue, 7 Dec 2004 15:49:15 -0800, VuCanDo <vuquyen@xxxxxxxxx> wrote:
> Hi Guru,
>
>   I have a SQL statement and run against 2 schemas exactly the same,=20
> but both give me a different explain plan run on same instance 9.2.0.4 =

> on NT.  Schema#2 seem to take longer then schema#1 as you can see.=20
> What's wrong with this SQL?  See below:
>
> select m.score_dt, count(1), sum(paid_amt), sum(allow_amt),=20
> min(overall_score), max(overall_score) from spy_mart_score m,=20
> spy_claim_case c where m.claim_id =3D getClaim(c.claim_id) and=20
> m.claim_line =3D
> getLine(c.claim_id) AND c.status =3D 'PEND' AND case_queue =3D '1' =
group=20
> by m.score_dt order by m.score_dt desc;
>
> Explain plan from SCHEMA#1 is
>
> FIRST_ROWS    SELECT STATEMENT   Cost =3D 22
>
> 1.1     SORT GROUP BY
> 2.1       TABLE ACCESS BY INDEX ROWID SPY_SCORE
> 3.1         NESTED LOOPS
> 4.1           NESTED LOOPS
> 5.1             TABLE ACCESS FULL SPY_CLAIM_CASE
> 5.2             TABLE ACCESS BY INDEX ROWID SPY_MART
> 6.1               INDEX UNIQUE SCAN SPY_MART_K1
> 4.2           INDEX RANGE SCAN SPY_SCORE_K1
>
> Explain plan from SCHEMA#2 is
>
> FIRST_ROWS    SELECT STATEMENT   Cost =3D 27169
>
> 1.1     SORT GROUP BY
> 2.1       NESTED LOOPS OUTER
> 3.1         HASH JOIN
> 4.1           TABLE ACCESS FULL SPY_CLAIM_CASE
> 4.2           TABLE ACCESS FULL SPY_MART
> 3.2         TABLE ACCESS BY INDEX ROWID SPY_SCORE
> 4.1           INDEX UNIQUE SCAN SPY_SCORE_K1
>
> Thanks,
>
> Vu
>
>







Other related posts: