Archives of the TeradataForum

Message Posted: Fri, 18 Sep 2009 @ 13:15:20 GMT

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

Recently I raised the question about the design we had to support the following Query, particularly wrt scalability :

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

First of all, a big thank you to VICTOR SOKOVIN for his comments, which helped in our discussions here at TNT. Sometimes an independent opinion can be very useful.

For the possible design interest of the Forum, however, this is what we decided to do ...

At the time, we had two columns on one row, the ACC_ID_SNDR and ACC_ID_RCVR. The former was supported by a NUPI, the latter by a NUSI.

The Explain was impressive in that both the NUPI and the NUSI was employed to satisfy the above query. Performance was good - very good.

We changed that design slightly, however, by adding another column ACC_ID_SEARCH which would contain either the ACC_ID_SNDR or ACC_ID_RCVR. The necessity, therefore, was to accept having two rows rather than one, at least on those entries where both the Account number were non- blank.

The benefit in this modified design is that we then only need to reference the Primary Index (essentially free use of Teradata) instead of the Primary Index AND the NUSI.

Scalability assured.

We eliminated duplicates (where they occurred) by a simple Olap function.

Hope that provides food for thought for those involved in database design.

Regards

David Clough
Database Developer
Database Design Group

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