| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: update with multi table join
Daniel Morgan <dmorgan_at_exesolutions.com> wrote in message news:<3D8600F5.EEF9E330_at_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
>
> 1. 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.
>
> 2. 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.
>
> 3. 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
Thanks Dan for your reply. I was a bit surprised the exlain plan only works on SQL, I will look into the DBMS PROFILER. You mentioned doing the update without the cursor, can you give me an example? I tried several times to do it but always got errors. The table being updated is a work table, only used to load data and validate before being moved to the real tables, so there is no primary key. Is there a way to include rowid? Here is the sql without the loop,
update
2 (select b.cd_income x, c.cd_income y 3 from w_tsi_payer a, 4 w_tsi_income b, 5 tsi_type_income c, 6 w_rg101 d, 7 w_tsi_client e
9 and b.id_tsi = a.id_tsi 10 and a.id_tsi = e.id_tsi 11 and b.id_line = a.id_line 12 and d.cd_case_category = c.cd_case_category 13 and a.payer_doc_type = c.payer_doc_type 14 and b.type_income = c.type_income )15* set x = y
*
ERROR at line 15:
ORA-01779: cannot modify a column which maps to a non key-preserved
table
Received on Tue Sep 17 2002 - 09:29:11 CDT
 |
 |