Optimized Views

May 31, 2013

Within your Clarify / Dovetail schema, there’s a concept of an Optimized View. This is really an implicit feature of a view, but if you’re aware of it, and understand how they work, you can use it to your advantage.

 

An optimized view happens when you have a join to a table, but the only field that you’re including from the joined in table is the objid field, and the relation type from the main table to the joined table is a OTOP or MTO.

 

When these conditions are met, then the schema editing tools (ddcomp, SchemaManager, Dovetail SchemaEditor) will optimize the underlying view, meaning that it will not actually create a join to the table, but instead will simply use the column from the main table as part of the select clause.

 

That’s a bunch of info to digest, so lets look at an example.

 

Simple Example

 

Lets say I want a view that inner joins the case table to the condition table. The view fields will include the case objid and id_number. We’ll also include the condition’s objid. Simple.

 

We would expect the schema editing tool to create the view so that it looks something like this:

CREATE VIEW table_my_view (case_objid, id_number, condition_objid)
AS SELECT table_case.objid ,  table_case.id_number , table_condition.objid
FROM table_case
INNER JOIN table_condition ON table_condition.objid = table_case.case_state2condition

 

But, what actually happens is that the view is created something like so:

 

CREATE VIEW table_my_view (case_objid, id_number, condition_objid) 
AS SELECT table_case.objid ,  table_case.id_number , table_case.case_state2condition
FROM table_case 
WHERE table_case.case_state2condition IS NOT NULL

 

Notice that there isn’t a join to the condition table, as we don’t actually need to – as we already have the condition’s objid data on the case table (because it’s a OTOP relation).

 

Therefore, this is an optimized view. When this happens, there’s a flag set on that view in the ADP table that indicates that it’s an optimized view.

 

Also, notice that because this is an INNER join, we want to make sure we actually have a related condition, so the schema editing tool adds in a WHERE clause of case_state2condition IS NOT NULL. This guarantees that there is a related condition, which is what we expect from an INNER join.

BOLT

 

In BOLT, we show the flags for a view, and that includes the VIEW_OPTIMIZED flag.  Here’s an example:

Inner Join

 

The user_case_load view (which is a baseline view) is OPTIMIZED because we’re doing a join to the user table, but since we’re only including the objid column from the user table, and the case_owner2user relation is a MTO, we can simply use the case_owner2user column to supply the user objid date, and not have to join in the user table.

 

Here’s what the view definition looks like:

CREATE VIEW table_user_case_load AS 
SELECT table_case.objid AS elm_objid , table_case.case_owner2user AS user_objid , table_condition.condition AS clarify_state , table_gse_status.title AS status , table_gse_cas_type.title AS case_type 
FROM table_condition 
INNER JOIN table_case ON table_condition.objid = table_case.case_state2condition 
INNER JOIN table_gbst_elm table_gse_cas_type ON table_gse_cas_type.objid = table_case.calltype2gbst_elm 
INNER JOIN table_gbst_elm table_gse_status ON table_gse_status.objid = table_case.casests2gbst_elm 
WHERE(table_case.case_owner2user IS NOT NULL)

 

Notice that the SELECT clause has: table_case.case_owner2user AS user_objid

 

Notice that the WHERE clause has: table_case.case_owner2user IS NOT NULL

What happens if it’s an OUTER join?

 

We can look at the wipelm_case view (which is a baseline view) and see what happens with an OUTER join.

 

In that view, we have an OUTER join from case to workaround. It’s outer because a case may or may not be linked to a workaround.

 

 

 

The underlying view definition looks like this:

 

CREATE VIEW table_wipelm_case (wip_objid, elm_objid, clarify_state, id_number, age, 
condition, status, title, priority, severity, 
owner, workaround, condition_code) 
AS SELECT table_case.case_wip2wipbin , table_case.objid , table_condition.condition , table_case.id_number , table_condition.wipbin_time , 
table_condition.title , table_gse_status.title , table_case.title , table_gse_priority.title , table_gse_severity.title , 
table_case.case_owner2user , table_case.case_soln2workaround , table_condition.condition
FROM table_case
INNER JOIN table_condition ON table_condition.objid = table_case.case_state2condition
INNER JOIN table_gbst_elm table_gse_priority ON table_gse_priority.objid = table_case.respprty2gbst_elm
INNER JOIN table_gbst_elm table_gse_severity ON table_gse_severity.objid = table_case.respsvrty2gbst_elm
INNER JOIN table_gbst_elm table_gse_status ON table_gse_status.objid = table_case.casests2gbst_elm
INNER JOIN table_site ON table_site.objid = table_case.case_reporter2site 
WHERE table_case.case_wip2wipbin IS NOT NULL 
AND table_case.case_owner2user IS NOT NULL

 

Notice a couple of things:

 

1. The workaround view column is getting its data from table_case.case_soln2workaround

 

2. There is NOT a where clause enforcing that case_soln2workaround is not null.

 

So the workaround column will either return an actual objid, or NULL, which is what we would expect from an OUTER join.

How can I take advantage of an optimized view?

 

Let me give a real-world example of where an optimized view was useful. I was recently working with a customer, and we were working on tuning the performance of a particular view that joined the case table to a bunch of other tables. To improve the query performance, we wanted to add an additional constraint so that we only returned open cases. By looking at the query explain plan, and some experimenting, we concluded that if added an additional constraint ofWHERE case_wip2wipbin > 0 then our query performance was good.

 

However, we didn’t want to actually join in the wipbin table, and then constrain on a field from that table, as that wouldn’t have the same effect. And, there were other queries that used that same view (and included closed cases, meaning that case_wip2wipbin would be null), and we didn’t want to affect those queries.

 

So, by understanding how optimized views worked, we were able to add an outer join to the wipbin table, but because we only included the wipbin’s objid, the schema editing tool optimized that view, and we are now able to add a constraint using that case_wip2wipbin column, which actually constrains the base table (table_case), so we get the query performance that we’re looking for.

Summary

 

In short, if you understand what an optimized view is, and how they work, you can use them to your advantage when needed.

 

Hope this helps.

 

Rock on.