Re: update with multi table join

From: Daniel Morgan <dmorgan_at_exesolutions.com>
Date: Mon, 16 Sep 2002 16:04:57 GMT
Message-ID: <3D8600F5.EEF9E330@exesolutions.com>

Ken Chesak wrote:

> Daniel Morgan <dmorgan_at_exesolutions.com> wrote in message news:<3D820817.676C42D9_at_exesolutions.com>...
> > Ken Chesak wrote:
> >
> > > I am using the following SQL to update a table. It requires a join to
> > > 4 other tables. This update takes about 35 minutes to run on 20,000
> > > records. That seems rather long. Is there a better way to perform
> > > this update?
> > > Thank,
> > > ex-Sybase programmer
> > >
> > > for m_rec in
> > > (select c.cd_income,b.id_tsi,b.id_line
> > > from w_tsi_payer a,
> > > w_tsi_income b,
> > > tsi_type_income c,
> > > w_rg101 d,
> > > w_tsi_client e
> > > where e.id_rg101 = d.id_rg101
> > > and b.id_tsi = a.id_tsi
> > > and a.id_tsi = e.id_tsi
> > > and b.id_line = a.id_line
> > > and d.cd_case_category = c.cd_case_category
> > > and a.payer_doc_type = c.payer_doc_type
> > > and b.type_income = c.type_income )
> > >
> > > loop
> > > update w_tsi_income
> > > set cd_income = m_rec.cd_income
> > > where id_tsi = m_rec.id_tsi
> > > and id_line = m_rec.id_line;
> > > end loop;
> > >
> > > commit;
> >
> > No platform. No operating system. No Oracle version or edition. No
> > indication of whether the optimizer is RULE or CBO. No indication whether
> > statistics are current if CBO. No indication of whether indexes exist on
> > any of the columns in either WHERE clause, no EXPLAIN PLAN. No TKPROF.
> >
> > How much help do you expect? ;-)
> >
> > Add rowid to your cursor and update by rowid. May not do a darned thing
> > but then your posting didn't either.
> >
> > Daniel Morgan

> Platorm is Sun Unix, Oracle 8.1.7.2.0, optimzier is CBO, all tables
> have been analized with compute statistics, each table has an index
> which is being used for the join.

> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=28 Card=1 Bytes=109)
> 1 0 NESTED LOOPS (Cost=28 Card=1 Bytes=109)
> 2 1 NESTED LOOPS (Cost=27 Card=1 Bytes=100)
> 3 2 NESTED LOOPS (Cost=27 Card=1 Bytes=84)
> 4 3 NESTED LOOPS (Cost=27 Card=1 Bytes=58)
> 5 4 TABLE ACCESS (FULL) OF 'W_TSI_INCOME' (Cost=27
> Card=1 Bytes=
> 29)
> 6 4 TABLE ACCESS (BY INDEX ROWID) OF 'W_TSI_PAYER'
> 7 6 INDEX (RANGE SCAN) OF 'IX_W_TSI_PAYER_02'
> (NON-UNIQUE)
> 8 3 TABLE ACCESS (BY INDEX ROWID) OF 'W_TSI_CLIENT'
> 9 8 INDEX (RANGE SCAN) OF 'IX_W_TSI_CLIENT_02'
> (NON-UNIQUE)
> 10 2 TABLE ACCESS (BY INDEX ROWID) OF 'W_RG101'
> 11 10 INDEX (RANGE SCAN) OF 'IX_W_RG101_02' (NON-UNIQUE)
> 12 1 TABLE ACCESS (FULL) OF 'TSI_TYPE_INCOME' (Cost=1
> Card=193 Bytes=17
> 37)

> Questions:
> - I can run explain plan on SQL in the stored procedure but not on the
> stored procedure?
> - Is not the CBO always the default?
> - My real question is does this look like the proper SQL to do an
> update with a multi table join? Is there a better way?
> - Once during testing the code table was empty, which is
> tsi_type_income, the stored procedure never completed? Why?
>
> Thanks

You are correct that EXPLAIN PLAN works on SQL but not on procedures. What everyone does is extract the SQL from their procedures, functions, triggers, and packages, and explain plan it independently. To perform the equivalent on an entire procedure use the DBMS_PROFILER built-in package.
CBO is perhaps the default with 8i. But it is amazing how many times I find that underqualified DBAs have copied old init.oras from install to install and run 8i with RULE. And current statistics are more a rarity than one might wish to believe.
Oracle does not have the limitations on transaction size that Sybase has. I see no reason for the cursor and the loop. I would just do the entire thing in a single SQL statement. I will definitely perform faster.

But part of the problem may be 8.1.7.2.0. My recollection is that this version had a bug in the optimizer code (I could be wrong on this so check it out). If there is no reason not to ... patch it or upgrade to 8.1.7.3 or 8.1.7.4.

Daniel Morgan Received on Mon Sep 16 2002 - 11:04:57 CDT