Hi All


I am explaining importance of the Index Awareness.


  • 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.

/wp-content/uploads/2014/06/1_465021.png


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

/wp-content/uploads/2014/06/2_465022.png

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 the county_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.

/wp-content/uploads/2014/06/3_465050.png


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,


/wp-content/uploads/2014/06/4_465051.png

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.
/wp-content/uploads/2014/06/5_465052.png

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.

/wp-content/uploads/2014/06/6_465056.png

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

/wp-content/uploads/2014/06/7_465057.png

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.


PS: Please be informed that this is just a taking one example and explain step by step.

Hope this was helpful.

To report this post you need to login first.

2 Comments

You must be Logged on to comment or reply to a post.

    1. Mark Prosser

      ZEEK ZEEK, The region table is being used in the WHERE clause. Using Index Awareness and setting up a foreign key column on your object means that if no objects are used from that table (in this case Countrys) then the foreign key column kicks in and takes the place of the object. So, REGION.COUNTRY_ID is used in place of COUNTRYS.COUNTRY_NAME, with the country names translated to their COUNTRY_ID

      Does that make sense to you now?

      (0) 

Leave a Reply