[Hibernate-issues] [Hibernate-JIRA] Commented: (HHH-710) Multi Column Index / Column in Multiple Indexes feature

[Tobias (JIRA) <no...@at...>]

    [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-710?page=comments#action_22548 ] 

Tobias commented on HHH-710:
----------------------------

I don't see how it is possible right now to control the order in which multiple columns are added to an index. There are situations where the "proper" ordering of the columns lets the DBMS use one index for multiple types of queries while reversing the order of the columns in the index makes it unuseable for all types of queries but one.

Example queries:

select ... from ... where a=? and b=?

select ... from ... where b=?

You either create two indexes with (a,b) and (b) or just one index (b,a) that can be used for both queries.


> Multi Column Index / Column in Multiple Indexes feature
> -------------------------------------------------------
>
>          Key: HHH-710
>          URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-710
>      Project: Hibernate3
>         Type: New Feature

>   Components: core
>     Versions: 3.0.5
>  Environment: N/A
>     Reporter: Bonny Rais

>
>
> There are many requests in the forum regarding Multi Column Index,
> declared separately from property declarations in mapping files.
> Hibernate already supports this capability, but only by way of 
> marking a property as participant in an index using the index 
> attribute in mapping files. Recent enhancement means that the 
> same column can be declared to be in multiple indexes.
> However, there is no control over the order of columns in the index
> or whether the index is unique.
> I've had a go at implementing this feature and to the best of my 
> ability to check, there are minor modifications required:
> 1. The DTD needs to change in two places:
>    a. The class entity requires having a new entity reference to the
>       index clause ( add a '(index)*,' after the property references)
>    b. add two attributes to the index entity itself: 'name' and 
>       'unique' and make them implied to ensure backwards compatibility.
> 2. HbmBinder.java needs to have a small fragment added to detect the
>    index element within the class node and use the currently existing
>    methods to add or create indexes.
> I can produce a patch file to show these differences, so people in the 
> Hibernate team can have a look and comment.
> I have tested this in my environment, which admittedly is not very 
> large or representative of all scenarios, but it works and works well.
> Cheers,
> Bonny

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://opensource.atlassian.com/projects/hibernate/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira