After enabling CIAI for user key fields like Last Name, the Contacts View is slower (Doc ID 536211.1)

Last updated on MARCH 02, 2017

Applies to:

Siebel Tools - Version 8.0 [20405] and later
Information in this document applies to any platform.
Product Release: V8 (Professional)
Version: 8.0 [20405]
Database: IBM DB2/UDB 8.1 FixPack 1S
Application Server OS: Microsoft Windows 2003 Server SP2
Database Server OS: Sun Solaris 9

This document was previously published as Siebel SR 38-3456864273.
Checked for Relevance on ***Jul-05-2012***


Symptoms

After upgrading the siebel application from 7.7 to 8.0, the contacts view loads very slowly.When the SQL is ran directly against the DB2 database, it takes 2 seconds. Looking closely at the SQL, the application is actually adding a clause at the end of the SQL: FOR FETCH ONLY OPTIMIZE FOR 1 ROW. When we ran the SQL along with this clause on the DB2 database, it takes 18-20 seconds. Why would this clause make so much difference? Is there any setting on the database that should be changed?

So, there are two issues with the SQL:
1. If X_LAST_NAME_CI column in the where clause of the SQL, with FOR FETCH ONLY OPTIMIZE FOR 1 ROW clause is used, it is taking a lot of time. Without this clause, it takes less time.
2. If we replace the X_LAST_NAME_CI with CON_LAST_NAME column, result is very quick with either FOR FETCH..... clause or not.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms