When an index in a TPP hierarchy exists on a secondary partition but not the primary, large table optimization will attempt to ignore the index on the primary partition, resulting in SQL exceptions and empty lists.

Steps to Reproduce

Tested in Kingston and London:
1) Create a new index on the sys_updated_on field using the Index Creator in Tables & Columns module.
2) Add the dictionary attribute "largeTable=true" to cmdb_ci.
3) Open the list view for cmdb_ci and sort the list by the "Updated" column.
4) Observe SQL exception, either in UI or the logs:
SEVERE *** ERROR *** FAILED TRYING TO EXECUTE ON CONNECTION 4: SELECT cmdb0.`sys_id` FROM cmdb cmdb0 ignore index(sys_updated_on) WHERE cmdb0.`sys_class_path` LIKE '/!!/!E/!!/!$/!3%' ORDER BY cmdb0.`sys_updated_on` DESC limit 0,20
Syntax Error or Access Rule Violation detected by database (Key 'sys_updated_on' doesn't exist in table 'cmdb0')


Set the system property glide.db.optimize.root_table.cmdb=false. If this alone does not address the problem, you may need to also add the dictionary attribute smallTable=true to the cmdb_ci table's sys_dictionary collection record.

NOTE: This change may present a performance impact. Be sure to test in a sub-prod environment to evaluate the impact of this change.

Related Problem: PRB1321133

Seen In


Associated Community Threads

There is no data to report.

Article Information

Last Updated:2019-07-01 15:22:03