Archives of the TeradataForum

Message Posted: Wed, 02 Sep 2009 @ 17:05:45 GMT


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

Subj:		All Amps usage

From:		David Clough

I've got a Fact table and we're searching for entries on this table by either the Sender Account number of the Receiver Account number (just to make sure we don't miss the entries on the Fact table).

The (simplified) query looks something like this :

     EXPLAIN SELECT *
     FROM PRODCSSR_P.CON_CSSR_SIMULATION_V01
     WHERE ACC_ID_SNDR = '000136943'
     OR ACC_ID_RCVR = '000136943'

I've got the table with a non-Unique Primary Index on ACC_ID_SNDR and a non-Unique Secondary Index on ACC_ID_RCVR.

Here's the explain plan, which I thought on first inspection was extremely good :

`Explanation --------------------------------------------------`
	`1)`	`First, we lock a distinct PRODCSSR_T."pseudo table" for read on a RowHash to prevent global deadlock for PRODCSSR_T.CON_CSSR_SIMULATION.`
	`2)`	`Next, we lock PRODCSSR_T.CON_CSSR_SIMULATION for read.`
	`3)`	`We do an all-AMPs RETRIEVE step from PRODCSSR_T.CON_CSSR_SIMULATION by way of index # 4 "PRODCSSR_T.CON_CSSR_SIMULATION.ACC_ID_RCVR = '000136943'" extracting row ids only with no residual conditions into Spool 2 (all_amps), which is built locally on the AMPs. The size of Spool 2 is estimated to be 22 rows. The estimated time for this step is 0.01 seconds.`
	`4)`	`We do a single-AMP RETRIEVE step from PRODCSSR_T.CON_CSSR_SIMULATION by way of the primary index "PRODCSSR_T.CON_CSSR_SIMULATION.ACC_ID_SNDR = '000136943'" extracting row ids only with no residual conditions into Spool 2 (all_amps), which is built locally on that AMP. The size of Spool 2 is estimated to be 133 rows. The estimated time for this step is 0.05 seconds.`
	`5)`	`We do a SORT to order Spool 2 by row id eliminating duplicate rows. The estimated time for this step is 0.00 seconds.`
	`6)`	`We do an all-AMPs RETRIEVE step from PRODCSSR_T.CON_CSSR_SIMULATION by way of row ids from Spool 2 (Last Use) with no residual conditions into Spool 1 (group_amps), which is built locally on the AMPs. The input table will not be cached in memory, but it is eligible for synchronized scanning. The size of Spool 1 is estimated with high confidence to be 133 rows. The estimated time for this step is 0.08 seconds.`
	`7)`	`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.13 seconds.`

Someone, however, has cast doubt on the scalability of this Explain, on the grounds that it has an ALL AMPS portion to it.

Effectively, we have a Primary Index use and a Secondary Index use, but the latter using All Amps.

Oh, I should point out that I think there will be a bit of skew on the Primary Index (Skew Factor 40) and the Secondary Index is likely to be similar.

Do you guys reckon this is Scalable or does this make you want to take a sharp intake of breath ?

Oh, I should also point out that we do consider this to be a Tactical Query. I know technically that to qualify as 'tactical' it must be a Primary Index hit but, either way, we need it to return fast (which it does).

Also, we'll be moving from probably a hundred users to about a thousand users online.

Currently, we're working with around 100 Amps but this figure is likely to grow quite a bit.

I've got ideas for changing the structure if need be, but I'd rather not if I can get away with it, as it's working well currently as it is.

Regards

David Clough
Database Developer
Database Design Group


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

Attachments

Library

Quick Reference