Unanswered: Complex Query with View (Joins in definition of View)
We have a complex query which is similar to what is given below (the Query and definition of the View). The Query takes around 2 second to execute. We have around 20 Million records in Table 01 and around 0.5 million records in Table 11 (Table01 has a join on Table11 in the View). We need to bring down this time. Can you please help answer the below Questions?
1. Would the Where conditions in the Query would be applied after the complete View is created?
2. Would there be any difference in the performance of the Query if rather than using the View, we have the Query with UNION and have the required conditions applied to the Table01 on left of the Union and to the Table02 on the right of the Union. Basically by avoiding usage of View
3. Any suggestions on writing the Query and defining the View in a better way.
Thanks in Advance!
Query Select ViewColumnA, sum (ViewColumnE), max(ColumnF) from ViewTable01Table02 where ViewColumnB = value from application , ViewColumnD = value from application
Definition of View - ViewTable01Table02 Select '1' As ViewColumnA, ' ' as ViewColumnB, Table01.ColumnC as ViewColumnC, Table01.ColumnD as ViewColumnD, ((Table01.ColumnE - Table01.ColumnH)* (CASE WHEN Table11.ColumnX = '1' THEN 1 WHEN Table11.ColumnX = '2' THEN -1 ELSE 0 END)) as ViewColumnE, CASE WHEN Table11.ColumnY = 1 THEN COALESCE(Tabl01.ColumnF, ' ') ELSE ' ' END AS ColumnF from (Select * from Table01 where ColumnG in ('1','2') ) Table01
JOIN on Table 11 where Table01.ColumnG = Table11.ColumnZ Union All Select '2' As ViewColumnA, Table02.ColumnB as ViewColumnB, ' ' as ViewColumnC, Table02.ColumnD as ViewColumnD, ((Table02.ColumnE - Table02.ColumnH)* (CASE WHEN Table12.ColumnX = '1' THEN 1 WHEN Table11.ColumnX = '2' THEN -1 ELSE 0 END)) as ViewColumnE, CASE WHEN Table12.ColumnY = 1 THEN COALESCE(Tabl02.ColumnF, ' ') ELSE ' ' END AS ColumnF from (Select * from Table02 where ColumnG in ('1','2') ) Table02
JOIN on Table 12 where Table02.ColumnG = Table12.ColumnZ
Complex Query with View (Joins in definition of View)
Thanks Dave for the quick reply and the suggestion! Had below points based on the response provided
1. Why should the index be only on Column D? Shouldn't the index be on both Column B and Column D as it is in the where clause of the Query? If yes, Column B wouldn't be in Table01 so is it okay to have only on Column B for Table 01?
2. The point about Null value to be as Default, I hope it wouldn't impact the calculations etc
3. Would putting a Null Value in ViewColumnB (rather than moving ' ') while creating View from Table01 would help. I ask because in the Query a check is made for the ViewColumnB to be a specific value which would never be SPACE.
4. Will get the syntax changed as suggested for the ColumnG check
5. Can you please mention where can I get more details on how the Queries on Views are processed?
1. do you provide any input values for column in table02??? Doesn't look like it, so no need for it in the index. You might want column as you are providing inputs, but do the values of 1 and 2 really filter much data? If not, then why bother.
2. how would nothing affect a calculation?
3. about the same
5. IBM Knowledge Center, search on explain. Partake in local User group meetings. Classes. etc...