Archives of the TeradataForum

Message Posted: Thu, 30 Mar 2006 @ 10:43:10 GMT


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

Subj:		Re: Indexes and WinDDI

From:		Praveen_Chakrapani

Hi Naveen,

Can someone help me why the query has IndexType NOT IN ('1','2','M') and what index types do they indicate ?

Index type '1','2' refers columns selected in a join index. For each column selected in the join index it has an entry in indexes table with index type 1 or 2.

'M' refers Multi-Column statistics collected for set of columns in a table. These are not indexes so it's ignored while listing the indexes in a table.

Also what are these Values Order (All) and Secondary ( All) indexes?

If a Value order or Secondary index is created with ALL option then it's stored with index type 'O' or 'H'

What is the use of storing Column Position in indices table and what is the benefit of giving indexes a name, we never give index a name and we dont see any problem with that.

If multi-column index is created with a name then it's easy to collect stats or drop index by just mentioning it's name instead of giving all the columns names in the index.

I am not sure about the need for column position. I hope it will be helpful for optimizer when it uses the stats for a Multi-column index. May be other experts will answer for you :)

Hope this helps U.

Thanks,

Praveen


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

Attachments

Library

Quick Reference