[jboss-user] [Persistence, JBoss/CMP, Hibernate, Database] - declaring an index on a table

[andstall]

I have a very big table of customers, with the primary key being the customer id.
One of the columns is zip, i.e. zip code of the customer location. How do I declare EJB 3.0 persistence annotations that there must be an index on that column?

A very frequent query is get customers by zip. That usually returns less than 10 customers (even no customers at all), sometimes up to 70 and very seldom more than that. I don't think the query should scan the whole table to find the customers in one zip code. 

A possible solution would be to declare a zip entity and declare a one to many relationship between zips and customers.  But this is probably overkill as I have no satellite data associated with the zip, for our app it is only a meaningless number we group customers by. Also this solution would imply yet another table (zip_customer).

Is indeed declaring an index on the zip column the best for me? How do I do it?

View the original post : http://www.jboss.com/index.html?module=bb&op=viewtopic&p=4006233#4006233

Reply to the post : http://www.jboss.com/index.html?module=bb&op=posting&mode=reply&p=4006233