1 Reply Latest reply on Feb 16, 2007 9:43 PM by Bas Ven

    declaring an index on a table

    Andy Worms Newbie

      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?