Hi,

We have table T1 and T2 with index on columns 'TPID' + 'DOC_ID'. T1 and T2 are identical. T1 contains active data - i.e app insert/update on this table. T2 is history table (i.e app mainly read from this table). We have data-transfer program that transfer non-active content from T1 to T2. We have also a view T_V that holds the join of T1 and T2. The no of distincts TPIDs in tables T1 & T2 are around 20. The number of rows in T_V is quite large.

We have an application - APP_A - in to which users associated with various TPIDs login. Association is one-to-one, e.g user_A will be asscoiated with TPID_A, user_B with TPID_B and so on. When user_A login APP_A selects only rows for TPID_A from T_V and similarly when USER_B login APP_A selects only rows for TPID_B from T_V. Currently it is done with SQLs that use T_V in APP_A explictly specifying required TPID in the WHERE class. e.g for USER_A & TPID_A, SELECT .. FROM T_V WHERE .... AND TPID='TPID_A' .
The question is:
Do we get any performance improvement by declaring different views for the various TPIDs - e.g T_TPID_A_V, T_TPID_B_V etc and changing APP_A sqls to use the appropriate view for the user. e.g SELECT .. FROM T_TPID_A_V WHERE ....

Thanks

Harikumar

Hi,

Did you try to use explain ?

Kara.

Try Kara's suggestion, but I think all you wil end up getting is a lot more views to manage. One thing you might want to think about though is only using that view when the app needs to look at active and/or inactive data, if, however, in some case you do not care about inactive data just go against the active data, since you say that table is much smaller.

Dave Nance

I am not 100% sure I understand the OP without seeting the table DDL and views, but my guess (of the question, not guessing about DB2) is that it will not help performance to have multiple views.

The reason why performance can typically not be better with multiple views is this: There must be a predicate to limit the rows for a specific user. Whether the predicate is in a single view (using the special register CURRENT USER) or is hard-coded in separate views doesn't make a difference. The view is compiled into the query and, thus, the predicate always evaluated.

Where you can see performance differences is if you have skew in the data for different users, i.e. one user has lots of data and another only a few rows. If you were to hard-code the user name in the predicate (instead of referring to the CURRENT USER special register), DB2 can do a much better selectivity estimation for each user and it may choose to use a different access plan based on that more precise estimate. With the special register, DB2 doesn't know the actual value for cached, prepared statements and, thus, cannot exploit the additional details.