Liferay Support does not recommend or endorse specific third-party products over others. Liferay is not responsible for any instructions herein or referenced regarding these products. Any implementation of these principles is the responsibility of the subscriber.
This article documents a known issue with DB2 when executing SQL queries like SELECT * FROM table_ WHERE column_ = ? whose:
- column column_ has a non-unique index, that is—in the Service Builder definition there is a non-unique, non-collection finder which generates a non-unique index
- table table_ has hundreds of thousands of records
- column column_ has all its values set to the default value (in case of numeric type, this default value is 0)
A concrete example of this type of query is SELECT Group_.* FROM Group_ WHERE Group_.liveGroupId = ? with a huge amount of sites and none of them are not under local Staging.
The reason behind this behavior is that DB2 planner is making the decision of doing a full table scan without taking into account the size of the table despite the fact that using the non-unique index would perform better.
Running the command db2 runstats on table table_ does not make any difference on the query's execution plan.
Resolution
Status: Won't Fix / Workaround Available
This issue is very specific to the DB2 planner, being only reproducible under the particular scenario described above and it will not be fixed.
The available workaround is to set the attribute VOLATILE of the affected table to true in order to force the use of the non-unique index.
This does not have any impact in current queries which already use indexes, although it might penalize the performance of some other queries which do not use any index slightly.
Additional Information
- Related ticket LPS-81265
- DB2 article on Favoring index access