Indexing Business Objects

these guidelines apply primarily to cloud administrators on premise customers are responsible for tuning their own individual databases about indexing docid\ wjo94k2x6emphr1cmtgo accessing indexes docid\ wjo94k2x6emphr1cmtgo adding indexes docid\ wjo94k2x6emphr1cmtgo about synchronizing indexes docid\ wjo94k2x6emphr1cmtgo about indexing indexing benefits docid\ wjo94k2x6emphr1cmtgo indexing rules docid\ wjo94k2x6emphr1cmtgo about derived objects docid\ wjo94k2x6emphr1cmtgo indexing benefits you can index various fields within a business object indexing decreases the execution time for queries designating a field as an index field can help the search performance in the following instances indexing fields that containing many (thousands of) records that are frequently accessed can improve performance indexing fields that are not used often or that have few records might reduce performance therefore, we do not recommend this indexing too many fields can slow down performance the optimum number of fields is based on the amount of access, number of users, and number of records, and is usually determined by trial and error metadata also contains index related information use cached metadata to reduce the number of times that the database is accessed metadata indexes are a subset of the indexes in the database schema smart indexing does the following enables you to capture indexes from the database schema and import them to a business object definition (table metadata) automatically creates and maintains cover indexes based on the business object metadata automates the creation of the primary and unique key constraints, to manage data integrity automates the creation of cover indexes with commonly searched fields, to improve query performance you can manage indexes directly from the configuration console, even without direct access to the database you can fix mismatches and resolve discrepancies between indexes in the metadata and database schemas indexing rules the primary key column must be a unique, non nullable, single column that does not have a filter, and has a maximum size of 900 bytes if the unique key column is nullable, then it must have a filter expression view cannot have filter expressions the maximum total size of index key columns is 900 bytes if you try to add an index to a business object that has more than one million records, you get an error message contact ivanti sre (site reliability engineering) via your portal and raise a ticket they can edit the database directly to make the change for you about derived objects group objects share fields with other objects and contain derived objects for example, address is a group object for example a sales area is determined by a brokerid (with its own table), company (with its own table), and stateid (with its own table) these three fields need to connect to a fourth table to pull proper information about the sales area (such as sales area number, sales reps, sales region, and sales regional manager), and this fourth table also has brokerid, company and stateid fields we need to connect three fields from three different tables to this fourth table the fourth table is known as a derived object derived objects list the base object indexed items you can index the fields from this derived object, and at the same time, see which fields are indexed in the original objects accessing indexes from the configuration console, click build > business objects the application displays the business objects workspace select any business object the application displays the business object workspace click the indexes tab the application displays the indexes workspace with a list of indexes for the business object indexes workspace the indexes workspace contains the following information name the name of the index configuration describes the index configuration, in a comma separated list, including the following primary key specifies that the index is defined for a field with a primary key constraint (such as the recid field for incidents) clustered specifies that the index is clustered currently, this is always disabled and cannot be changed unique specifies that the index is defined for a field with a unique constraint when you set a field as unique, the application creates a unique constraint and unique index in the database schema filtered specifies that the index has at least one filter expression defined cover indicates that the index has at least one included field disabled indicates that the index is disabled you cannot set this parameter from the indexes workspace it must be set within the database system indicates that the index is a application index you cannot set this parameter from the indexes workspace it must be set within the database key fields lists the key fields for this index see viewing, adding, and deleting key fields docid\ wjo94k2x6emphr1cmtgo included fields displays a list of all the included columns used by the index the order of included fields does not matter see viewing, adding, and deleting included fields docid\ wjo94k2x6emphr1cmtgo to delete an index, click the delete icon at the end of the row adding indexes adding a new index docid\ wjo94k2x6emphr1cmtgo viewing, adding, and deleting key fields docid\ wjo94k2x6emphr1cmtgo viewing, adding, and deleting included fields docid\ wjo94k2x6emphr1cmtgo viewing, adding, and deleting filter expressions docid\ wjo94k2x6emphr1cmtgo adding a new index from the configuration console, click build > business objects the application displays the business objects workspace select any business object the application displays the business object workspace click the indexes tab the application displays the indexes workspace with a list of indexes for the business object to add a new index, click add new\ the application displays index fields associated with this business object select the field to index for this business object you can add more fields later the application displays the system properties workspace system properties page enter information into the fields field description index name a unique name for this index description a useful description for this index unique designates this value as unique, preventing duplicate entries in the index and its backing table you can create uniqueness by using multiple fields this option automatically creates an unique index of this field unlike the primary key field you can have multiple unique fields in the business object this field is allowed to contain null values (although the null values are not indexed) clustered checks if the business object is part of a clustered index at the database level clustered indexes can greatly increase overall speed of retrieval, but usually only where data is accessed sequentially in the same or reverse order of the clustered index, or when a range of items is selected this is the same as a clustered index function for a microsoft sql database a clustered index cannot contain included fields (it cannot be a cover index) this checkbox is disabled for index fields that have an increasing value, such as guid key fields see viewing, adding, and deleting key fields docid\ wjo94k2x6emphr1cmtgo included fields see viewing, adding, and deleting included fields docid\ wjo94k2x6emphr1cmtgo filter expression optional you can apply a filter to the index that does not include null values click the add icon , then choose a field from the list to create a filter repeat as needed operation choose an operation from the drop down list for this filter notnull the filter does not index null values see about null values docid\ cdcowtymtdsshk9kupklw for more information on defining null values value enter a string value or expression (see about expressions docid 0eih f97oc4pmanhy9f29 ) for this filter click add this index the application adds the index to the business object viewing, adding, and deleting key fields the application displays the running size, in bytes, of the index for the key fields the limit is 900 bytes from the configuration console, click build > business objects the application displays the business objects workspace select any business object the application displays the business object workspace click the indexes tab the application displays the indexes workspace with a list of indexes for the business object click an index name to open the record to add a key field, do the following a click the add icon at the end of the row b click not set to select a field from the drop down list c click ascending to change the order you can select either ascending or descending to delete a key field, click the delete icon at the end of the row click save viewing, adding, and deleting included fields from the configuration console, click build > business objects the application displays the business objects workspace select any business object the application displays the business object workspace click the indexes tab the application displays the indexes workspace with a list of indexes for the business object click an index name to open the record to add an included field, do the following a click the add icon at the end of the row b click not set to select a field from the drop down list to delete an included field, click the delete icon at the end of the row click save viewing, adding, and deleting filter expressions from the configuration console, click build > business objects the application displays the business objects workspace select any business object the application displays the business object workspace click the indexes tab the application displays the indexes workspace with a list of indexes for the business object click an index name to open the record to add a filter expression, do the following a click the add icon at the end of the row b click not set under field to select a field from the drop down list c click notnull to select an operation from the drop down list d click not set under value to enter a value to delete a filter expression, click the delete icon at the end of the row click save about synchronizing indexes index types docid\ wjo94k2x6emphr1cmtgo synchronizing index messages docid\ wjo94k2x6emphr1cmtgo synchronizing indexes docid\ wjo94k2x6emphr1cmtgo index types there are two types of indexes database schema and metadata property metadata indexes database schema indexes are compared? index name indexname index name yes description description n/a no fields fields n/a yes condition unique unique is unique yes condition system system n/a no condition isdisabled isdisabled is disabled yes condition isclustered isclustered is clustered yes origin origin n/a no condition isprimarykey isprimarykey is primary key yes table name n/a table name no index id n/a index id no unique constraint n/a is unique constraint no the application compares some of the index values, and if there are any differences, the application displays the differences synchronizing index messages if you open a business object and see a caution sign next to the indexes tab, that means that the indexes need to be synchronized mismatched index message 1 the application also displays a warning message on the indexes workspace mismatched index message 2 depending on the type of error, you may also see both an error message and a caution icon next to the affected index synchronizing indexes from the configuration console, click build > business objects the application displays the business objects workspace select any business object the application displays the business object workspace click the indexes tab the application displays the indexes workspace with a list of indexes for the business object if there are any indexes that need to be synchronized, the application displays an error message click save the application displays the resolve indexing discrepancies dialog box select one of the following accept database indexes select this option if you see an error that an index is not defined in the metadata does the following copies database schema indexes to the metadata if they are not in the metadata removes metadata indexes from the metadata if they are not in the database schema applies changes from the database schema indexes to the metadata indexes accept metadata indexes select this option if you see an error that an index is not defined in the database schema does the following copies metadata indexes to the database schema if they are not in the database schema removes database schema indexes from the database schema if they are not in the metadata applies changes from the metadata indexes to the database schema indexes resolve later does not change the indexes click ok