Configuration Guidelines > Performance Guidelines > Reusing Standard Columns >

Table S_ CONTACT: Reusing LAST_NAME, FST_NAME, MID_NAME

The columns LAST_NAME, FST_NAME, MID_NAME of the S_CONTACT table are denormalized into three other columns in the S_POSTN_CON table: CON_LAST_NAME, CON_FST_NAME, CON_MID_NAME.

The visibility setting of the view often governs the sorting of data in the view. A good example of this is when VisibilityAppletType=Sales Rep, which is the case on the My Contacts view.

Here, the application uses the denormalized columns CON_LAST_NAME and CON_FST_NAME of the S_POSTN_CON table. This forces the database to use a particular index.

If the contact name was stored in extension columns in the S_CONTACT table (for example, X_LAST_NAME and X_FST_NAME), these columns would be used for sorting (as a result of existing sort specifications, user interaction, and querying) instead of the standard LAST_NAME and FST_NAME columns.

Even if these extension columns were indexed, the application could not use the index for creating the needed joins and for sorting the data, because the index is on S_CONTACT and not on S_POSTN_CON. This would result in a significant reduction in performance, especially with larger sets of results, as shown in this example.

The first SQL statement below is generated by the standard view My Contacts. The query plan shows that the database uses indexes to execute the statement.

SELECT

T1.LAST_UPD_BY,

T1.ROW_ID,

T1.CONFLICT_ID,

.

.

.

T15.LAST_UPD_BY,

T2.CON_MANAGER_PER_ID,

T16.CITY,

T2.ASGN_USR_EXCLD_FLG

FROM

SIEBEL.S_PARTY T1

INNER JOIN SIEBEL.S_CONTACT T2 ON T1.ROW_ID = T2.PAR_ROW_ID

INNER JOIN SIEBEL.S_POSTN_CON T3 ON (T3.POSTN_ID = '1-C1RC', 0.05)

AND T2.ROW_ID = T3.CON_ID

INNER JOIN SIEBEL.S_PARTY T4 ON (T4.ROW_ID = T3.POSTN_ID, 0.05)

LEFT OUTER JOIN SIEBEL.S_BU T5 ON T2.BU_ID = T5.PAR_ROW_ID

LEFT OUTER JOIN SIEBEL.S_USER T6 ON T2.EMP_ID = T6.PAR_ROW_ID

LEFT OUTER JOIN SIEBEL.S_CONTACT T7 ON T2.CON_MANAGER_PER_ID=

T7.PAR_ROW_ID

LEFT OUTER JOIN SIEBEL.S_ORG_EXT T8 ON T2.PR_DEPT_OU_ID =

T8.PAR_ROW_ID

LEFT OUTER JOIN SIEBEL.S_TIMEZONE T9 ON T2.TIMEZONE_ID = T9.ROW_ID

LEFT OUTER JOIN SIEBEL.S_CONTACT_SS T10 ON T1.ROW_ID = T10.PAR_ROW_ID

LEFT OUTER JOIN SIEBEL.S_INT_INSTANCE T11 ON T10.OWN_INST_ID =

T11.ROW_ID

LEFT OUTER JOIN SIEBEL.S_TIMEZONE_LANG T12 ON T9.ROW_ID =

T12.PAR_ROW_ID AND T12.LANG_ID = 'ENU'

LEFT OUTER JOIN SIEBEL.S_EMP_PER T13 ON T1.ROW_ID = T13.PAR_ROW_ID

LEFT OUTER JOIN SIEBEL.S_CONTACT_X T14 ON T1.ROW_ID = T14.PAR_ROW_ID

LEFT OUTER JOIN SIEBEL.S_PER_PRTNRAPPL T15 ON T1.ROW_ID =

T15.PAR_ROW_ID

LEFT OUTER JOIN SIEBEL.S_ADDR_ORG T16 ON T2.PR_OU_ADDR_ID =

T16.ROW_ID

LEFT OUTER JOIN SIEBEL.S_PARTY T17 ON T2.PR_DEPT_OU_ID = T17.ROW_ID

LEFT OUTER JOIN SIEBEL.S_ORG_EXT T18 ON T2.PR_DEPT_OU_ID =

T18.PAR_ROW_ID

LEFT OUTER JOIN SIEBEL.S_POSTN T19 ON T3.POSTN_ID = T19.PAR_ROW_ID

LEFT OUTER JOIN SIEBEL.S_USER T20 ON T19.PR_EMP_ID = T20.PAR_ROW_ID

LEFT OUTER JOIN SIEBEL.S_PARTY T21 ON T2.PR_GRP_OU_ID = T21.ROW_ID

LEFT OUTER JOIN SIEBEL.S_ORG_GROUP T22 ON T2.PR_GRP_OU_ID =

T22.PAR_ROW_ID

WHERE

((T2.PRIV_FLG = 'N') AND

(T3.CON_LAST_NAME >= ?))

ORDER BY

T3.POSTN_ID, T3.CON_LAST_NAME, T3.CON_FST_NAME

Query plan : T3(S_POSTN_CON_M1),T2(S_CONTACT_P1),T1(S_PARTY_P1),T1(S_PARTY_P1), T19(S_POSTN_U2),T9(S_TIMEZONE_P1),T21(S_PARTY_P1),T17(S_PARTY_P1), T4(S_PARTY_P1),T7(S_CONTACT_U2),T16(S_ADDR_ORG_P1),T18(S_ORG_EXT_U 3),T8(S_ORG_EXT_U3),T13(S_EMP_PER_U1),T20(S_USER_U2),T6(S_USER_U2) ,T22(S_ORG_GROUP_U2),T5(S_BU_U2),T10(S_CONTACT_SS_U1),T11(seq),T14 (S_CONTACT_X_U1),T12(S_TIMEZONE_LANG_U1),T15(S_PER_PRTNRAPPL_U1)

