index types and usage

[saxgeek]

From reading the manuals, I gather that order by hash and order by values clauses on indexes help when querying a range of values. Or am I totally off base here? So I have a table with a UPI - one column numeric. A colleague of mine added a NUSI on the same column with the order by hash clause. Doing this seemed to make the queries run faster - but the queries are not doing range scans. They are doing equality joins on the index. Why would adding the second index make them run faster? Or did something else that was done to the queries cause the speedup?

 

[dnoeth]

In that special case the index is almost useless, because the UPI is on the same column and it's also hash ordered. And only "order by values" will help in range scans.

I'd guess that statistics have been collected and the optimizer choose a more efficient plan...

Dieter

 

[saxgeek]

You were right in that the original statistics on the tables were collected before there was any data in them. I made my own copies of the tables etc. I couldn't even create the index with the order by hash clause because teradata recognized that there already was an index on that column.

Anyway, the explain looks a lot better when there are legitimate stats collected on them. Thanks for your help.