 |
 |
Archives of the TeradataForum
Message Posted: Tue, 18 Feb 2003 @ 19:32:39 GMT
| Subj: | | Re: INSERT/SELECT out of one table into two |
| |
| From: | | Terry Stover |
When you are inserting into an empty table with the same PI the optimizer is smart enough to realize it doesn't need to do a dupe row
check. Same thing is supposed to happen when your target table has a subset of the source table PI columns (we used to make 2 copies of an
end of month reporting table, the PI's were optimized for different sets of reports). We found a bug on that feature during 4.1.3
regression testing. The optimization may not be very robust, maybe the multistatement request screws it up. Does it work on each statement
independently?
For performance I'd recommend changing the where criteria. The CAST(SUBSTR(EXTRACT_TS, 1, 10) AS DATE) < '2003-01-10' will have to do a
calculation on every row to find the qualifying records. Try converting the '2003-01-10' to a timestamp constant instead (I'm assuming
EXTRACT_TS is a timestamp), no point in wasting cpu. I've had 20x performance improvement for similar situations (where the constraint was
= / IN not inequality), but those also had other where clause contraints.
| |
