Re: Query

From: Sagi <sag1rk_at_yahoo.com>
Date: 3 Dec 2002 09:25:10 -0800
Message-ID: <54d80104.0212030925.68bba715_at_posting.google.com>


deepesh_garg_at_yahoo.com (Deepesh Garg) wrote in message news:<b21bb3.0211271706.4648cd0e_at_posting.google.com>...
> Hi,
> I am running a query of this sort (on Oracle 8i) and it seems to hang
> most of the time. Can anyone tell me why does it hangs.
>
> update some_table set some_field = 'some value', primary_key_field =
> 100 where primary_key_field = 100
>
> I know its stupid, but I want to know that why this type of query
> hangs. If I remove the primari_key_field = 100 from set part of the
> query, it runs fine.
>
> -Deepesh.

Hi,

The reason your UPDATE operation being slow is because you are updating the primary key column (index exists on primary key).

Here is an example

SQL\> create table t as select rownum X, A.OBJECT_NAME FROM ALL_OBJECTS A ; SQL> SELECT COUNT(*) FROM T ;   COUNT(*)


     24922

SQL\> SET TIMING ON SQL> update t set x=x+50000
  2 where object_name like '/%' ;

13236 rows updated.

Elapsed: 00:00:14.60
SQL> rollback ;

Rollback complete.

SQL> ALTER TABLE ADD CONSTRAINT PK_T PRIMARY KEY (X) ; SQL> update t set x=x+50000
  2 where object_name like '/%' ;

13236 rows updated.

Elapsed: 00:00:17.60

When there was no Primary Key Constraint - there was no index - so its fast.
When there is Primary Key Constraing - there is or uses INDEX - so slow.

Here my UPDATE was trying to show you the differece of time for Records of 13236. The more bigger the table is the more difference in time. You have not told the no. of rows of your table.

Please let me know whether my example helps you. I believe in learning from your feedbacks. Please tell me what every you feel....

Regards,
Sagi Received on Tue Dec 03 2002 - 18:25:10 CET

Original text of this message