Hi Anurag,
I guess the simple and easy solution to ur problem is to use MERGE
statement, which is NEW to 9i. I have personally benchmarked as
opposed to cursors for a 22 million data and the MERGE just flies.
Thanks
Arindam
ganesh_at_gtfs-gulf.com (Ganesh Raja) wrote in message news:<a8aed4.0305080331.7dc04b1d_at_posting.google.com>...
> "Ryan" <rgaffuri_at_cox.net> wrote in message news:<3Teua.36540$g41.3163796_at_news1.east.cox.net>...
> > "Sybrand Bakker" <gooiditweg_at_nospam.demon.nl> wrote in message
> > news:8tribv00oec00lk7lagkcepia92uf103jd_at_4ax.com...
> > > 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
> >
> > Update with where exists is supposed to be slower according to the tuning
> > book Im using.
> >
> > Specs on the tables. Both are essentially identical. Same columns. Same
> > primary keys. In this case same number of rows. One of them however, has a
> > non-uniqie index on one of its primary key columns.
> >
> > I think it should use a nested loop join by doing a fast full index scan.
> >
> > Not familiar with the following parameters. Ill ask our dbas about them
> > tomorrow.
> >
> > optimizer_index_cost_adj
> > optimizer_index_caching
> >
> > What are the defaults? So I can check to see if we have them.
> >
> > Sorry about the messy query plan. I posted from the web.
> >
> > Operation Object
> > Name Rows Bytes Cost
> > SELECT STATEMENT Hint=CHOOSE 2M
> > 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
> >
> > could someone help me intepret this? Why is it doing both a 'merge join' and
> > a 'sort join' and what is the difference? I know what a sort-merge join is?
>
>
> Did u try doing the FTS in parallel. It will give u some performance
> improvments. If you have more than once CPU that is.
>
> Regards,
> Ganesh R
Received on Thu May 08 2003 - 14:06:55 CDT