In the second SQL statement, the ORDER BY clause has been changed. Even though the columns LAST_NAME, FST_NAME of S_CONTACT are indexed, the database cannot use this index. Performance decreases from the use of a temporary table. The same behavior happens if the ORDER BY clause uses the columns X_LAST_NAME and X_FST_NAME instead of LAST_NAME and FST_NAME.

SELECT

T1.LAST_UPD_BY,

T1.ROW_ID,

T1.CONFLICT_ID,

.

.

.

T15.LAST_UPD_BY,

T2.CON_MANAGER_PER_ID,

T16.CITY,

T2.ASGN_USR_EXCLD_FLG

FROM

SIEBEL.S_PARTY T1

INNER JOIN SIEBEL.S_CONTACT T2 ON T1.ROW_ID = T2.PAR_ROW_ID

INNER JOIN SIEBEL.S_POSTN_CON T3 ON (T3.POSTN_ID = '1-C1RC', 0.05)

AND T2.ROW_ID = T3.CON_ID

INNER JOIN SIEBEL.S_PARTY T4 ON (T4.ROW_ID = T3.POSTN_ID, 0.05)

LEFT OUTER JOIN SIEBEL.S_BU T5 ON T2.BU_ID = T5.PAR_ROW_ID

LEFT OUTER JOIN SIEBEL.S_USER T6 ON T2.EMP_ID = T6.PAR_ROW_ID

LEFT OUTER JOIN SIEBEL.S_CONTACT T7 ON T2.CON_MANAGER_PER_ID =

T7.PAR_ROW_ID

LEFT OUTER JOIN SIEBEL.S_ORG_EXT T8 ON T2.PR_DEPT_OU_ID =

T8.PAR_ROW_ID

LEFT OUTER JOIN SIEBEL.S_TIMEZONE T9 ON T2.TIMEZONE_ID = T9.ROW_ID

LEFT OUTER JOIN SIEBEL.S_CONTACT_SS T10 ON T1.ROW_ID = T10.PAR_ROW_ID

LEFT OUTER JOIN SIEBEL.S_INT_INSTANCE T11 ON T10.OWN_INST_ID =

T11.ROW_ID

LEFT OUTER JOIN SIEBEL.S_TIMEZONE_LANG T12 ON T9.ROW_ID =

T12.PAR_ROW_ID AND T12.LANG_ID = 'ENU'

LEFT OUTER JOIN SIEBEL.S_EMP_PER T13 ON T1.ROW_ID = T13.PAR_ROW_ID

LEFT OUTER JOIN SIEBEL.S_CONTACT_X T14 ON T1.ROW_ID = T14.PAR_ROW_ID

LEFT OUTER JOIN SIEBEL.S_PER_PRTNRAPPL T15 ON T1.ROW_ID =

T15.PAR_ROW_ID

LEFT OUTER JOIN SIEBEL.S_ADDR_ORG T16 ON T2.PR_OU_ADDR_ID =

T16.ROW_ID

LEFT OUTER JOIN SIEBEL.S_PARTY T17 ON T2.PR_DEPT_OU_ID = T17.ROW_ID

LEFT OUTER JOIN SIEBEL.S_ORG_EXT T18 ON T2.PR_DEPT_OU_ID =

T18.PAR_ROW_ID

LEFT OUTER JOIN SIEBEL.S_POSTN T19 ON T3.POSTN_ID = T19.PAR_ROW_ID

LEFT OUTER JOIN SIEBEL.S_USER T20 ON T19.PR_EMP_ID = T20.PAR_ROW_ID

LEFT OUTER JOIN SIEBEL.S_PARTY T21 ON T2.PR_GRP_OU_ID = T21.ROW_ID

LEFT OUTER JOIN SIEBEL.S_ORG_GROUP T22 ON T2.PR_GRP_OU_ID =

T22.PAR_ROW_ID

WHERE

((T2.PRIV_FLG = 'N') AND

(T3.CON_LAST_NAME >= ?))

ORDER BY

T3.CON_LAST_NAME, T3.CON_FST_NAME, T3.POSTN_ID

Query plan: TEMPORARY TABLE T3(S_POSTN_CON_M1),T2(S_CONTACT_P1),T1(S_PARTY_P1),T19(S_POSTN_U2) ,T9(S_TIMEZONE_P1),T21(S_PARTY_P1),T17(S_PARTY_P1),T4(S_PARTY_P1), T7(S_CONTACT_U2),T16(S_ADDR_ORG_P1),T18(S_ORG_EXT_U3),T8(S_ORG_EXT _U3),T13(S_EMP_PER_U1),T20(S_USER_U2),T6(S_USER_U2),T22(S_ORG_GROU P_U2),T5(S_BU_U2),T10(S_CONTACT_SS_U1),T11(seq),T13(S_CONTACT_X_U1 ),T12(S_TIMEZONE_LANG_U1),T15(S_PER_PRTNRAPPL_U1)

NOTE:  Do not remap existing fields, especially those based on User Key columns, to other columns in the same table.


 Configuration Guidelines 
 Published: 18 April 2003