Bookshelf v7.7: About the DAC Indices Tab

The Indices tab displays a list of all the indices of all the data warehouse tables. It is recommended that you do not register any indices for source tables. During the ETL process, when a table is going to be truncated, all the indices as defined in the repository will be dropped before the data is loaded and will be created after the data is loaded automatically. While this improves the ETL performance, the preconfigured workflows have the bulk load option turned on. The bulk load will fail if there are indices on the table. Therefore, it is important to keep the index definitions in sync with the database. For example, if you create an index on the database, and it is not registered in the repository, the index will not be dropped and the load will fail.

For Teradata databases, only secondary indices should be registered in the DAC. You should not register primary indices or the more complex indices, such as single- and multi-table indices, because they cannot be dropped and recreated. You can use SQL commands to drop and create such tasks in the DAC.

The drop-down list to the right of the top pane toolbar allows you to filter the set of displayed indices based on the database type. To display all indices regardless of database type, select the option All.

Table 40. DAC Indices Tab List Columns
Column	Description
Name	Unique name with which the index is created.
Table Name	Table for which an index is created.
Index Usage	Usage of index: ETL or Query. An ETL index is typically used during the ETL process. A Query index is an index is used only during the reporting process. It is recommended that you have a clear understanding of when and where the index will be used at the time of registering the index.
Databases	Allows you to associate an index with a database type.
# Unique Columns	For unique indices, the number of columns that will be unique.
Is Unique	Indicates whether the index is unique.
Is Clustered	Indicates whether the index is clustered. There can be only one clustered index per table.
Is Bitmap	Indicates whether the index is of the bitmap type.
Allow Reverse Scan	Applicable only for DB2-UDB databases. The index will be created with the Allow Reverse Scan option.
Table Space Name	Applicable only for Oracle databases. If a tablespace is mentioned, an index is created in that particular tablespace. If all indices go to a tablespace different from the data area, you can define it in the ETL Preferences (Tools > ETL Management> Preferences).
Inactive	Indicates whether an index is active or inactive. Inactive indices do not participate in the ETL process.

Table 41 shows which index properties are available for the different database types that are supported.

Table 41. Index Properties and Database Types Matrix
Column/ Database	Is Unique	Is Clustered	Is Bitmap	Allow Reverse Scan	# Unique Columns
Oracle	X		X
DB2	X	X		X	X
DB2-390	X
MSSQL	X	X

Table 42. Indices Subtabs
Tab	Description
Edit	Allows you to edit the table selected in the top pane. The "Display database-specific properties" drop-down list allows you to specify a database type in order to display database-specific properties in the Edit window.
Description	Displays a description of the table selected in the top pane.
Columns	Displays the list of columns the index is made of. The list of columns contains the following column headings: Name. Name of the column. Position. Position of the column in the index. Sort Order. Indicates whether the sort order is ascending or descending. Inactive. Indicates whether the column is active or inactive.
Databases	For Siebel Data Warehouse version 7.7.1.3 and lower, indicates whether an index is applicable only to a certain type of database. If no database is indicated, the index will always be created. For Siebel Data Warehouse version 7.7.1.4 and higher, indicates the database types that apply to the index. If no database type is indicated, the index will not be created. You can inactivate a database type by checking the Inactive check box.

Actions Available

The following actions are available in the top pane toolbar and in the right-click menu when the Indices tab is active.

Copy Record

Advanced Custom Index Management

The DAC allows you to drop and recreate indices during the load process, which reduces the overall load time during a full load. The DAC drops and recreates indices based on the index definitions stored in the DAC metadata.

NOTE: More advanced index management needs to be handled outside of the DAC, such as creating partitioned indices on Oracle databases, which requires specific syntax, and creating single-table and multi-table join indices on Teradata databases. In such cases you can use DAC SQL tasks placed appropriately in the task dependencies, or you can use a pre-session or post-session script in Informatica. You must drop the partitioned index before a task runs to load data using the bulk loader, because the bulk loader may fail if there are indices on the target database.

If you need to modify the preconfigured indices with extra options or syntax, you must inactivate them in the DAC metadata so that the DAC server does not try to drop and recreate them. You can then manage these indices in the same manner as the advanced index management described above.