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

Attachments

Library

Quick Reference