Bookshelf v10.1.3.2: About the DAC Indices Tab

The Indices tab displays a list of all the indices associated with the selected source system container. 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.

Table 18 provides a description of the index properties displayed in the top pane of the Indices tab.

Table 18. DAC Indices Tab Top Pane Properties
Column	Description
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 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.
Always Drop & Create	Indicates whether the index will be dropped and created regardless of whether the table is being loaded using a full load or incremental load.

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

Table 19. 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 20 provides a description of the child tabs in the bottom pane of the Indices tab.

Table 20. Indices Child Tabs
Tab	Description
Columns	Displays the list of columns the index is made of. It includes the following properties: Position. Position of the column in the index. Sort Order. Indicates whether the sort order is ascending or descending.
Databases	Lists the database types that apply to the selected index. If no database type is indicated, the index will not be created.

Actions Available

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

Database Filter

The database filter appears in the Indices tab to the right of the top pane toolbar, as shown in Figure 11. Click on the words "Database Type" to open the Index Filtering dialog box. It 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.