Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Mon, 07 Jan 2002 @ 19:35:25 GMT


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


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 :

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



     
  <Prev Next>  
<<First
<Prev
Next> Last>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 27 Dec 2016