Archives of the TeradataForum

Message Posted: Tue, 09 Aug 2005 @ 15:04:33 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

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

Thanks,

Anonymous

	<Prev	Next>		<<First	<Prev	Next>	Last>>