[askdba] Re: SQL -HELP
- From: Rick_Cale@xxxxxxxxxxxxxx
- To: askdba@xxxxxxxxxxxxx
- Date: Thu, 16 Dec 2004 07:34:06 -0500
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:
