Re: Please help tuning a query

On 7 May 2003 12:49:51 -0700, rgaffuri_at_cox.net (Ryan Gaffuri) wrote:

>I have a staging table and a production table. I need to update my
>production table based based on what is in the staging table. So I
>have the following cursor.
>
>According to Guy Harrison's book running a cursor and then updating
>based on the rowid is supposed to be faster, however, I think the CBO
>is writing bad plan.
>
>I just used dbms_stats with estimate=20 and Cascade=>true on each of
>the tables.
>
>select b.col1,
> b.col2,
> b.etc
>from stage_Schema.tab s, prod_schema.tab p
>where s.col2 = p.col2
> and s.col3 = p.col3
> and so on...
>
>All of the primary key columns are joined. So I think the CBO should
>do a nested loop join with a fast full index scan. Each table has
>exactly the same number of rows.
>
>Here is my explain plan. Why is it using a sort merge? The update with
>the where exists is actually alot faster.
>
>Operation Object Name Rows Bytes Cost
>SELECT STATEMENT Hint=CHOOSE 2 M 102385 MERGE JOIN
> 2 M 398 M 102385 INDEX FULL SCAN PK_IND_TAB 2 M 97 M 23494
> SORT JOIN 2 M 298 M 74520 TABLE ACCESS
>FULL TAB 2 M 298 M 4371
>
>Sorry about the formatting. All the columns in the join are part of
>the primary key. I can force an index scan on the inner table, but I
>cant force a nested loop join???

You don't have any criteria on either table. So how do you expect it wouldn't choose a full table scan?
Also: did you change optimizer_index_cost_adj and optimizer_index_caching from the defaults, which are lousy and almost always result in full table scan?
Finally: why do you think the 'update with where exists is actually a lot faster'? You are aware the CBO tries to reduce the number of I/O's, it doesn't reduce the number of seconds elapsed?

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Wed May 07 2003 - 15:47:24 CDT