DBA Hub

📋Steps in this guide1/4

Updates Based on Queries

This article describes how a table can be updated using data from another table.

oracle miscconfigurationintermediate
by OracleDba
14 views
1

Setup

The table contains 10,000 rows. The table contains 5,000 rows, each of which has a matching key value with a row from the table, but different data in the and columns. At this point we can see none of the values in the column of the table contain the word "Updated". The aim is to update the rows in the table with the data from the table.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
DROP TABLE dest_tab PURGE;
DROP TABLE source_tab PURGE;

CREATE TABLE dest_tab AS
SELECT level AS id,
       'CODE' || level AS code,
       'Description for ' || level AS description
FROM   dual
CONNECT BY level <= 10000;

ALTER TABLE dest_tab ADD CONSTRAINT dest_tab_pk PRIMARY KEY (id);


CREATE TABLE source_tab AS
SELECT level AS id,
       'CODE' || (level*10) AS code,
       'Updated description for ' || level AS description
FROM   dual
CONNECT BY level <= 5000;

ALTER TABLE source_tab ADD CONSTRAINT source_tab_pk PRIMARY KEY (id);

EXEC DBMS_STATS.gather_table_stats(USER, 'dest_tab');
EXEC DBMS_STATS.gather_table_stats(USER, 'source_tab');

SELECT COUNT(*)
FROM   dest_tab
WHERE  description LIKE 'Updated%';

  COUNT(*)
----------
         0

SQL>
2

Subquery Method

The first option is to do an update of the table using a subquery to pull the correct data from the table. Notice the predicate to exclude rows from the table with no matching row in the table. Without this, the unmatched rows will have their values set to NULL. The execution plan for the current data volume is shown below. If the workload is sufficiently large and the server can cope with the extra workload, the hint can be used to make this run in parallel.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
UPDATE dest_tab tt
SET    (tt.code, tt.description) = (SELECT st.code, st.description
                                    FROM   source_tab st
                                    WHERE  st.id = tt.id)
WHERE  EXISTS (SELECT 1
               FROM   source_tab
               WHERE  id = tt.id);

5000 rows updated.

SQL>


SELECT COUNT(*)
FROM   dest_tab
WHERE  description LIKE 'Updated%';

  COUNT(*)
----------
      5000

SQL>

ROLLBACK;

