November 29, 2006 at 12:19 am
November 29, 2006 at 12:27 am
Assuming that f1 and f2 are fields in table1 and that the in works the same way in oracle as sql...
Select * from table1 inner join table2 on table1.f1=table2.f1 and table1.f2=table2.f2
HTH
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 29, 2006 at 12:59 am
November 29, 2006 at 1:27 am
in mssql also its "NOT IN" but it can be used only for a single field.
select * from table1 where (f1) not in (select f1 from table2 )
"Keep Trying"
November 29, 2006 at 1:53 am
It looks a little wierd, but this is what you want. A not in for 2 fields.
Select table1.* from table1 LEFT OUTER join table2 on table1.f1=table2.f1 and table1.f2=table2.f2
WHERE Table2.f1 is null and table2.f2 is null
This is an alternative that will also work
Select * from table1 WHERE NOT EXISTS (Select 1 FROM table2 where table1.f1=table2.f1 and table1.f2=table2.f2)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 29, 2006 at 3:47 am
there is no merge statement in ms sql server, i have a merge statement like this (see below). Is there a (new) statement which works like merge of oracle sql.
MERGE INTO table2 B
USING (
SELECT f1, f2
FROM table1
WHERE f1 =20) A
ON (B.f1 = A.f1)
WHEN MATCHED THEN
UPDATE SET B.f1 = A.f2 * 0.1
WHEN NOT MATCHED THEN
INSERT (B.f1, B.f2)
VALUES (A.f1, A.f2 * 0.05);
November 29, 2006 at 4:03 am
No merge statement. You'll have to write seperate update and insert statements. Something like this
Update table2 set f1 = table1.f2*0.1
FROM table1 where table2.f1=table1.f1 and table1.f1=20
INSERT Into table2 (f1, f2)
SELECT f1, f2*0.05 FROM table1 where not exists (select 1 from table1 where table2.f1=table1.f1 and table2.f2=table1.f1 and f1=20)
those should be equivalent, but they are untested and I'm not completely familiar with how oracle's merge works
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 29, 2006 at 4:47 am
thanks!
Oracle merge inserts or updates the rows into the table2 (i.e. merge table) based on the equijoin condition (i.e. (B.f1 = A.f1))
------------------------------------------------------------
I have 2 tables (more than 10 million rows each).
table1 has 3 columns
a - datatype - varchar, b - varchar, c - float
there is no primary key or index
there is data in column a,b & all the rows of column c have null value
table2 has same 3 columns
a - varchar, b - varchar, c - varchar
there is no primary key or index
there is data in column a,b,c...
I have a simple procedure to put the c value of table2 into c value of table1.
The procedure looks like this
------------------------------------------------------------
create procedure my_procedure as
begin
declare @a nvarchar(255),@b nvarchar(255)
declare @C nvarchar(255)
declare c1 cursor dynamic
for select a,b,c from table1
open c1
fetch next from c1 into @a,@b,@c
while(@@fetch_status = 0)
begin
update table2
set c = @C
where a = @a and
b = @b-2
print @C
print @a
fetch next from c1 into @a,@b,@c
if @@error <> 0
print 'exception 1 ' + @@error
end
close c1
deallocate c1
end
------------------------------------------------------------
It takes arround 4 hrs to execute this procedure, is there a way to optimize this procedure code? (I dont have the rights to add keys to table or index..etc. only code changes) Its actually data transfer of column c from table1 to table2
November 29, 2006 at 5:02 am
Absolutely, yes. Cursors are massively inefficient.
Again this is untested, but I think it should do the same as the cursor. I'd be very interested in how long it runs.
UPDATE
table2 SET c = CAST(table1.c AS VARCHAR(255))
FROM table1 WHERE table1.a = table2.a and table1.b=table2.b
btw, if you have tables with 10 million + rows with no indexes, you're going to have terrible performance. Is there anyone you can speak to about putting indexes on these tables? Not for this, but for all your other queries
Also, you said about copying the values of column c from table2 into table1, but your code has it the other way round.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 29, 2006 at 6:32 am
November 29, 2006 at 6:48 am
No bulk update, and I would hazard a guess that it's not the logging that's slowing your proc down.
Is there any chance of getting someone to add an index? on a and b in both tables should really help.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 30, 2006 at 7:13 am
Assuming that f1 and f2 are varchar, the most direct equivalent in SQL Server would be:
select * from table1 where f1+f2 in (select f1+f2 from table 2)
December 3, 2006 at 10:57 pm
That works, the problem with it is that it prevents any index usage, if you have an index on f1 or f2.
Reason been it's seen as a function on a column, which instantly makes the expression non-SARGable
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply