 |
 |
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.
| |
