Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Fri, 28 May 2004 @ 08:00:02 GMT


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


Subj:   Re: Access Lock with PI Selection
 
From:   Dieter Noeth

Anomy Anom wrote:

  So does anyone know of a way to ensure an Access lock when selecting on PI through a view that locks row for access? I do realize that we could just add the locking modifier to the users' PI select statement, but expecting that of our developers all the time is unrealistic. I had hoped to handle this completely through the view layer.  


  An explain without PI select shows:  


     1) First, we lock Prod_Tables.Summ_Customer for access.
     2) Next, we do an all-AMPs RETRIEVE step from...........
  An explain with PI select skips step 1 above:  


     1) First, we do a single-AMP JOIN step from Prod_Tables.Summ_Customer
        by way of the unique primary index

You dropped the interesting part:

     create view qwert as lock row access sel * from au.trans;

     sel a.account_number, count(*)
     from qwert t join au.accounts a
     on a.account_number = t.account_number
     where t.account_number = 1
     group by 1;
       
  1)First, we do a single-AMP JOIN step from au.a by way of the unique primary index "au.a.account_number = 1" with no residual conditions, which is joined to au.trans by way of the primary index "au.trans2.account_number = 1" with no residual conditions locking row of au.a for access and row of au.trans for access. au.a and au.trans2 are joined using a merge join, with a join condition of ("au.a.account_number = au.trans.account_number").  


In V2R5 this is the expected plan, can't remember if it was different in V2R4. So what's your release?


Dieter



     
  <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