| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Slow update query
Hi
I suspect you need an index on N2(MAINCELL,NBRCELL) presumably a unique index (else the update will fail with single-row subquery...).
for comparison look at
SQL*Plus: Release 9.2.0.4.0 - Production on Wed Mar 17 13:09:07 2004
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
SQL> drop table t1;
Table dropped.
SQL> drop table t2;
Table dropped.
SQL> create table t1 as select * from all_objects;
Table created.
SQL> create table t2 as select * from all_objects;
Table created.
SQL> get c1
1 update t1
2 set (object_name,owner) =
3 (select t2.subobject_name,t2.owner
4* from t2 where t1.object_id=t2.object_id)
SQL> set autotrace on explain SQL> set timing on SQL> /
Elapsed: 00:00:00.02
SQL> alter t1 modify object_name null;
alter t1 modify object_name null
*
ERROR at line 1:
ORA-00940: invalid ALTER command
Elapsed: 00:00:00.00
SQL> alter table t1 modify object_name null;
Table altered.
Elapsed: 00:00:00.02
SQL> get c1
1 update t1
2 set (object_name,owner) =
3 (select t2.subobject_name,t2.owner
4* from t2 where t1.object_id=t2.object_id)
SQL> /
31393 rows updated.
Elapsed: 00:04:28.06
Execution Plan
0 UPDATE STATEMENT Optimizer=CHOOSE 1 0 UPDATE OF 'T1'
2 1 TABLE ACCESS (FULL) OF 'T1' 3 1 TABLE ACCESS (FULL) OF 'T2'
SQL> rollback;
Rollback complete.
Elapsed: 00:00:01.09
SQL> create index idx_1 on t2(object_id);
Index created.
Elapsed: 00:00:01.00
SQL> get c1
1 update t1
2 set (object_name,owner) =
3 (select t2.subobject_name,t2.owner
4* from t2 where t1.object_id=t2.object_id)
SQL> /
31393 rows updated.
Elapsed: 00:00:01.02
Execution Plan
0 UPDATE STATEMENT Optimizer=CHOOSE 1 0 UPDATE OF 'T1'
2 1 TABLE ACCESS (FULL) OF 'T1' 3 1 TABLE ACCESS (BY INDEX ROWID) OF 'T2' 4 3 INDEX (RANGE SCAN) OF 'IDX_1' (NON-UNIQUE)
-- Niall Litchfield Oracle DBA Audit Commission UK "Tayfun Yetkin" <yetkin_at_bornova.ege.edu.tr> wrote in message news:e82b22f6.0403170445.6a2602c0_at_posting.google.com...Received on Wed Mar 17 2004 - 07:26:19 CST
> 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
 |
 |