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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
ASKER