Hi,
Suppose I have a table with 4 fields (f1, f2, f3, f4)
I define 2 unique indexes u1 (f1, f2, f3) and u2 (f1, f2, f4)
I have 3 records
A, B, C, D (this will be inserted)
A, B, C, E (this will pass u2, but not u1, thus not inserted)
A, B, F, D (this will pass u1, but not u2, thus not inserted)
Now, for performance ...
I have tables like this with 500.000 records where there's a new
upload of approx. 20.000 records.
It is only now that we say index u2 to be necessary. So, until now, I
did something like insert into ... select f1, f2, f2, max(f4) group by
f1, f2, f3
That is ok ... and also logically ok because of the data definition
I cannot do this with 2 group by's. I tried this on paper and I'm not
succeeding.
So, I must use a function that will check against u1 and u2, and then
insert if it is ok.
I know that such a function is way slower that my insert query.
So, my question ...
How can I keep the same performance, but also with the new index in
mind ???
Met vriendelijke groeten,
Bien à vous,
Kind regards,
<bold>Yves Vindevogel</bold>
<bold>Implements</bold>
<smaller>
</smaller>Hi,
Suppose I have a table with 4 fields (f1, f2, f3, f4)
I define 2 unique indexes u1 (f1, f2, f3) and u2 (f1, f2, f4)
I have 3 records
A, B, C, D (this will be inserted)
A, B, C, E (this will pass u2, but not u1, thus not inserted)
A, B, F, D (this will pass u1, but not u2, thus not inserted)
Now, for performance ...
I have tables like this with 500.000 records where there's a new upload
of approx. 20.000 records.
It is only now that we say index u2 to be necessary. So, until now, I
did something like insert into ... select f1, f2, f2, max(f4) group by
f1, f2, f3
That is ok ... and also logically ok because of the data definition
I cannot do this with 2 group by's. I tried this on paper and I'm not
succeeding.
So, I must use a function that will check against u1 and u2, and then
insert if it is ok.
I know that such a function is way slower that my insert query.
So, my question ...
How can I keep the same performance, but also with the new index in
mind ???
Met vriendelijke groeten,
Bien à vous,
Kind regards,
Yves Vindevogel
Implements
<smaller>
Mail: yves.vindevogel@implements.be - Mobile: +32 (478) 80 82 91
Kempische Steenweg 206 - 3500 Hasselt - Tel-Fax: +32 (11) 43 55 76
Web: http://www.implements.be
<italic><x-tad-smaller>
First they ignore you. Then they laugh at you. Then they fight you.
Then you win.
Mahatma Ghandi.</x-tad-smaller></italic></smaller>
Mail: yves.vindevogel@implements.be - Mobile: +32 (478) 80 82 91
Kempische Steenweg 206 - 3500 Hasselt - Tel-Fax: +32 (11) 43 55 76
Web: http://www.implements.be
First they ignore you. Then they laugh at you. Then they fight you.
Then you win.
Mahatma Ghandi.
