Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Tue, 20 Jul 1999 @ 18:51:19 GMT


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


Subj:   Re: OLTP transactions
 
From:   Bob Maccione

Hi John,

This was more of a global question as to whether or not Teradata works in a OLTP environment (or mixed OLAP/TP world). All of the queries that we've tried were index scans, and we are aware of the large limitation of partial index searches (i.e. - where you have an index on last_name, first_name and search only on last_name. The index will be ignored cause it's a hash, not a btree. And yes we are aware of join indexes but haven't tested with them yet.). An example of a query that we're dealing with and the changes we applied is: (names changed to protect my job :-))

     Analyzed Cursor 1
        Example select:
        SELECT a.c_id,
              b.c_desc, b.c_type, a.i_nbr,
              a.d_date (FORMAT 'MM/DD/YYYY'), a.r_id,
              c.c_status, c.c_stat,
              a.d_dt (FORMAT 'MM/DD/YYYY'),
              b.d_ex_dt (FORMAT 'MM/DD/YYYY')
           FROM gst_sent a
           INNER JOIN gst_own b ON b.c_id = a.c_id
           INNER JOIN gst_status c ON c.c_status = a.c_status
           WHERE a.i_fld_1 = 114 AND a.i_fld_2 = 114
              AND a.c_status <> 'P'
           ORDER BY a.d_date DESC;

        Example select took 48 sec.


                Added index (7min): create index (i_fld_1, i_fld_2) on gst_sent;

        Example select took 1-3 sec.


                Added index (4min 38sec): create index (c_status) on gst_sent;

        Example select took 1-3 sec.

< note, the explain says ~.33 seconds per query, and I know that that is just a guesstimate >

< note2, the query runs in < .5 seconds on Informix >

The explain is:

Explanation
--------------------------------------------------
 
  1)First, we lock a distinct XXX."pseudo table" for read on a RowHash to prevent global deadlock for X.a.  
  2)Next, we lock a distinct XXX."pseudo table" for read on a RowHash to prevent global deadlock for X.b.  
  3)We lock a distinct X."pseudo table" for read on a RowHash to prevent global deadlock for X.c.  
  4)We lock X.a for read, we lock X.b for read, and we lock X.c for read.  
  5)We do an all-AMPs RETRIEVE step from X.a by way of index #4 "X.a.i_fld_1 = 114, X.a.i_fld_2 = 114" with a residual condition of ("XB.a.c_status <> 'P'") into Spool 2, which is redistributed by hash code to all AMPs. Then we do a SORT to order Spool 2 by row hash. The size of Spool 2 is estimated with high confidence to be 10 rows. The estimated time for this step is 0.07 seconds.  
  6)We do an all-AMPs JOIN step from X.c by way of a RowHash match scan with no residual conditions, which is joined to Spool 2 (Last Use). X.c and Spool 2 are joined using a merge join, with a join condition of ("X.c.c_status = c_status"). The result goes into Spool 3, which is redistributed by hash code to all AMPs. Then we do a SORT to order Spool 3 by row hash. The size of Spool 3 is estimated with index join confidence to be 10 rows. The estimated time for this step is 0.06 seconds.  
  7)We do an all-AMPs JOIN step from X.b by way of a RowHash match scan with no residual conditions, which is joined to Spool 3 (Last Use). X.b and Spool 3 are joined using a merge join, with a join condition of ("X.b.c_id = c_id"). The result goes into Spool 1, which is built locally on the AMPs. Then we do a SORT to order Spool 1 by the sort key in spool field1. The size of Spool 1 is estimated with index join confidence to be 10 rows. The estimated time for this step is 0.20 seconds.  
  8)Finally, we send out an END TRANSACTION step to all AMPs involved in processing the request.  
  ->The contents of Spool 1 are sent back to the user as the result of statement 1. The total estimated time is 0.33 seconds.  


The same query on our informix database returns:

Estimated Cost: 5
Estimated # of Rows Returned: 1

     1) X.a: INDEX PATH

         Filters: X.a.c_offer_status != 'P'

         (1) Index Keys: i_fld_2 i_fld_2 d_date   (Serial, fragments: 4)
             Lower Index Filter: (X.a.i_fld_1 = 114 AND X.a.i_fld_2 =114 )

     2) Xb: INDEX PATH

         (1) Index Keys: c_id
             Lower Index Filter: X.b.c_id = Xa.c_id
     NESTED LOOP JOIN

     3) Xc: INDEX PATH

         (1) Index Keys: c_status
             Lower Index Filter: Xc.c_status = Xa.c_status
     NESTED LOOP JOIN

an interesting sidenote is that this query was _slower_ under informix if I turned on pdqpriority

Of course the explain engines have 2 totally different ways of telling us that's going on but the purpose of the question/message is to see if anyone out there is really using Teradata for OLTP and what mindset changes were required compared to OLAP.


later,

Bob Maccione



     
  <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