Optimization of update statements (functions in the where clause)

Question

Now I feel dumb.

I need to update 100,000 rows in a database that I do not have direct access to. The total number of rows in the table is about 500,000 rows. The update simply adds one character to the field if the length is <3. Basically:

UPDATE X SET VALUE = '0' || VALUE WHERE LENGTH (VALUE) <3

So, I send this update to the database administrator and they return it to me, saying that the statement is too expensive (because the table is full access and the commit is 100k) and that I should write proces instead. And then they provide me with sample code if I don't know how to create it.

I tell WTF, how will a process run faster than a single update statement? Afer does some tests, my update takes 30 seconds, the process, following the code example, takes 10 minutes.

So, the real question, after all this decomposition, is this: is there a way to avoid the full acces table when using such a function in the where clause? (index is indexed)

Answer 1

. (Full Table Scan). , /. CAN , , ( : 100 ) .

:

• SQL, .
• SQL-, PL/SQL.
• PL/SQL, Java.
• Java, C.
• C, , .

Answer 2

100 . 500 . (.. 20%) , .
, .

Answer 3

, :

X  SET VALUE = LPAD (VALUE, 3, '0')  WHERE LENGTH (VALUE) < 3

... .

Answer 4

- , .

(!), , PL/SQL:

DECLARE mylimit 10000; /* Arbitrary limit */
BEGIN
  LOOP
    UPDATE X SET VALUE = '0'||VALUE WHERE LENGTH(VALUE) < 3 and ROWNUM<=mylimit;
    EXIT WHEN SQL%ROWCOUNT<mylimit;
  END LOOP;
END;

, , (VALUE) = 1 , WHERE. , ...

Answer 5

, , , - UPDATE . , , LIMIT 1000 , ( , ).

, ; Bash, LIMIT , , sleep # . , .

---

- , UPDATE - . , , , , .

, , ( , ). , (.. WHERE LENGTH(name) < 3). , , , , . , , 100- 2 . , .

Answer 6

(, - ), , (, PK) .

"chunking", - . , , , - , .

, , , . ( , v $session_longops.)

Answer 7

A functional index can help speed up udpates.

create index x_idx1 on x(length(value));

Here is an example.

sqlplus>create table t
  2  ( id NUMBER(9) PRIMARY KEY,
  3  name VARCHAR2(100)
  4  );
Table created.

sqlplus>insert into t select object_id, object_name from user_objects;
2188 rows created.

sqlplus>exec  dbms_stats.gather_table_stats(ownname=>'test',tabname =>'t');
PL/SQL procedure successfully completed.

sqlplus>create index t_idx3 on t(length(name));
Index created.

sqlplus>explain plan for update t set name = name || '1' where length(name) < 25;
Explained.

sqlplus>select * from table(dbms_xplan.display);
--------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT             |        |   109 |  2616 |     4   (0)| 00:00:01 |
|   1 |  UPDATE                      | T      |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| T      |   109 |  2616 |     4   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | T_IDX3 |    20 |       |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access(LENGTH("NAME")<25)
15 rows selected.