Hi Linda!
I finally found some time (and free brain) for answering the 2nd part of the mail (the index story).
Answers inline.
txs and LieGrue,
strub
>________________________________
> From: Linda DeMichiel <linda.demichiel_at_oracle.com>
>To: struberg_at_yahoo.de
>Sent: Monday, May 21, 2012 10:01 PM
>Subject: Re: [jpa-spec users] [jsr338-experts] Re: JPA schema generation
>
>>
>> Regarding the Indexes. I'd rather have a typesafe @Index nnotation
>> which allows for own typesafe index groups.
>> Just a very first idea:
>>
>> public @interface 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
>> }
>>
>
>Not sure I'm following. I was proposing that if the index included multiple
>columns that the @Indexes annotation would need to be applied to the class.
[strub]
I'm not exactly a fan of any @Indexes annotation on the class itself, because we have such a thingy already (@UniqueContraints). The reason why I don't really like it that it's a.) not easy to maintain, b.) not typesafe (they are just random strings) c.) hard to refactor and d.) hard to detect unused indices.
We all learned a few tricks while doing this annotation stuff all the years (especially while working on CDI) and meta-annoations would allow us to do all this in a type-safe way. I've wrote up my ideas in a
http://java.net/jira/browse/JPA_SPEC-22 including a sample of how this could probably look like.
The basic idea is to create an inner-class Annotation for your multi-column Index and annotate your columns with it.
I'm not sure if the sample in the issue is self-explaining. And it certainly takes some time to get familiar with this kind of style. Any feedback is highly appreciated!
I'm not saying this is THE way, but rather like to showcase other options we have and trigger some brainstorming. So keep your ideas rolling, folks ;)
>> @Entity
>> public class MyUser {
>>
>> // @Index meta-information to show that this is an index group
>> private @Index(unique=true)@interface LoginId {
>> int value() default Integer.MAX_VALUE; // mandatory for @Index
>> groups: for ordering inside the grp
>> }
>> private @Index_at_interface CarLookup {
>> // if no int value() is specified in an index, the order is
>> undefined
>> }
>>
>> @LoginId(1);
>> private String tenant;
>>
>> @LoginId(2);
>> private String userLogin;
>>
>> @CarLookup
>> private String carVendor;
>>
>> @CarLookup
>> private String carType;
>> }
>>
>>
>> The
>> @Index an groups should also be respected when sorting the query
>> criterias. For older DBs you still sometimes need unnecessary
>> nativeQueries just to get the order right to match the data (first
>> query
>> criteria should let you get rid of most data). Not sure if this is
>> still true nowadays with the latest DBs but I think even Oracle10 had
>> problems with queries which didnt fit the order of the index.
>>
>
>Please explain further. A good query optimizer should consider the indexes.
>(But that doesn't *guarantee* that the result would be in that order)
[strub]
The order in which you write the columns in your CREATE INDEX statement did matter a lot in the past. The first one should be the one which has the best 'spread'. This gets better with more and more DB systems switching to dynamic statistics and cost based optimizers. But there are still use cases where the column 'ordinal' inside the Index does matter a lot. Thus I'd still like to have a way to explicitly state the number which denotes this ordinal. In some DBs that was so bad that the index was ignored at all if the order of the columns in the query statement didn't fit the order of the columns in the index itself (leading to a full table space scan). Not sure if this is a problem nowadays anymore...
>> Maybe
>> for logicalKeys we could do something similar to EmbeddedId for
>> @NaturalId. Automatically generating a unique index for those fields
>> and
>> making them usable in em.find(MyEntity, mylogicalKey) ...
>>
>
>Beyond a certain point I start worrying about too many indexes being
>added - and that being counterproductive.
[strub]
I agree that we need to take this into consideration. But finally I don't share this sentiment. A logical or 'natural' key is always a unique Index. Otherwise it would not be a natural KEY.
(Btw, I think there is another thread already where Steve pointed out that this might even be used to do an em.find(naturalkey) kind of thingy.)
Let's face it: currently you need to create most of your Indices via an externally maintained create_index.sql file. And this file is for sure much harder to maintain than having this information right in the entity, isn't?
So yes, we need to take care about people adding too many indices - but overall we would improve the situation imo, wdyt?
>> More about it in JPA_SPEC-22
>>
>>
>>
>> txs and LieGrue,
>> strub
>>
>>
>> PS:
>> hello everybody and thanks for the hard work on JPA! I'm some random
>> old jerk who started writing DB frameworks some decades ago on the
>> mainframe (in C on OS/360), and use/write db stuff in Java since late
>> 90s or so.
>
>OS/360? Wow.
[strub]
That was in the early 90s. This specific stock exchange program switched to OS/390 later and still running z/OS nowadays afaik.
And dumps and ABENDs basically still look the same on a Host, even today ;)
>
>> PPS: plz excuse me if I miss something obvious...
>
>I don't think you have. Thanks again for taking the trouble to write this up.
>
>-Linda
>