Archives of the TeradataForum
Message Posted: Tue, 09 Aug 2005 @ 15:04:33 GMT
<-- Anonymously Posted: Tuesday, August 09, 2005 10:58 -->
Folks are complaining that in our DEV machine, with 1 node and 7 AMPS, running RDBMS V2.5, certain UPDATE and DELETE operations are taking way, way, way too long to complete.
I traced it to tables with UNIQUE SECONDARY INDEXES in their definition, so I conducted a quick test to confirm my suspicion that the USI was the culprit.
I created two test tables by making a copy from the same base table and populated both with the same 1,000,000 rows each.
Table A was defined with a UNIQUE PRIMARY INDEX.
Table B was defined with a PRIMARY INDEX and a UNIQUE SECONDARY INDEX.
First test was to update one of the COLUMNS not in the PI or USI to have a constant value of 'DELETE'.
Both tables A and B performed the 1,000,000 row update in almost the same amount of time.
Next test was to submit, at the same time and in separate sessions, two delete statements:
DELETE FROM TABLE A WHERE Col1 = 'DELETE'; DELETE FROM TABLE B WHERE Col1 = 'DELETE';
Table A with the UPI removed all 1,000,000 rows in 22 seconds.
Session resources used: 7 AMP CPU and 17,569 AMP IO.
Table B with the PI and USI was KILLED after 55 minutes of run time (call me impatient).
Session resources at time of death: 380 AMP CPU and 2,808, 208 AMP IO.
I repeated the same experiment in our PROD box with the same results, but this time I allowed the DELETE on table B to finish.
Table A with the UPI removed all 1,000,000 rows in 1 second.
Session resources used: 14 AMP CPU and 17,404 AMP IO).
Table B with the PI and USI removed all 1,000,000 rows in 3:24.
Session resources used: 1111 AMP CPU and 5,295,926 AMP IO
So barring some DBS Control setting (like the Transitive Property discussed earlier); it appears USI's are the kiss of death for bulk maintenance.
Any comments or suggestions are welcome (besides dropping USIs for maintenance)
|Copyright 2016 - All Rights Reserved|
|Last Modified: 28 Jun 2020|