Details

    • Type: New Feature New Feature
    • Status: Resolved
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: None
    • Labels:
      None

      Description

      Currently Indices are only provided via @UniqueConstraint. The main downside of this annotation is that you need to specify the database column names. This is often broken and not easy to maintain.
      Another problem: there is atm no way to explicitly define a name for an Index. Which means the database will generate one and you have no way to properly change it later. There is also no easy way to detect any unused Indices.

      Here are a few requirements I would have for such a system:

      • typesafety - using column names or field names is not really refactoring friendly
      • allow naming an Index
      • index sorting. CostBased optimizers are nowadays pretty good already, but there is still some need for explicitly defining the order of the columns in an index. wdyt?
      • allow multiple indices on an entity.
      • support unique/sorting type indices
      • if it fits we could also think about logical-key support

        Activity

        Hide
        Mark Struberg added a comment - - edited

        Here is a first try on this topic. The idea is to use @Index on single fields but also as a meta-annotation for creating own 'index-groups'

        package javax.persistence;
        public @interface Index {
          String name() default ""; // the name of the index
          int value() default Integer.MAX_VALUE; // for ordering the index if no group annotation is used
          boolean unique() default false; 
          // add asc, desc etc and other stuff as you like
        }
        

        This can be used in your own project the following way:

        @Entity
        public class MyUser {
        
          // first we define our index group annotations 
          
          @Index(unique=true, name="I_LOGIN")  // @Index meta-information to show that this is an index group
          private @interface LoginIdx {
            int value() default Integer.MAX_VALUE; // optional for @Index groups which need ordering inside the Index  
          }
        
          @Index
          private @interface CarLookupIdx {
            // if no int value() is specified in an index, the order is undefined
          }
        
        
          // and now we can use them on the fields
          @LoginIdx(2);
          private String tenant;
        
          @LoginIdx(1);
          private String userLogin;
         
          @CarLookupIdx
          private String carVendor;
         
          @CarLookupIdx
          private String carType;
        }
        

        TODO: we can also add a tblName to the index to allow reusing an index grp (e.g. @SearchIdx) in multiple entities without having the index names clash.

        The same trick could be use to define a default @LogicalKey index group. Just an idea so far...

        Show
        Mark Struberg added a comment - - edited Here is a first try on this topic. The idea is to use @Index on single fields but also as a meta-annotation for creating own 'index-groups' package javax.persistence; public @ interface Index { String name() default ""; // the name of the index int value() default Integer .MAX_VALUE; // for ordering the index if no group annotation is used boolean unique() default false ; // add asc, desc etc and other stuff as you like } This can be used in your own project the following way: @Entity public class MyUser { // first we define our index group annotations @Index(unique= true , name= "I_LOGIN" ) // @Index meta-information to show that this is an index group private @ interface LoginIdx { int value() default Integer .MAX_VALUE; // optional for @Index groups which need ordering inside the Index } @Index private @ interface CarLookupIdx { // if no int value() is specified in an index, the order is undefined } // and now we can use them on the fields @LoginIdx(2); private String tenant; @LoginIdx(1); private String userLogin; @CarLookupIdx private String carVendor; @CarLookupIdx private String carType; } TODO: we can also add a tblName to the index to allow reusing an index grp (e.g. @SearchIdx) in multiple entities without having the index names clash. The same trick could be use to define a default @LogicalKey index group. Just an idea so far...
        Hide
        mkeith added a comment -

        Indexing has been included in the latest schema generation proposal sent to the list. Can you please make any comments on that proposal that you feel should be considered? Thanks.

        Show
        mkeith added a comment - Indexing has been included in the latest schema generation proposal sent to the list. Can you please make any comments on that proposal that you feel should be considered? Thanks.
        Hide
        Mark Struberg added a comment -

        I've seen the mail you mention and sent a long mail back to the experts and users list on may 9th. Obviously it didn't come through thus I decided to create a bug report instead.

        Actually I do not care about how to solve this, but having it purely based on some string logic is just not state of the art anymore imo.

        PS: regarding the original mail: I do think that is is NOT possible to create the schema updates at runtime because of many reasons. I've never seen one single project which does this in production. And for good reasons!

        • additional tables for non-jpa use (e.g. accessed via nativeQuery or even JDBC)
        • additional indices for performance tuning
        • sometimes even non-portable indices (Oracle Text Search)
        • doing manual statistic runs or enabling automatically scheduled statistic runs
        • setting up tons of other stuff: triggers, stored procedures, db replication to other nodes, etc
        • where do you get the required db privileges from? In my projects the app server surely does NOT have any privileges to create tables (not to think about extents, etc)
        • it's just too easy to break your db and f*** up your whole data.
        • DB Admins are paranoid when it comes to their data - and I personally like that a lot! I've seen way too many messed up databases the last 25 years.
        Show
        Mark Struberg added a comment - I've seen the mail you mention and sent a long mail back to the experts and users list on may 9th. Obviously it didn't come through thus I decided to create a bug report instead. Actually I do not care about how to solve this, but having it purely based on some string logic is just not state of the art anymore imo. PS: regarding the original mail: I do think that is is NOT possible to create the schema updates at runtime because of many reasons. I've never seen one single project which does this in production. And for good reasons! additional tables for non-jpa use (e.g. accessed via nativeQuery or even JDBC) additional indices for performance tuning sometimes even non-portable indices (Oracle Text Search) doing manual statistic runs or enabling automatically scheduled statistic runs setting up tons of other stuff: triggers, stored procedures, db replication to other nodes, etc where do you get the required db privileges from? In my projects the app server surely does NOT have any privileges to create tables (not to think about extents, etc) it's just too easy to break your db and f*** up your whole data. DB Admins are paranoid when it comes to their data - and I personally like that a lot! I've seen way too many messed up databases the last 25 years.
        Hide
        ldemichiel added a comment -

        Index support has been added

        Show
        ldemichiel added a comment - Index support has been added

          People

          • Assignee:
            Unassigned
            Reporter:
            Mark Struberg
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: