Insert into using concantenated index

I have two tables:
SourceTable
-----------------
Name
ExpDate
Type
OtherStuff

MasterTable
-----------------
Name
ExpDate
Type

We would like to take the info from SourceTable to Master Table where the incoming three fields do not exist in Master.
We placed a concantenated index on Master for these fields thinking it would be faster somehow.  But it looks like we are forced to do something like:
Insert into MasterTable.....
where
 (Name || ExpDate || Type)  
Not in
(select  (Name || ExpDate || Type)  from MasterTable)

Seems like the index is really useless.
What's the fastest way to accomplish this?
GNOVAKAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
Given the information provided, I would start out with:

insert into master
select name, expdate, type from source
minus
select name, expdate, type from master
/
0
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
Probably better, use MERGE:

merge into master mas
using (
	select name, expdate, type from source
) src
on (
	src.name = mas.name and
	src.expdate = mas.expdate and
	src.type = mas.type
)
when not matched then
	insert (mas.name, mas.expdate, mas.type) values (src.name, src.expdate, src.type)
;

Open in new window

0
 
GNOVAKAuthor Commented:
Thanks ! Great examples.
0
 
awking00Commented:
Indexes improve performance when data is being retrieved. So, to do the inserts you should have an index on the source table. And there is no need for a concatenated index, just use a compound index. For example, create index src_idx on sourcetable(name, expdate, type). Any index on the mastertable would assist in query performance but actually reduce performance on the inserts. It is fairly normal to disable indexes on target tables during inserts and rebuilding them after inserts are completed.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.