Search This Blog

Monday 6 February 2012

Creating sql indexes via Hibernate

The definition of a database index  from wikipedia is as follows:
A database index is a data structure that improves the speed of data retrieval operations on a database table at the cost of slower writes and increased storage space.
Indexes can be created using one or more columns of a database table, providing the basis for both rapid random lockups and efficient access of ordered records
Indexes play an important role in optimizing database performance.The indexes can be specified as a part of the hbm files. To create an index on the FIRST_NAME column of user table, we need to add the index attribute to the first_name attribute.
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
        "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd" >
<hibernate-mapping package="com.sql.index">
    <class name="User">
        <id name="id" type="long">
            <generator class="native" />
        </id>
        <property name="firstName">
            <column name="FIRST_NAME" index="IDX_FIRST_NAME"/>
        </property>
        <property name="lastName">
            <column name="LAST_NAME" />
        </property>

    </class>
</hibernate-mapping>
On start up, the hbm2ddl tool generated the following index creation statement:
create index IDX_FIRST_NAME on User (FIRST_NAME)
It is also possible to create multi-column indexes using the same property. For example, if we need to create an Index on the basis of first_name and last_name, this can be done by setting the same index name on both columns. The modified hbm is as below:
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
        "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd" >
<hibernate-mapping package="com.sql.index">
    <class name="User">
        <id name="id" type="long">
            <generator class="native" />
        </id>
        <property name="firstName">
            <column name="FIRST_NAME" index="IDX_NAME" />
        </property>
        <property name="lastName">
            <column name="LAST_NAME" index="IDX_NAME" />
        </property>

    </class>
</hibernate-mapping>
The generated SQL is as follows:
create index IDX_NAME on User (FIRST_NAME, LAST_NAME)

4 comments:

  1. What if you want the last_name to come *first* in the index? Is it possible to determine the order of columns in the index?

    ReplyDelete
  2. Nice question Brad. I wonder if I changing the definition sequence could help, not tested it though.
    If it doesn't we can always add custom SQL code to be executed along with the hibernate generated code. Let me know if you get a solution for this one.

    ReplyDelete
  3. This comment has been removed by the author.

    ReplyDelete