UPDATE t
SET t.name = (SELECT a.name
FROM a
WHERE a.id = t.id )
我现在的情况是:表a有100万条数据,无论有没有对a.id建索引,执行时间都是一样很长。请问,有没有使用到表a的索引?
SET t.name = (SELECT a.name
FROM a
WHERE a.id = t.id )
我现在的情况是:表a有100万条数据,无论有没有对a.id建索引,执行时间都是一样很长。请问,有没有使用到表a的索引?
要你自己看执行计划才知道用没用索引。别人能猜出来用没用?
SET t.name = (SELECT a.name
FROM a
WHERE a.id = t.id )
如果是一对一的更新
加条件语句:
where exists (select 1 from a where a.id=t.id)
t中有100条记录,a中有100万条记录,无论有没有对a.id建索引,执行时间都是30秒。to cosio:好像那样子更慢吧。
create index i_test on a(id,name) compute statistics;
我的内存是这样开的:共享池:400M,缓冲区高速内存:304M,大型池:104M,Java池:104M。
http://topic.csdn.net/u/20080916/14/b42f0028-854d-43ab-803f-eb30f788526f.html
(
select id,name from a
)tbl
where t.id = tbl.id
from
(
select id,name from a
)tbl
where t.id = tbl.id
from testa a, testb b
where a.cellid = b.cellid )
set av = bv;
这样写,索引肯定生效。
SET t.name = (SELECT /*+ index(a index_a)*/a.name
FROM a
WHERE a.id = t.id )
-----------
使用hint进行强制索引试试.
SQL> set autot trace only
SQL> update t
2 SET t.name = (SELECT a.name FROM a WHERE a.id = t.id )
3 /
...
执行计划
----------------------------------------------------------
Plan hash value: ...------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
...
------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
0 recursive calls
8 db block gets
7 consistent gets
1 physical reads
1688 redo size
668 bytes sent via SQL*Net to client
571 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
6 rows processed
麻烦楼主把set autot trace only后,加索引和不加索引的执行结果分别贴上来。