 |
 |
Archives of the TeradataForum
Message Posted: Mon, 07 Jan 2002 @ 19:35:25 GMT
| Subj: | | Block everybody with ALTER TABLE |
| |
| From: | | Rudel Simard |
Hi,
I have a table (say TABLE1) with 340 000 000 rows (50 giga without NUSI). I try to drop 4 columns and add 1 with the same ALTER TABLE
command. No views are referencing to the remove columns.
If I execute :
ALTER TABLE SYSDBA.TABLES1DROP COD_POSTL_BEN,
DROP COLUMN1,
DROP COLUMN2,
DROP COLUMN3,
DROP COLUMN4,
ADD COLUMN5 BYTEINT COMPRESS;
All job already running, still run.
But, all new job comming to the system are all block until the end og the ALTER. All those jobs are not using the altered table.
Does anyboby know why all job are block?
My feeling is DBC.AccessRights use index # 4, this index NUSI. The system block the table to update when it's use a NUSI. All other
table of DBC use USI and are rowhash block. Why TERADATA are not blocking DBC.AccessRights for the step 5-15 only?
Here the explain :
Explanation
-------------------------------------------------- | | 1) First, we lock a distinct SYSDBA."pseudo table" for exclusive use on a RowHash to prevent global deadlock for SYSDBA.TABLE1
2) Next, we lock SYSDBA.TABLE for exclusive use.
3) We lock a distinct DBC."pseudo table" for write on a RowHash to prevent global deadlock for DBC.AccessRights.
4) We lock DBC.AccessRights for write, we lock DBC.TVFields for write on a RowHash, we lock DBC.TVM for write on a RowHash, and we lock
DBC.Indexes for write on a RowHash.
5) We execute the following steps in parallel.
- 1) We do a single-AMP ABORT test from DBC.TVM by way of the unique primary index with no residual conditions.
- 2) We do a single-AMP ABORT test from DBC.Indexes by way of the primary index.
- 3) We do a single-AMP ABORT test from DBC.ReferencingTbls by way of the primary index.
- 4) We do a single-AMP DELETE from DBC.TVFields by way of the primary index.
- 5) We do a single-AMP ABORT test from DBC.Indexes by way of the primary index.
- 6) We do a single-AMP ABORT test from DBC.ReferencingTbls by way of the primary index.
- 7) We do a single-AMP DELETE from DBC.TVFields by way of the primary index.
| | |
| | 8) | We do a single-AMP ABORT test from DBC.Indexes by way of the primary index.
| | |
| | |
| | 9) | We do a single-AMP ABORT test from DBC.ReferencingTbls by way of the primary index.
| | |
| | |
| | 10) | We do a single-AMP DELETE from DBC.TVFields by way of the primary index.
| | |
| | |
| | 11) | We do a single-AMP ABORT test from DBC.Indexes by way of the primary index.
| | |
| | |
| | 12) | We do a single-AMP ABORT test from DBC.ReferencingTbls by way of the primary index.
| | |
| | |
| | 13) | We do a single-AMP DELETE from DBC.TVFields by way of the primary index.
| | |
| | |
| | 14) | We do an INSERT into DBC.TVFields.
| | |
| | |
| | 15) | We do an all-AMPs DELETE from DBC.AccessRights by way of index # 4.
| | |
| | |
| | 16) | We do a single-AMP UPDATE from DBC.TVM by way of the unique primary index with no residual conditions.
| | |
| | |
| | 17) | We do a single-AMP UPDATE from DBC.DBCAssociation by way of the unique primary index with no residual conditions.
| | |
| | 6) | We modify the table header.
| |
| | 7) | We spoil the parser's dictionary cache for the table.
| |
| | 8) | Finally, we send out an END TRANSACTION step to all AMPs involved in processing the request.
| |
| | -> | No rows are returned to the user as the result of statement 1.
| |
Rudel Simard
R�gie de l'assurance-maladie du Qu�bec
DBA de l'environnement informationnel
| |
