Re: Slow update query

From: Tayfun Yetkin <yetkin_at_bornova.ege.edu.tr>
Date: 17 Mar 2004 04:45:51 -0800
Message-ID: <e82b22f6.0403170445.6a2602c0@posting.google.com>

This is the query and explain plan

EXPLAIN PLAN FOR
  UPDATE CELLCFG.NEIGHBOUR_LIST N1
  SET
(CAND, CS, KHYST, KOFFSETP, LHYST, LOFFSETP, TRHYST, TROFFSETP, AWOFFSET, BQOFFSET)=
(SELECT N2.CAND, N2.CS, N2.KHYST, N2.KOFFSET, N2.LHYST, N2.LOFFSET,          N2.TRHYST, N2.TROFFSET, N2.AWOFFSET, N2.BQOFFSET   FROM CELLTRANSFORM.NBRTEMP N2
  WHERE N1.MAIN_CELL_KEY=N2.MAINCELL
  AND N1.NEIGHBOUR_CELL_KEY=N2.NBRCELL); Plan Table

| Operation                 |  Name              |  Rows | Bytes|  Cost  | Pstar

t| Pstop |

| UPDATE STATEMENT          |                    |       |      |        |


 |       |


|  UPDATE                   |NEIGHBOUR_LIST      |       |      |        |


 |       |

Plan Table

|   TABLE ACCESS FULL       |NEIGHBOUR_LIST      |       |      |        |


 |       |


|   TABLE ACCESS FULL       |NBRTEMP             |       |      |        |


 |       |


--------------------------------------------------------------------------------

7 rows selected.

Execution Plan

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=58 Card=12257 Bytes=
          1927)

   1    0   UNION-ALL
   2    1     TABLE ACCESS (FULL) OF 'DUAL' (Cost=16 Card=4072)
   3    1     TABLE ACCESS (FULL) OF 'DUAL' (Cost=16 Card=4072)
   4    1     VIEW (Cost=10 Card=41 Bytes=1927)
   5    4       CONNECT BY (WITHOUT FILTERING)
   6    5         TABLE ACCESS (FULL) OF 'PLAN_TABLE' (Cost=1 Card=41
          Bytes=21525)

   7    5         SORT (AGGREGATE)
   8    7           TABLE ACCESS (FULL) OF 'PLAN_TABLE' (Cost=1 Card=1
           Bytes=39)

   9    1     TABLE ACCESS (FULL) OF 'DUAL' (Cost=16 Card=4072)

"Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> wrote in message news:<40571c27$0$6545$ed9e5944_at_reading.news.pipex.net>...

> I have two immediate comments and a question
> 
> 1. do you have appropriate indexes on the key columns?
> 2. if you have any records that don't match your where clause you are going
> to be updating a number of columns in CELLCFG.NEIGHBOUR_LIST to NULL, I
> suspect you don't want this.
> 
> Can you post the explain plan?
> 
> -- 
> Niall Litchfield
> Oracle DBA
> Audit Commission UK
> "Tayfun Yetkin" <yetkin_at_bornova.ege.edu.tr> wrote in message
> news:e82b22f6.0403160445.5bc23204_at_posting.google.com...
> > I want to update table by using another table columns. I wrote the
> > update query like as below. But it takes an hour and not finished. All
> > table structure is similar and similar size (220000 records for each).
> > How can optimize the query or is there any other solution for making
> > this update
> >
> >   UPDATE CELLCFG.NEIGHBOUR_LIST N1
> >   SET
> > (CAND, CS, KHYST, KOFFSETP, LHYST, LOFFSETP,
> > TRHYST, TROFFSETP, AWOFFSET, BQOFFSET)=
> >  (SELECT N2.CAND, N2.CS, N2.KHYST, N2.KOFFSET, N2.LHYST, N2.LOFFSET,
> >          N2.TRHYST, N2.TROFFSET, N2.AWOFFSET, N2.BQOFFSET
> >   FROM CELLTRANSFORM.NEIGHBOUR_LIST
> >   WHERE N1.MAIN_CELL_KEY=N2.MAINKEY
> >   AND N1.NEIGHBOUR_CELL_KEY=N2.NBRKEY);
> >
> > BR
> >
> > Tayfun

Received on Wed Mar 17 2004 - 06:45:51 CST