[askdba] Re: SQL -HELP

  • From: Regis Biassala <Regis.Biassala@xxxxxxxxxx>
  • To: askdba@xxxxxxxxxxxxx
  • Date: Thu, 16 Dec 2004 14:27:14 -0000

Why would you want to merge something with nothing!
Merge is behaving as it should....
Post the query...as Patty said....Because she said so.

Regis


-----Original Message-----
From: askdba-bounce@xxxxxxxxxxxxx [mailto:askdba-bounce@xxxxxxxxxxxxx] On
Behalf Of Rick_Cale@xxxxxxxxxxxxxx
Sent: 16 December 2004 12:34
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


 

                      Abraham Kurian

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

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

                      elists.org

 

 

                      12/07/2004 11:05

                      PM

                      Please respond to

                      askdba

 

 





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,
> 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.
> What's wrong with this SQL?  See below:
>
> select m.score_dt, count(1), sum(paid_amt), sum(allow_amt),
> min(overall_score), max(overall_score)
> from spy_mart_score m, spy_claim_case c
> where m.claim_id = getClaim(c.claim_id) and m.claim_line =
> getLine(c.claim_id) AND c.status = 'PEND' AND case_queue = '1'
> group by m.score_dt
> order by m.score_dt desc;
>
> Explain plan from SCHEMA#1 is
>
> FIRST_ROWS    SELECT STATEMENT   Cost = 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 = 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: