代码之家 › 专栏 › 技术社区 › Thorsten

使用Oracle PL/SQL表时更新速度慢

plsql oracle

Thorsten · 技术社区 · 15 年前

我们使用一个pl/sql表(名为ptable)来收集一些要更新的ID。

但是,声明

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

执行需要很长时间。

优化器似乎想出了一个非常糟糕的执行计划,而不是使用ID上定义的索引(作为主键),而是决定对数据库使用全表扫描。ptable通常包含很少的值(在大多数情况下只有一个)。

我们能做些什么来加快速度?我们想到的最好办法是处理低ptable(1和2)作为特殊情况,但这肯定不是很优雅。

谢谢你所有的建议。我在我的博客中写到了这个问题 http://smartercoding.blogspot.com/2010/01/performance-issues-using-plsql-tables.html .

6 回复 | 直到 8 年前

APC 15 年前

您可以尝试基数提示。如果您知道(大致)集合中的行数,这是很好的。

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

Vadim K. 15 年前

这是另一种方法。创建临时表:

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

要执行更新,请填充 pTempTable 内容如下: pTable 并执行:

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

应该在不诉诸优化器提示的情况下运行良好。

Nils Weinander 15 年前

糟糕的执行计划可能不可避免(不幸的是)。pl/sql表没有统计信息,因此优化器无法知道其中的行数很少。是否可以在更新中使用提示?如果是这样,您可能会以这种方式强制使用索引。

Thorsten 15 年前

它有助于告诉优化器使用“正确的”索引,而不是进行一次疯狂的全表扫描:

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

我无法将此解决方案应用于更复杂的场景,但找到了其他解决方案。

Gary Myers 15 年前

您可以尝试添加rownum<…条款。在此测试中,rownum<30将更改计划以使用索引。当然,这取决于您的一组值具有合理的最大大小。

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)|          |
---------------------------------------------------------------------------------------------------

dpbradley 15 年前

我想知道来自pl/sql表的subselect中的materialize提示是否会强制临时表实例化并帮助优化器?

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