-----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT             |                |  4999 |   185K| 30013  (34)| 00:06:01 |
|   1 |  UPDATE                      | dest_tab       |       |       |            |          |
|   2 |   NESTED LOOPS SEMI          |                |  4999 |   185K|    19   (6)| 00:00:01 |
|   3 |    TABLE ACCESS FULL         | dest_tab       | 10000 |   332K|    18   (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN         | source_tab_PK  |  2500 | 10000 |     0   (0)| 00:00:01 |
|   5 |   TABLE ACCESS BY INDEX ROWID| source_tab     |     1 |    43 |     2   (0)| 00:00:01 |
|*  6 |    INDEX UNIQUE SCAN         | source_tab_PK  |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("ID"="TT"."ID")
   6 - access("ST"."ID"=:B1)

UPDATE /*+ PARALLEL(8) */ dest_tab tt
SET    (tt.code, tt.description) = (SELECT st.code, st.description
                                    FROM   source_tab st
                                    WHERE  st.id = tt.id)
WHERE  EXISTS (SELECT 1
               FROM   source_tab
               WHERE  id = tt.id);
3

Inline View Method

The second option is to join the two tables as an inline view and base the update on that. The execution plan for the current data volume is shown below. If the workload is sufficiently large and the server can cope with the extra workload, the hint can be used to make this run in parallel. The number of rows updated can be altered by adding a clause to either the inline view or the main update statement.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
UPDATE (SELECT tt.id,
               tt.code,
               tt.description,
               st.code AS st_code,
               st.description AS st_description
        FROM   dest_tab tt, source_tab st
        WHERE  tt.id = st.id) ilv
SET    ilv.code = ilv.st_code,
       ilv.description = ilv.st_description;

5000 rows updated.

SQL>


SELECT COUNT(*)
FROM   dest_tab
WHERE  description LIKE 'Updated%';

  COUNT(*)
----------
      5000

SQL>

ROLLBACK;

-----------------------------------------------------------------------------------
| Id  | Operation           | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT    |             |  5000 |   375K|    31   (4)| 00:00:01 |
|   1 |  UPDATE             | dest_tab    |       |       |            |          |
|*  2 |   HASH JOIN         |             |  5000 |   375K|    31   (4)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| source_tab  |  5000 |   209K|    12   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| dest_tab    | 10000 |   332K|    18   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("TT"."ID"="ST"."ID")

UPDATE /*+ PARALLEL(8) */
       (SELECT tt.id,
               tt.code,
               tt.description,
               st.code AS st_code,
               st.description AS st_description
        FROM   dest_tab tt, source_tab st
        WHERE  tt.id = st.id) ilv
SET    ilv.code = ilv.st_code,
       ilv.description = ilv.st_description;

-- Rows limited by the inline view.
UPDATE (SELECT tt.id,
               tt.code,
               tt.description,
               st.code AS st_code,
               st.description AS st_description
        FROM   dest_tab tt, source_tab st
        WHERE  tt.id = st.id
        AND    st.id  <= 2500) ilv
SET    ilv.code = ilv.st_code,
       ilv.description = ilv.st_description;

-- Rows limited by the WHERE clause of the UPDATE.
UPDATE (SELECT tt.id,
               tt.code,
               tt.description,
               st.code AS st_code,
               st.description AS st_description
        FROM   dest_tab tt, source_tab st
        WHERE  tt.id = st.id) ilv
SET    ilv.code = ilv.st_code,
       ilv.description = ilv.st_description
WHERE  ilv.id  <= 2500;
4

MERGE Statement Method

The third option is to use the statement, omitting the clause as it is not needed. The execution plan for the current data volume is shown below. If the workload is sufficiently large and the server can cope with the extra workload, the hint can be used to make this run in parallel. Remember, you can use queries as the source of a statement. The query can include a clause to limit the number of rows updated. For more information see: - MERGE Statement Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
MERGE INTO dest_tab tt
    USING source_tab st
    ON (tt.id = st.id)
  WHEN MATCHED THEN
    UPDATE SET tt.code = st.code,
               tt.description = st.description;

5000 rows merged.

SQL>


SELECT COUNT(*)
FROM   dest_tab
WHERE  description LIKE 'Updated%';

  COUNT(*)
----------
      5000

SQL>

ROLLBACK;

------------------------------------------------------------------------------------
| Id  | Operation            | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | MERGE STATEMENT      |             |  5000 |   429K|    31   (4)| 00:00:01 |
|   1 |  MERGE               | dest_tab    |       |       |            |          |
|   2 |   VIEW               |             |       |       |            |          |
|*  3 |    HASH JOIN         |             |  5000 |   375K|    31   (4)| 00:00:01 |
|   4 |     TABLE ACCESS FULL| source_tab  |  5000 |   209K|    12   (0)| 00:00:01 |
|   5 |     TABLE ACCESS FULL| dest_tab    | 10000 |   332K|    18   (0)| 00:00:01 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("TT"."ID"="ST"."ID")

MERGE /*+ APPEND PARALLEL(8) */ INTO dest_tab tt
USING source_tab st
ON (tt.id = st.id)
WHEN MATCHED THEN
  UPDATE SET tt.code = st.code,
             tt.description = st.description;

MERGE INTO dest_tab tt
    USING (SELECT * FROM source_tab WHERE id <= 2500) st
    ON (tt.id = st.id)
  WHEN MATCHED THEN
    UPDATE SET tt.code = st.code,
               tt.description = st.description;

Comments (0)

Please to add comments

No comments yet. Be the first to comment!