Index Awareness in IDT

• Data base Columns used as primary key and foreign keys, These keys allows queries to take advantage of indexes on key columns.
• In the universe, when you set up index awareness, you indicate in the information design tool which database columns are primary and foreign keys. 
  

              

                       Below are the some of the benefits of index Awareness:

• Performance and uniqueness.
• Universe design tool eliminates the number of joins in the Query and Reduce the number of tables joined.

• It can search indexed, rather than non-indexed, columns.
• Avoids issues with duplicate labels.
• Universe design tool can take advantages of the indexes on key columns to speed data retrieval.

Setting up Primary key Index awareness

Joined Customer, City, Region and Country tables and Created Query, to view the SQL before applying index awareness: Click Queries à Edit Query.

View Script à The Query contains the query filter applied to country. (Without index awareness, the universe design tool generates the following SQL)

The generated SQL places the query filter in the WHERE clause. The country names are used, which implies that the underlying database needs to search for the name values to generate the query. A more effective way is to use index values instead of name values.Now return to the business layer view to set primary key index awareness on the country object.

Modify the Country object to set primary key index awareness. The Primary key for the country object is thecounty_id Colman of the county table.

Click on the Country Name à Click the Key Tab à Click Add Key à New primary key line is inserted in the key type column à Select SQL button (to add a primary key value).

Add the appropriate column value to the SELECT statement (COUNTRY_ID)à save business layer àTest the results using the Query.

The Country name values in the WHERE clause have been replaced by index values due to the use of primary key index awareness for the country object,

Test the query by previewing the results are correctly matched.

Setting up Foreign Key Index awareness

With Primary key index awareness already applied, the generated SQL shows the index values in the WHERE clause.

However the country object is not used in the Query result object pane, the county table is still added to the FROM clause and a join to the country table is added to the WHERE clause.

Applying the foreign key index awareness on an object, the information design tool can restrict the values returned without the need to join the tables.

The universe design tool generates the SQL Country column table has been removed from the FROM Clause.

And country join has been removed from the WHERE clause and shows the index values in the WHERE clause, using the Region. Country_ID foreign key. and get the same results.