The fastest way to update a table row when using Hibernate and Oracle
Imagine having a tool that can automatically detect JPA and Hibernate performance issues. Wouldn’t that be just awesome?
Well, Hypersistence Optimizer is that tool! And it works with Spring Boot, Spring Framework, Jakarta EE, Java EE, Quarkus, or Play Framework.
So, enjoy spending your time on the things you love rather than fixing performance issues in your production system on a Saturday night!
Introduction
Oracle provides several pseudocolumns, and ROWID
is one of them. The ROWID
pseudocolumn specifies the address of the underlying database record, and according to Oracle documentation, it’s the fastest way to reference a table row.
As explained on Ask TOM, there are some operations that might lead to a ROWID change (e.g. partitioning or compacting tables). If that’s the case, then you should not rely on the ROWID pseudocolumn since its value iss no longer consistent.
If your database never executes an operation that triggers a ROWID modification, then you should consider using this technique.
In this article, I’m going to show you how you can access a database record by its ROWID
when using Hibernate.
@RowId
The first thing to do is to annotate the JPA entity using the Hibernate-specific @RowId.
Considering that we have the following two entities:
@Entity(name = "Post") @Table(name = "post") @RowId( "ROWID" ) public class Post { @Id private Long id; private String title; @OneToMany( cascade = CascadeType.ALL, mappedBy = "post", orphanRemoval = true ) private List<PostComment> comments = new ArrayList<>(); //Getters and setters omitted for brevity public void addComment(PostComment comment) { comments.add(comment); comment.setPost(this); } public void removeComment(PostComment comment) { comments.remove(comment); comment.setPost(null); } } @Entity(name = "PostComment") @Table(name = "post_comment") @RowId( "ROWID" ) public class PostComment { @Id @GeneratedValue private Long id; @ManyToOne( fetch = FetchType.LAZY ) private Post post; private String review; //Getters and setters omitted for brevity @Override public boolean equals(Object o) { if (this == o) return true; if (!(o instanceof PostComment)) return false; return id != null && id.equals(((PostComment) o).getId()); } @Override public int hashCode() { return getClass().hashCode(); } }
If you wonder about the unusual equals and hashcode implementation choices, then you should read the following article.
The @RowId( "ROWID" )
mapping tells Hibernate to use the ROWID
pseudocolumn when accessing the database record when executing an UPDATE
statement.
Testing time
Assuming we have the following entities in our database:
Post post = new Post(); post.setId(1L); post.setTitle( "High-Performance Java Persistence" ); entityManager.persist(post); PostComment comment1 = new PostComment(); comment1.setReview("Great!"); post.addComment(comment1); PostComment comment2 = new PostComment(); comment2.setReview("To read"); post.addComment(comment2); PostComment comment3 = new PostComment(); comment3.setReview("Lorem Ipsum"); post.addComment(comment3);
When executing the following test case:
Post _post = doInJPA( entityManager -> { return entityManager.createQuery( "select p " + "from Post p " + "join fetch p.comments " + "where p.id = :id", Post.class) .setParameter( "id", 1L ) .getSingleResult(); } ); List<PostComment>_comments = _post.getComments(); _post.getComments().get( 0 ) .setReview( "Must read!" ); _post.removeComment( _comments.get( 2 ) ); doInJPA( entityManager -> { entityManager.merge( _post ); } );
Hibernate generates the following SQL statements:
SELECT p.id AS id1_0_0_, p.title AS title2_0_0_, p.ROWID AS rowid_0_, c.post_id AS post_id3_1_1_, c.review AS review2_1_1_, c.ROWID AS rowid_1_, c.post_id AS post_id3_1_0__, c.id AS id1_1_0__ FROM post p INNER JOIN post_comment c ON p.id = c.post_id WHERE p.id = 1 -- Merge SELECT query skipped for brevity UPDATE post_comment SET post_id = 1, review = 'Must read!' WHERE ROWID = AAAwmzAAEAAACZDAAA DELETE FROM post_comment WHERE id = 3
The SELELCT
statement includes the ROWID
pseudocolumn which is stored in the currently running Persistence Context. During the merge entity state transition, Hibernate copies the detached entity state onto the newly fetched entity versions, and the dirty checking mechanism propagates the changes to the database.
Only the UPDATE
statement benefits from the ROWID
pseudocolumn, the DELETE
statement does not (yet). The HHH-11761 is going to take care of this limitation, but it will only be addressed in 6.0.
ROWID vs INDEX access path
So, why would you care about ROWID
anyway? After all, every entity has its own identifier, and the underlying Primary Key is indexed as well.
To understand why it’s worth using the ROWID
pseudocolumn, it’s better to visualize the Execution Plan for the aforementioned UPDATE
statement:
--------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| --------------------------------------------------------------------------------- | 0 | UPDATE STATEMENT | | 1 | 537 | 1 (0)| | 1 | UPDATE | POST_COMMENT | | | | | 2 | TABLE ACCESS BY USER ROWID| POST_COMMENT | 1 | 537 | 1 (0)| ---------------------------------------------------------------------------------
Whereas, when updating a row by its identifier, the Execution Plan is:
------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| ------------------------------------------------------------------------- | 0 | UPDATE STATEMENT | | 1 | 538 | 1 (0)| | 1 | UPDATE | POST_COMMENT | | | | |* 2 | INDEX UNIQUE SCAN| SYS_C00281229 | 1 | 538 | 1 (0)| ------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("ID" = 1)
Although the Cost is the same in this trivial example, in reality, the TABLE ACCESS BY USER ROWID access path is faster to execute than INDEX UNIQUE SCAN because the Extractor can load the data page directly without having to go to the index to fetch the ROWID
.
If you enjoyed this article, I bet you are going to love my Book and Video Courses as well.
Conclusion
Hibernate is not just an ORM tool, but a full-blown data access framework, offering all sorts of performance optimizations. If you are using Oracle and you execute lots of UPDATE
statements, it makes a lot of sense to use the @RowId
mapping.
