Home Page for the TeradataForum

Archives of the TeradataForum

Message Posted: Fri, 06 Jul 2007 @ 12:07:30 GMT

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

Subj:   Re: Break Point for using a Sparse Join Index
From:   william.gregg

Thanks for the thoughts Victor.

Unfortunately, things are in a development phase, so there is no query history against which to perform any analysis. I am hoping to hear some thoughts from those that have used Sparse Join Indexes in the field.

It is safe to say that there are queries that will go against these tables searching against the frequently occurring values, only (eg. select ... where attr1 = freq-value.)

I'd like to hear some feedback from the field -- a mix of success stories and what to avoid.

Original post follows:

(In each case assume, a table w/ more than 100M rows.)

1) One value appears on over 70% of the rows, no other value appears on more than 3% of the rows

2) Most frequent value on 40% of the rows, 2 other values on 10% of the rows

3) The top seven values range from 5 to 25% of the rows; together, the Top 7 account for 70% of the rows.

4) Top 2 values hit 20% of the rows each; the balance of the histogram is flat and completely made up by 10K other values

Feel free to add other scenarios or considerations to the items above.

I'm looking for experiences/thoughts regarding SpJI, but approaches using alternative indexing strategies are welcome.


Bill Gregg

  <Prev Next>   <<First <Prev Next> Last>>  
  Top Home Privacy Feedback  
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 28 Jun 2020