| 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
Ken Chesak wrote:
> 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
>
>
In Oracle rowid has nothing to do with primary keys or any other column data. It is what is known as a pseudo-column and is the unique pointer to a specific row stored in an index. Try the following query:
SELECT rowid, cd_income
FROM tsi_type_income
WHERE rownum < 20;
And just for your information ... rownum has nothing to do with anything stored in a table but is the numbering of rows returned by the statement. It is important that you understand both key words.
Daniel Morgan Received on Tue Sep 17 2002 - 10:47:15 CDT
 |
 |