Share

Denormalized Columns in Siebel

Siebel uses Denormalized Columns to improve the performance of query operation in view where data visibility is restricted using Siebel View modes like Organization, Position etc. Denormalized columns are generally created for columns most frequently used for query like Contact First Name, Last Name etc.  These denormalized columns are created in Intersection tables of the base table with Position, Organization tables.  This provides an added improvement while querying for commonly used fields in My Contacts or All Contacts (Organization Visibility) views.

For example in My Contact View, there would be a by default where clause on S_POSTN_CON table on Position Id.  When user queries in the view for a particular first Name or Last Name, without the denormalized columns, SQL generated would have where clause on S_CONTACT as well as S_POST_CON table

By having the denormalized column, the SQL generated would have where clause only on Single table and performance of the query will be faster. For denormalized column, Siebel ensure that if you are updating the data using any standard Siebel method (UI, EIM, EAI) the value in denormalized columns are automatically kept in synchronization.  But if you are updating any such columns using direct SQL (not supported by Siebel), these denormalized column do not get updated.

4 comments on “Denormalized Columns in Siebel”

  1. Vamshi Reply

    Hi,

    This post is very nice.

    But instead of performance gain, we are facing performance degrade issues with this normalization.

    For example, When we Query on First Name in Contacts List Applet, the SQL being generated is Querying on S_POSTN_CON.CON_FST_NAME, which is taking long time to execute.

    But the same Query is made on S_CONTACT.FST_NAME, it is being executed very fast.

    So we wants to modify the configuration to set the Query is being made on S_CONTACT tabale instead of S_POSTN_CON.

    In our application anyways we do not require such View modes, so we tried with inactivating the View Modes. The Query is changed and performing Query on S_CONTACT. But the new issue here is while creating a record, no records are being inserted into S_POSTN_CON, though the link of Contact/Position is always active.

    Please suggest how to fix this.

  2. Vijay Gupta Reply

    Dear Vamshi,

    First of all thanks for your response, In vanilla configuration there is a Index “S_CONTACT_M51” in S_CONTACT that has FST_NAME as first field in database whereas the index S_POSTN_CON_M1 in S_POSTN has POSTN_ID followed by LAST_NAME and FST_NAME in index, since the query generated in My Contact would not be able to effectively use the index when querying on FST_NAME

    you can consider following options for better performance

    1. If your application requires frequent query using Contact First Name in My contact View, consider creating index on S_POSTN_CON on following columns in given sequence (POSTN_ID, CON_FST_NAME, CON_LAST_NAME).

    2. Since in your application doesn’t need to use the view mode, consider giving access to All across Org View. Query on this view does not uses denormalized columns. If you are querying using script, please set the view mode to “AllView” before executing the query.

    Please share your feedback after implementing this suggestions.

    Regards,
    Vijay.

  3. Arnab Chanda Reply

    @Vijay – I actually did the same thing in a past project. When I created a custom index on the S_POSTN_CON, performance improved.
    @Vamshi – S_PARTY, S_CONTACT, S_POSTN_CON are related by implicit joins in Contact BC. Unless the developer disabled anything vanilla (user property/link/class), any new contact record should create a corresponding S_POSTN_CON record.
    Siebel created the de-normalized columns for performance improvement only. They actually discuss in details about this in the performance tuning handbook. However, lot of times developers tweak around sort specs in the Contact BC without realizing the consequences. Also, whether S_CONTACT column is picked up or S_POSTN_CON column is picked up for sort/quety is defined in the BC class level, so there is not much you can do about it. Sometimes even adding an extra field to the sort spec can have adverse effects, specially in an Oracle CBO environment.
    I'd recommend that you spool the query, ask the DBA to run an explain plan for you, and then decide how to tune the BC.

  4. Joselo Reply

    Vijay excelent post! 
     I had the same performance problem using views of my team over contact bc, 
       In my case I found that in some process outside siebel, via some bad designed external interface, it changed S_POSTN_CON.CON_LAST_NAME with S_POSTN_CON.CON_FST_NAME and viceversa, following the rule of denormalization I decided to update the columns from S_POSTN_CON using S_CONTACT.
    The problem was solved 
    Regards!

Leave A Reply

Your email address will not be published. Required fields are marked *

error: Content is protected !!