| optimizing sql statement [message #21652] |
Wed, 21 August 2002 13:07  |
Son Nguyen
Messages: 8
Registered: July 2002
|
Junior Member |
|
|
Hello,
The Part table in this query has 15 MILLION items, and this query takes FOREVER to run. Is there a BETTER way to write this query? In SQL server I was able to use right joins, etc and it worked faster. But with oracle I do not have that luxary.
Thanks.
SQL STATEMENT:
UPDATE Part SET (Part.MfrName, Part.MfrPartNum) = (SELECT @C2A_CAT.MfrName, @C2A_CAT.MfrPartNum WHERE Part.PartID = @C2A_CAT.PartID) WHERE (PART.PartNum, PART.PartNumExt, PART.UOM) IN (SELECT PartNum, PartNumExt, UOM FROM @C2A_CAT)
|
|
|
|
|
|
| Re: optimizing sql statement [message #21662 is a reply to message #21658] |
Wed, 21 August 2002 15:02  |
Son Nguyen
Messages: 8
Registered: July 2002
|
Junior Member |
|
|
I don't see the syntax in the other sql statement but here is a simple one that does the same thing. I tested it and it works so the syntax is correct. Here is the underlaying problem... I have table A with 5 records, table B with 2 records.... if I run that query WITHOUT the very last WHERE clause it will update 5 records. I need to put in that last where clause so it will only update the records where it MATCHES, or else it sets the rest to NULL. This query takes forever to run, is there a better way to accomplish the same solution?
SQL STATEMENT:
update a set a.description = (select b.description from b where a.partnum = b.partnum)
where a.partnum in (select partnum from b);
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
