Slow Update When Using Oracle PL/SQL Table

Question

We're using a PL/SQL table (named pTable) to collect a number of ids to be updated.

However, the statement

UPDATE aTable
SET aColumn = 1
WHERE id IN (SELECT COLUMN_VALUE
                     FROM   TABLE (pTable));

takes a long time to execute.

It seems that the optimizer comes up with a very bad execution plan, instead of using the index that is defined on id (as the primary key) it decides to use a full table scan on the aTable. pTable usually contains very few values (in most cases just one).

What can we do to make this faster? The best we've come up with is to handle low pTable.Count (1 and 2) as special cases, but that is certainly not very elegant.

Thanks for all the great suggestions. I wrote about this issue in my blog at http://smartercoding.blogspot.com/2010/01/performance-issues-using-plsql-tables.html.

Answer 1

The bad execution plan is probably unavoidable (unfortunately). There is no statistics information for the PL/SQL table, so the optimizer has no way of knowing that there are few rows in it. Is it possible to use hints in an UPDATE? If so, you might force use of the index that way.

Answer 2

I wonder if the MATERIALIZE hint in the subselect from the PL/SQL table would force a temp table instantiation and help the optimizer?

UPDATE aTable
SET aColumn = 1
WHERE id IN (SELECT /*+ MATERIALIZE */ COLUMN_VALUE
                     FROM   TABLE (pTable));

Answer 3

You can try the cardinality hint. This is good if you know (roughly) the number of rows in the collection.

UPDATE aTable 
SET aColumn = 1 
WHERE id IN (SELECT /*+ cardinality( pt 10 ) */ 
                     COLUMN_VALUE 
              FROM   TABLE (pTable) pt ); 

Answer 4

It helped to tell the optimizer to use the "correct" index instead of going on a wild full-table scan:

UPDATE /*+ INDEX(aTable PK_aTable) */aTable
SET aColumn = 1
WHERE id IN (SELECT COLUMN_VALUE
                  FROM   TABLE (CAST (pdarllist AS list_of_keys)));

I couldn't apply this solution to more complicated scenarios, but found other workarounds for those.

Answer 5

Here's another approach. Create a temporary table:

create global temporary table pTempTable ( id int primary key )
    on commit delete rows;

To perform the update, populate pTempTable with the contents of pTable and execute:

update
(
    select aColumn
    from aTable aa join pTempTable pp on aa.id = pp.id
)
set aColumn = 1;

The should perform reasonably well without resorting to optimizer hints.

Answer 6

You could try adding a ROWNUM < ... clause. In this test a ROWNUM < 30 changes the plan to use an index. Of course that depends on your set of values having a reasonable maximum size.

create table atable (acolumn number, id number);
insert into atable select rownum, rownum from dual connect by level < 150000;
alter table atable add constraint atab_pk primary key (id);

exec dbms_stats.gather_table_stats(ownname => user, tabname => 'ATABLE');

create type type_coll is table of number(4);
/

declare
    v_coll type_coll;
begin
  v_coll := type_coll(1,2,3,4);
  UPDATE aTable
  SET aColumn = 1
  WHERE id IN (SELECT COLUMN_VALUE
                     FROM   TABLE (v_coll));
end;
/

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
UPDATE ATABLE SET ACOLUMN = 1 WHERE ID IN (SELECT COLUMN_VALUE FROM TABLE (:B1 ))
----------------------------------------------------------------------------------------------
| Id  | Operation                           | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT                    |        |       |       |   142 (100)|          |
|   1 |  UPDATE                             | ATABLE |       |       |            |          |
|*  2 |   HASH JOIN RIGHT SEMI              |        |     1 |    11 |   142   (8)| 00:00:02 |
|   3 |    COLLECTION ITERATOR PICKLER FETCH|        |       |       |            |          |
|   4 |    TABLE ACCESS FULL                | ATABLE |   150K|  1325K|   108   (6)| 00:00:02 |
----------------------------------------------------------------------------------------------

declare
    v_coll type_coll;
begin
  v_coll := type_coll(1,2,3,4);
  UPDATE aTable
  SET aColumn = 1
  WHERE id IN (SELECT COLUMN_VALUE
                     FROM   TABLE (v_coll)
                            where rownum < 30);
end;
/


PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------
UPDATE ATABLE SET ACOLUMN = 1 WHERE ID IN (SELECT COLUMN_VALUE FROM TABLE (:B1 ) WHERE
ROWNUM < 30)

---------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT                       |          |       |       |    31 (100)|          |
|   1 |  UPDATE                                | ATABLE   |       |       |            |          |
|   2 |   NESTED LOOPS                         |          |     1 |    22 |    31   (4)| 00:00:01 |
|   3 |    VIEW                                | VW_NSO_1 |    29 |   377 |    29   (0)| 00:00:01 |
|   4 |     SORT UNIQUE                        |          |     1 |    58 |            |          |
|*  5 |      COUNT STOPKEY                     |          |       |       |            |          |
|   6 |       COLLECTION ITERATOR PICKLER FETCH|          |       |       |            |          |
|*  7 |    INDEX UNIQUE SCAN                   | ATAB_PK  |     1 |     9 |     0   (0)|          |
---------------------------------------------------------------------------------------------------