Archives of the TeradataForum

Message Posted: Thu, 17 May 2012 @ 19:41:49 GMT

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

If you create a composite secondary index, the rows will be ordered by the hash of all of the columns in the index. But if you choose the "order by hash" option you can order the subtable rows by just the row hash of the single column. This essentially vertically partitions your table and the index would then be used to cover your query.

For example, if you code a SQL statement like

     Select divn_nbr from proddimid where end_dt = value;

The optimizer can use the index to access the subtable by hashing the date value supplied and get the divn_nbr value from the subtable without accessing the base table. If you did not use the "order by hash" option, then the optimizer could still use the index subtable to cover the query it would just have to traverse the subtable which would take a little longer.

An alternative to creating a hash ordered index is a single table join index, which many times would be a better choice since you can also sparse that index by adding a where clause. This allows you to only build the index subtable on a subset of the data, which is typically the hotter more frequently accessed data.

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