[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-3709) Add StartRevision/EndRevison fileds to audit tables

[Michal Wegrzyn (JIRA)]

    [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-3709?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=32177#action_32177 ] 

Michal Wegrzyn commented on HHH-3709:
-------------------------------------

This would be a great improvement. I've been using exactly that query in my versioning system written in Oracle PL/SQL. The sql performance is very high. It has been tested on 4 million-row audit table. 

Additionaly I suggest using an database index on multiple columns in the following order:( EndRevision, StartRevision, id) and retype the query in that way:

 select a.id, a.REV, a.REVTYPE, a.name, a.surname, a.address_id
      from Person_AUD a
      where a.REVTYPE <> ? and a.id = ?
            and  a.EndRevision>? and a.StartRevision<=?

I would be interested in contributing on this field.

Best regards,
Michal

> Add StartRevision/EndRevison fileds to audit tables
> ---------------------------------------------------
>
>                 Key: HHH-3709
>                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-3709
>             Project: Hibernate Core
>          Issue Type: Improvement
>          Components: envers
>    Affects Versions: 3.4
>            Reporter: jason shi
>
> In Envers audit tables(eg:Person_Aud), two fields added:REV,REVTYPE
> When retrieve data at special REV, a sql with subselect executed:
>  select a.id, a.REV, a.REVTYPE, a.name, a.surname, a.address_id
>       from Person_AUD a
>       where a.REVTYPE <> ? and a.id = ?
>           and a.REV = (select max(b.REV)   from Person_AUD b   where b.REV <= ?       and a.id = b.id)
> The sql performance is poor.
> I suggest adding  StartRevision/EndRevison fileds to the audit tables,replace the REV field.
> The StartRevision equals the original REV field,EndRevision will be filledd when this record changed in next Revision.
> The new query sql will like this:
>  select a.id, a.REV, a.REVTYPE, a.name, a.surname, a.address_id
>       from Person_AUD a
>       where a.REVTYPE <> ? and a.id = ?
>             and a.StartRevision<=? and a.EndRevision>?

-- 
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