Menu
I am using Hibernate 3.3.2 in a fairly large project with PostgreSQL 8.4 using the PostgreSQLDialect.
The way we have our relationships set up, we end up doing a lot of searching on the Foreign Key attributes of our tables.
For performance reasons, I would like to have Hibernate add Indexes to the all of the foreign key columns when we are creating our tables using hbm2dll.auto.
MySQL would automatically add Indexes to these columns, but there doesn't seem to be a way to do this in Postgres.
Is there an option I can set somewhere, or something I can add to my hbm.xml files to make this happen?
717
When you define a foreign key constraint in your database table, an index will not be created automatically on the foreign key columns, as in the PRIMARY KEY constraint situation in which a clustered index will be created automatically when defining it.
MySQL requires that foreign key columns be indexed; if you create a table with a foreign key constraint but no index on a given column, an index is created. Information about foreign keys on InnoDB tables can also be found in the INNODB_FOREIGN and INNODB_FOREIGN_COLS tables, in the INFORMATION_SCHEMA database.
Every foreign key? No. Where the selectivity is low (i.e. many values are duplicated), an index may be more costly than a table scan. Also, in a high activity environment (much more insert/update/delete activity than querying) the cost of maintaining the indexes may affect the overall performance of the system.
Defining a foreign key constraint does not create the underlying index. A constraint only put a limit on what data can be inserted into the column. Adding a non-clustered index to the Foreign Key Column in the child table can increase query performance by removing Table or Index Scans with Index Seeks.
Introduction In this post, we feature a comprehensive Example on Hibernate Foreign Key. Foreign key refers to single column or group of columns in table that link data present in another table through its primary key. A Foreign key can’t exist without its parent key but viceversa is not true. Example – A Menu can have submenus.
This class is used to make a hibernate framework understand the requirement and the strategy to get the primary key generated. This class helps enable the use of primary key functionality to make a record unique in the database. These classes operate upon interface called: org.hibernate.id.IdentifierGenerator.
This is used to support the “id” column which is generated internally by database only by calling the auto-incremented column algorithm of respective database. In this case, hibernate will not generate the primary key and so no parameters for this class to be passed.
A Foreign key can’t exist without its parent key but viceversa is not true. Example – A Menu can have submenus. It can be represented in tabular form as shown below where column MENU_ID is Primary key of T_MENU table and it is acting as Foreign Key (link between both tables) for T_SUBMENU table:
Here's a quick-and-dirty query that would generate the DDL for indexes on each defined foreign key in a schema:
SELECT 'CREATE INDEX fk_' || conname || '_idx ON '
|| relname || ' ' ||
regexp_replace(
regexp_replace(pg_get_constraintdef(pg_constraint.oid, true),
' REFERENCES.*$','',''), 'FOREIGN KEY ','','') || ';'
FROM pg_constraint
JOIN pg_class
ON (conrelid = pg_class.oid)
JOIN pg_namespace
ON (relnamespace = pg_namespace.oid)
WHERE contype = 'f'
AND nspname = 'public';
This has only been tested in PostgreSQL 8.4, but I think it should work in most 8.x versions.
Also note that this doesn't detect which ones are already covered by an index so you would probably have some duplication.
190
Hibernate only enforces indexcreation for MySQL, not for PostgreSQL, Oracle, etc. MySQL doesn't allow foreign keys without an index, other databases don't have this restriction.
Sybase has an explanation why it's a good thing not to enforce indexes, the Hibernate forum also has a topic about it (Oracle related) including a workaround. But before you start creating a lot of indexes, start checking if you need all these indexes. In many cases the indexes can be combined with others to speed things up. It all depends!
Use EXPLAIN on your queries to see how the database executes them, what indexes are used, where indexes are missing, etc.
30
Try either of:
<many-to-one name="master" class="Master" column="master_id" index="my_index_name" />
<many-to-one name="master" class="Master">
<column name="master_id" index="my_index_name" />
</many-to-one>
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
