Archives of the TeradataForum
Message Posted: Tue, 20 Jul 1999 @ 18:51:19 GMT
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:
The same query on our informix database returns:
Estimated Cost: 5
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.
|Copyright 2016 - All Rights Reserved|
|Last Modified: 28 Jun 2020|