Index selection

Database
Enthusiast

Index selection

We are told that Transaction factors affect index selection within Teradata. Could someone please expalin how the following affects index selection.

•How are transactions written?

•How are transactions parcelled?

•What levels and types of locking does a transaction require?

•How long does the transaction hold locks?

This is the link to the teradata doc's

http://www.info.teradata.com/htmlpubs/DB_TTU_14_10/index.html#page/Database_Management/B035_1094_112...

Thanks

4 REPLIES
Enthusiast

Re: Index selection

Index selection mostly based on the following factors

1. Proper data distribution and retrieval with in databases

2. Avoid skewness

3. Business usage of the selected column

4. Usage of the selected index column in other processing like join clauses, where conditions etc..

Enthusiast

Re: Index selection

Yes it does but the Teradata documentation states many more reasons and I would like additional information on there stated case which I have presented above. Anybody got any ideas ? I was thinking that prehaps the locks have something to do with how transactions are written and that affects the PI ?

Teradata Employee

Re: Index selection

Locks on indexes are separate from locks on the underlying rows. The referred to factors above are about secondary and join index selection rather than primary index selection. If a transaction updates many individual rows and each of those place locks on indexes, many transactions can be blocked or even deadlocked. This does not apply to bulk operations where a table lock is obtained anyway - just to single row or PACKed sets of insert, update or delete operations.

Enthusiast

Re: Index selection

Many thanks Todd !