Hi,

I have the following problem
The update is very long (more than 4 hours)
table T_SUBJECTVISITFORMITEMS is 3M rows

How can I do that more quickly ?

Thanks in advance

CREATE TABLE T_ITEMFIRSTDATA_STATUS_ICLD
AS
SELECT
MIN (ITEMFIRSTDATA) ITEMFIRSTDATA ,
SUBJECTID,
VISITID,
DB_SOURCE
FROM T_SUBJECTVISITFORMITEMS
WHERE FORMMNEMONIC = 'STATU'
GROUP BY SUBJECTID,VISITID,FORMID,DB_SOURCE;

ALTER TABLE T_SUBJECTVISITFORMITEMS ADD ITEMFIRSTDATA_STATU DATE;

UPDATE T_SUBJECTVISITFORMITEMS B SET ITEMFIRSTDATA_STATU =
(SELECT DISTINCT ITEMFIRSTDATA
FROM T_ITEMFIRSTDATA_STATUS_ICLD A
WHERE A.SUBJECTID=B.SUBJECTID
AND A.VISITID=B.VISITID
AND A.DB_SOURCE= B.DB_SOURCE);

On Dec 3, 11:11*pm, "bob123" <nom... (AT) nowhere (DOT) com> wrote:
Try posting the plan. The distinct perhaps means a full table scan
of ...icld for each row of ...formitems. Would you happen to have any
indices? Does your cpu go nuts? Does statspack have something to say
about what is going on in that time period?

jg
--
@home.com is bogus.
http://www.signonsandiego.com/news/2...chnology-fund/

bob123 schreef:
There's something fishy about the above: in the create table you group by
"SUBJECTID,VISITID,FORMID,DB_SOURCE", but when querying, you only specify
SUBJECTID,VISITID and DB_SOURCE. So I would expect errors during the update
(select may return more than one row).

If you want to speed this up, don't use T_ITEMFIRSTDATA_STATUS_ICLD. Write a
procedure so the update can be spread over multiple transactions. Put an index
on SUBJECTID,VISITID,FORMID,DB_SOURCE.

joel garry schreef:

?????

perhaps?? indices?? He's just created the damned table, and, God forbid,
Oracle does not yet create indexes on its own initiative.

maybe I can avoid this with a CTAS ?

below the plan:
Plan
UPDATE STATEMENT ALL_ROWS Cost: 39,539 Bytes: 68,254,407 Cardinality:
2,527,941
5 UPDATE SYSTEM.T_SUBJECTVISITFORMITEMS
1 TABLE ACCESS FULL TABLE SYSTEM.T_SUBJECTVISITFORMITEMS Cost: 39,539
Bytes: 68,254,407 Cardinality: 2,527,941
4 HASH UNIQUE Cost: 3 Bytes: 27 Cardinality: 1
3 TABLE ACCESS BY INDEX ROWID TABLE SYSTEM.T_ITEMFIRSTDATA_STATUS_ICLD
Cost: 2 Bytes: 27 Cardinality: 1
2 INDEX RANGE SCAN INDEX SYSTEM.T_ITEMFIRSTDATA_STATUS Cost: 1
Cardinality: 1

bob123 schreef:
Wow, my mistake, Oracle does create indexes on its own.

On Dec 5, 3:50*am, "Gerard H. Pille" <g... (AT) skynet (DOT) be> wrote:
lol!

bob123, Girard correctly pointed out the fishiness, you have to have
that distinct in your select, which causes the range scan of the
index. You may have a design problem, not being selective enough in
what data you are updating with, so as to get it more directly. I'm
not thinking too clearly yet this Monday morning, but I think a
correct unique update would be faster than a CTAS or multiple runs,
though if I'm not interpreting it correctly Girard's answer may be
better. And of course, Oracle may come up with something better with
a different query. You also want to post predicate information with a
plan, see http://jonathanlewis.wordpress.com/2...cate-problems/
for an example.

Version information (select banner from v$version) is very often
important too.

jg
--
@home.com is bogus.
http://www.bnet.com/blog/technology-...gotiation/7172