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.

I'm running an online workshop on the 20-21 and 23-24 of November about High-Performance Java Persistence.

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.

Insert details about how the information is going to be processed

DOWNLOAD NOW