Re: update with multi table join

From: Ken Chesak <datavector_at_hotmail.com>
Date: 16 Sep 2002 07:25:50 -0700
Message-ID: <3f2f39c4.0209160625.1e8fba1e@posting.google.com>

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 Received on Mon Sep 16 2002 - 09:25:50 CDT