Archives of the TeradataForum

Message Posted: Fri, 09 Dec 2005 @ 13:40:22 GMT

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

H and O refer to the ALL option in the CREATE INDEX statement. The SQL DDL manual describes the option as follows:

"That a NUSI should retain row ID pointers for each logical row of a join index (as opposed to only the compressed physical rows).

ALL also ignores the NOT CASESPECIFIC attribute of data types. This feature has the benefit of enabling NUSIs to do the following:

- Include case-specific values

- Cover a table or join index on a NOT CASESPECIFIC column

ALL enables a NUSI to cover a join index, enhancing performance by eliminating the need to access that join index when all values needed by a query are present in the secondary index. However, ALL might also require the use of additional index storage space.

Use this keyword when a NUSI is being defined for a join index and you want to make it eligible for the Optimizer to select when covering reduces access plan cost. You can also use the ALL option for an index on a table when that index contains NOT CASESPECIFIC columns.

You cannot specify multiple indexes that differ only by the presence or absence of the ALL option."

Regards,

Victor

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