LOJ and View Materialization

Mark Tierney

LOJ and View Materialization
Greetings All,

We have a handful of views that use a boatload of Left Outer Joins
(approaching the table limit). When queried they perform materialization.
In working through the regular things that cause materialization I do not
see the cause. Does anyone know if the table limit or heavy use of Left
Outer Joins cause materialization?

Thanks, Mark



Philip Sevetson

Re: LOJ and View Materialization
(in response to Mark Tierney)
"It Depends."

Broadly speaking, the more joins you perform, the more materializations
you will do, starting somewhere around four or five tables in my
experience. Some tuning of access paths is possible by creating indexes
to match the local predicates (the WHERE clause) for the first, and
sometimes the second, tables as the EXPLAIN sees them. Matching an index
to the Join predicates (the ON clause) on the second and third table
(again, as seen by the EXPLAIN) can also be profitable. Beyond that,
tuning a complex join is a matter of individual analysis of predicates and
business needs.

If incredibly bad response times are resulting, you might want to
consider temporary tables (the kind which can have indexes, I forget the
name at the moment) and extract/LOAD alternatives. This can help
peformance sometimes, at the expense of more complex/less maintainable
code.

Hope this helps.

--Phil Sevetson
[login to unmask email]
Database Administration
Wakefern Food Corporation





Mark Tierney <[login to unmask email]>
Sent by: DB2 Data Base Discussion List <[login to unmask email]>
06/03/2003 09:11 AM
Please respond to DB2 Data Base Discussion List


To: [login to unmask email]
cc:
Subject: LOJ and View Materialization


Greetings All,

We have a handful of views that use a boatload of Left Outer Joins
(approaching the table limit). When queried they perform materialization.
In working through the regular things that cause materialization I do not
see the cause. Does anyone know if the table limit or heavy use of Left
Outer Joins cause materialization?

Thanks, Mark



the reached at
[login to unmask email]



Patrick Bossman

Re: LOJ and View Materialization
(in response to Philip Sevetson)
Hello,
For inner joins materialization is less likely than outer joins.
If there are 15 tables in a query block and one of them is a view/table
expression which has 2 tables, then the view will materialize to avoid 16
tables in query block. This is the only situation when query has INNER
JOINS ONLY that I'm aware of where number of tables is the driving factor
for materialization (I'm not 100% certain of this, but of the many cases of
merge / materialization I've looked at I've only seen #tables be the
deciding factor when the merge would cause > 15).
Of course there can be other causes (aggregations, column expressions, etc.)
which drive materialization. I will confess that within optimization,
merge / materialization processing is not my stronge

The question - are you more likely to see materialization with outer joins
than inner joins?
The answer is YES, and I provide explicit examples below.
(Improvements were made in DB2 UDB for OS/390 V6 to reduce materializations
versus prior releases).

In some cases, the transformations allowed for inner joins are not
available to outer joins because the result would not be correct. This
results in more materializations with outer joins than inner joins.

Here are two examples where inner join merges, outer join materializes. This
is not an all-inclusive list.

CASE 1: table expression contains inner join.
When the outer select uses INNER JOIN, the table expression can be merged
because INNER JOIN allows A
INNER JOIN will MERGE table expression B.
EXPLAIN ALL SET QUERYNO = 10 FOR
SELECT *
FROM SYSIBM.SYSDATABASE A
INNER JOIN <===== INNER JOIN MERGES B
( SELECT B1.*
FROM SYSIBM.SYSDATABASE B1
, SYSIBM.SYSDATABASE B2
WHERE B1.NAME = B2.NAME
) B
ON A.NAME = B.NAME ;

OUTER JOIN causes materialization.
(If merge occured, the inner join would filter NULLS, possible incorrout)
EXPLAIN ALL SET QUERYNO = 11 FOR
SELECT *
FROM SYSIBM.SYSDATABASE A
LEFT OUTER JOIN <==== OUTER JOIN MATERIALIZE B
( SELECT B1.*
FROM SYSIBM.SYSDATABASE B1
, SYSIBM.SYSDATABASE B2
WHERE B1.NAME = B2.NAME
) B
ON A.NAME = B.NAME ;

CASE 2:
VALUE clause within table expression causes materialization when referenced
as part of outer join. The VALUE CLAUSE can be merged for INNER JOIN, but
for OUTER JOIN, the table expression has to materialize to ensure the VALUE
CLAUSE is evaluated BEFORE the outer join. If the view were merged, the
VALUE CLAUSE would apply to rows which are padded null from JOIN which would
be incorrect. Inner join does not have this issue.

EXPLAIN ALL SET QUERYNO = 20 FOR
SELECT *
FROM SYSIBM.SYSDATABASE A
INNER JOIN <=== INNER JOIN MERGES B
( SELECT VALUE ( NAME , ' ') AS NAME
FROM SYSIBM.SYSDATABASE ) B
ON A.NAME = B.NAME ;

EXPLAIN ALL SET QUERYNO = 21 FOR
SELECT *
FROM SYSIBM.SYSDATABASE A
LEFT OUTER JOIN <=== OUTER JOIN MATERIALIZES B
( SELECT VALUE ( NAME , ' ') AS NAME
FROM SYSIBM.SYSDATABASE ) B
ON A.NAME = B.NAME ;

Hope this is helpful.
Regards,
Pat Bossman



Michael Shields

view materialization
(in response to Patrick Bossman)
What causes a view to materialize? It makes sense that a where clause and/or a union and/or order by within a view would cause the view to materialize.
However, we're seeing a batch report take over 5 hours currently because it's joining to a view (without any of the above conditions) that joins a couple 10 million + record tables and that view is materializing.
When we go directly to the tables/columns we need, rather than getting the data from the view, we get the same results in seconds.

We want to use a view as it allows the support group of that data to manage it as they desire, but we're not going to hog our db2 environment for hours when it should only takes seconds to execute.

Any ideas as to how we can prevent it from materializing?

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Philip Sevetson

Re: view materialization
(in response to Michael Shields)
You might want to try "OPTIMIZE FOR 1 ROW" in the poorly-performing SELECT
statement -- DB2 assigns different weights to outcomes based on OPTIMIZE.
Other than that, one would have to examine the statement in question along
with the table/index structures and statistics to get a good picture of
why the optimizer is causing a materialization.

--Phil Sevetson
Database Administration
Wakefern Food Corporation CISD
mailto:[login to unmask email]






Shields Michael <[login to unmask email]>
Sent by: DB2 Data Base Discussion List <[login to unmask email]>
12/09/2003 03:45 PM
Please respond to DB2 Database Discussion list at IDUG


To: [login to unmask email]
cc:
Subject: view materialization


What causes a view to materialize? It makes sense that a where clause
and/or a union and/or order by within a view would cause the view to
materialize.
However, we're seeing a batch report take over 5 hours currently because
it's joining to a view (without any of the above conditions) that joins a
couple 10 million + record tables and that view is materializing.
When we go directly to the tables/columns we need, rather than getting the
data from the view, we get the same results in seconds.

We want to use a view as it allows the support group of that data to
manage it as they desire, but we're not going to hog our db2 environment
for hours when it should only takes seconds to execute.

Any ideas as to how we can prevent it from materializing?
---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of
the office, send the SET DB2-L NO MAIL command to
[login to unmask email] The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm


---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Michael Shields

Re: view materialization
(in response to Philip Sevetson)
I did "Optimize For 1 Row" as I remembered hearing that was a little trick for this type of issue.
It did drop the cost estimate in the explain down lower than either of the other 2 variations of the query.
I'm going to execute it on prod tomorrow and see how it runs out.
The explain with the "Optimize for 1 Row" still showed materialization of the view.


-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On Behalf Of [login to unmask email]
Sent: Tuesday, December 09, 2003 2:51 PM
To: [login to unmask email]
Subject: Re: view materialization



You might want to try "OPTIMIZE FOR 1 ROW" in the poorly-performing SELECT statement -- DB2 assigns different weights to outcomes based on OPTIMIZE. Other than that, one would have to examine the statement in question along with the table/index structures and statistics to get a good picture of why the optimizer is causing a materialization.

--Phil Sevetson
Database Administration
Wakefern Food Corporation CISD
mailto:[login to unmask email]





Shields Michael <[login to unmask email]>
Sent by: DB2 Data Base Discussion List <[login to unmask email]>


12/09/2003 03:45 PM
Please respond to DB2 Database Discussion list at IDUG



To: [login to unmask email]
cc:
Subject: view materialization



What causes a view to materialize? It makes sense that a where clause and/or a union and/or order by within a view would cause the view to materialize.
However, we're seeing a batch report take over 5 hours currently because it's joining to a view (without any of the above conditions) that joins a couple 10 million + record tables and that view is materializing.
When we go directly to the tables/columns we need, rather than getting the data from the view, we get the same results in seconds.

We want to use a view as it allows the support group of that data to manage it as they desire, but we're not going to hog our db2 environment for hours when it should only takes seconds to execute.

Any ideas as to how we can prevent it from materializing?
--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm


---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

goodrich

Re: view materialization
(in response to Michael Shields)
MessageReference v7 admin guide 5.10.7.2 for discussion of
view materialization
----- Original Message -----
From: Shields Michael
Newsgroups: bit.listserv.db2-l
To: [login to unmask email]
Sent: Tuesday, December 09, 2003 2:45 PM
Subject: view materialization


What causes a view to materialize? It makes sense that a where clause and/or a union and/or order by within a view would cause the view to materialize.
However, we're seeing a batch report take over 5 hours currently because it's joining to a view (without any of the above conditions) that joins a couple 10 million + record tables and that view is materializing.
When we go directly to the tables/columns we need, rather than getting the data from the view, we get the same results in seconds.

We want to use a view as it allows the support group of that data to manage it as they desire, but we're not going to hog our db2 environment for hours when it should only takes seconds to execute.

Any ideas as to how we can prevent it from materializing?
--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Dale Smock

Re: view materialization
(in response to goodrich)
As mentioned in another response, the admin guide lists the causes of
materialization (group by, column functions, distinct, join). When you join
to a view that is materialized, you are joining to the result table (in
DSNDB07) that is not indexed, resulting in a scan of the result table for
additional joins. The only way we have found to avoid this with large result
sets, is to create additional views which select directly to tables and
include all the needed joins within a view.

Dale Smock
Bertelsmann

-----Original Message-----
From: Shields Michael [mailto:[login to unmask email]
Sent: Tuesday, December 09, 2003 3:45 PM
To: [login to unmask email]
Subject: view materialization


What causes a view to materialize? It makes sense that a where clause
and/or a union and/or order by within a view would cause the view to
materialize.
However, we're seeing a batch report take over 5 hours currently because
it's joining to a view (without any of the above conditions) that joins a
couple 10 million + record tables and that view is materializing.
When we go directly to the tables/columns we need, rather than getting the
data from the view, we get the same results in seconds.

We want to use a view as it allows the support group of that data to manage
it as they desire, but we're not going to hog our db2 environment for hours
when it should only takes seconds to execute.

Any ideas as to how we can prevent it from materializing?
----------------------------------------------------------------------------
----- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page
select "Join or Leave the list". If you will be out of the office, send the
SET DB2-L NO MAIL command to [login to unmask email] The IDUG List
Admins can be reached at [login to unmask email] Find out the
latest on IDUG conferences at http://conferences.idug.org/index.cfm


---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Michael Shields

Re: view materialization
(in response to Dale Smock)
Thanks to everyone for responding!

I snagged the book from my DBA and read the recommended admin section on materialization of views.

I then ran a couple tests/explains using the view and found the following:

select from the view -- did not cause materialization
select joining to the view -- did not materialization
select outer joining to the view -- did cause materialization

I don't understand 'why' and I currently see this as an issue that should be corrected as it will drive people away from creating/using views.
Using the view with the outer join took 5+ hours.
Running it last night outer joining to the tables took 30 seconds.

If the view itself does unions or executes group by, order by, where clauses, then I would understand the execution process and why it would materialize.
Going from join to outer join, however, in the query that joins to the view doesn't seem reasonable.

Thanks again for all the feedback!
Mike

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On Behalf Of [login to unmask email]
Sent: Tuesday, December 09, 2003 4:34 PM
To: [login to unmask email]
Subject: Re: view materialization


As mentioned in another response, the admin guide lists the causes of materialization (group by, column functions, distinct, join). When you join to a view that is materialized, you are joining to the result table (in DSNDB07) that is not indexed, resulting in a scan of the result table for additional joins. The only way we have found to avoid this with large result sets, is to create additional views which select directly to tables and include all the needed joins within a view.

Dale Smock
Bertelsmann

-----Original Message-----
From: Shields Michael [mailto:[login to unmask email]
Sent: Tuesday, December 09, 2003 3:45 PM
To: [login to unmask email]
Subject: view materialization


What causes a view to materialize? It makes sense that a where clause and/or a union and/or order by within a view would cause the view to materialize.
However, we're seeing a batch report take over 5 hours currently because it's joining to a view (without any of the above conditions) that joins a couple 10 million + record tables and that view is materializing.
When we go directly to the tables/columns we need, rather than getting the data from the view, we get the same results in seconds.

We want to use a view as it allows the support group of that data to manage it as they desire, but we're not going to hog our db2 environment for hours when it should only takes seconds to execute.

Any ideas as to how we can prevent it from materializing?
--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm


---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Mark Doyle

Re: view materialization
(in response to Michael Shields)
MessageMichael,
That actually makes sense to me (and I would suspect that IBM will say it's working as designed). Here's the reasoning: for an inner join, if the data didn't exist in the non-view part of the join, there's no reason to look for it in the view -- hence, no view materialization. But for an outer join, there may not be a non-view corresponding piece, so the view must be materialized in order not to miss valid rows.

Hope that made sense,
Mark
----- Original Message -----
From: Shields Michael
Newsgroups: bit.listserv.db2-l
To: [login to unmask email]
Sent: Wednesday, December 10, 2003 11:01 AM
Subject: Re: view materialization


Thanks to everyone for responding!

I snagged the book from my DBA and read the recommended admin section on materialization of views.

I then ran a couple tests/explains using the view and found the following:

select from the view -- did not cause materialization
select joining to the view -- did not materialization
select outer joining to the view -- did cause materialization

I don't understand 'why' and I currently see this as an issue that should be corrected as it will drive people away from creating/using views.
Using the view with the outer join took 5+ hours.
Running it last night outer joining to the tables took 30 seconds.

If the view itself does unions or executes group by, order by, where clauses, then I would understand the execution process and why it would materialize.
Going from join to outer join, however, in the query that joins to the view doesn't seem reasonable.

Thanks again for all the feedback!
Mike
-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On Behalf Of [login to unmask email]
Sent: Tuesday, December 09, 2003 4:34 PM
To: [login to unmask email]
Subject: Re: view materialization


As mentioned in another response, the admin guide lists the causes of materialization (group by, column functions, distinct, join). When you join to a view that is materialized, you are joining to the result table (in DSNDB07) that is not indexed, resulting in a scan of the result table for additional joins. The only way we have found to avoid this with large result sets, is to create additional views which select directly to tables and include all the needed joins within a view.

Dale Smock
Bertelsmann
-----Original Message-----
From: Shields Michael [mailto:[login to unmask email]
Sent: Tuesday, December 09, 2003 3:45 PM
To: [login to unmask email]
Subject: view materialization


What causes a view to materialize? It makes sense that a where clause and/or a union and/or order by within a view would cause the view to materialize.
However, we're seeing a batch report take over 5 hours currently because it's joining to a view (without any of the above conditions) that joins a couple 10 million + record tables and that view is materializing.
When we go directly to the tables/columns we need, rather than getting the data from the view, we get the same results in seconds.

We want to use a view as it allows the support group of that data to manage it as they desire, but we're not going to hog our db2 environment for hours when it should only takes seconds to execute.

Any ideas as to how we can prevent it from materializing?
--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm
--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm
--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Michael Shields

Re: view materialization
(in response to Mark Doyle)
it's just the opposite, which is why I don't understand it. If we wanted everything from the view and then left joining it to the other data that would make sense to me.
However, we're left outer joining our data to the view data. (data left outer join view)

I really appreciate Mark and anyone else who is willing to try and explain why.

Thanks,
Mike

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On Behalf Of Mark A. Doyle
Sent: Wednesday, December 10, 2003 11:26 AM
To: [login to unmask email]
Subject: Re: view materialization


Michael,
That actually makes sense to me (and I would suspect that IBM will say it's working as designed). Here's the reasoning: for an inner join, if the data didn't exist in the non-view part of the join, there's no reason to look for it in the view -- hence, no view materialization. But for an outer join, there may not be a non-view corresponding piece, so the view must be materialized in order not to miss valid rows.

Hope that made sense,
Mark

----- Original Message -----
From: Shields Michael <mailto:[login to unmask email]>
Newsgroups: bit.listserv.db2-l
To: [login to unmask email]
Sent: Wednesday, December 10, 2003 11:01 AM
Subject: Re: view materialization

Thanks to everyone for responding!

I snagged the book from my DBA and read the recommended admin section on materialization of views.

I then ran a couple tests/explains using the view and found the following:

select from the view -- did not cause materialization
select joining to the view -- did not materialization
select outer joining to the view -- did cause materialization

I don't understand 'why' and I currently see this as an issue that should be corrected as it will drive people away from creating/using views.
Using the view with the outer join took 5+ hours.
Running it last night outer joining to the tables took 30 seconds.

If the view itself does unions or executes group by, order by, where clauses, then I would understand the execution process and why it would materialize.
Going from join to outer join, however, in the query that joins to the view doesn't seem reasonable.

Thanks again for all the feedback!
Mike

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On Behalf Of [login to unmask email]
Sent: Tuesday, December 09, 2003 4:34 PM
To: [login to unmask email]
Subject: Re: view materialization


As mentioned in another response, the admin guide lists the causes of materialization (group by, column functions, distinct, join). When you join to a view that is materialized, you are joining to the result table (in DSNDB07) that is not indexed, resulting in a scan of the result table for additional joins. The only way we have found to avoid this with large result sets, is to create additional views which select directly to tables and include all the needed joins within a view.

Dale Smock
Bertelsmann

-----Original Message-----
From: Shields Michael [mailto:[login to unmask email]
Sent: Tuesday, December 09, 2003 3:45 PM
To: [login to unmask email]
Subject: view materialization


What causes a view to materialize? It makes sense that a where clause and/or a union and/or order by within a view would cause the view to materialize.
However, we're seeing a batch report take over 5 hours currently because it's joining to a view (without any of the above conditions) that joins a couple 10 million + record tables and that view is materializing.
When we go directly to the tables/columns we need, rather than getting the data from the view, we get the same results in seconds.

We want to use a view as it allows the support group of that data to manage it as they desire, but we're not going to hog our db2 environment for hours when it should only takes seconds to execute.

Any ideas as to how we can prevent it from materializing?
--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm


---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

HEPP SHERY C

Re: view materialization
(in response to Michael Shields)
Michael- have you ran an explain on the LOJ query accessing the view? I'm wondering if you're doing merge scan join or nested loop. We have many queries here that use views and multiple inner and LOJ's And yes we have experienced some performance problems, but all of them have been resolved- either by applying PTF's from IBM or by changing the SQL. What version of Db2 are you on and how current on maintenance are you?

Regards, Shery Hepp

-----Original Message-----
From: Shields Michael [mailto:[login to unmask email]
Sent: Wednesday, December 10, 2003 10:35 AM
To: [login to unmask email]
Subject: Re: view materialization

it's just the opposite, which is why I don't understand it. If we wanted everything from the view and then left joining it to the other data that would make sense to me.
However, we're left outer joining our data to the view data. (data left outer join view)

I really appreciate Mark and anyone else who is willing to try and explain why.

Thanks,
Mike

--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Michael Shields

Re: view materialization
(in response to HEPP SHERY C)
db2 os390 v7.
type2 jdbc driver
Version 7.2 fixpack 9
-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On Behalf Of HEPP SHERY C
Sent: Wednesday, December 10, 2003 11:41 AM
To: [login to unmask email]
Subject: Re: view materialization


Michael- have you ran an explain on the LOJ query accessing the view? I'm wondering if you're doing merge scan join or nested loop. We have many queries here that use views and multiple inner and LOJ's And yes we have experienced some performance problems, but all of them have been resolved- either by applying PTF's from IBM or by changing the SQL. What version of Db2 are you on and how current on maintenance are you?

Regards, Shery Hepp

-----Original Message-----
From: Shields Michael [mailto:[login to unmask email]
Sent: Wednesday, December 10, 2003 10:35 AM
To: [login to unmask email]
Subject: Re: view materialization

it's just the opposite, which is why I don't understand it. If we wanted everything from the view and then left joining it to the other data that would make sense to me.
However, we're left outer joining our data to the view data. (data left outer join view)

I really appreciate Mark and anyone else who is willing to try and explain why.

Thanks,
Mike

--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm --------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Dave Nance

Re: view materialization
(in response to Michael Shields)
Michael,
Just wanted to let you know that your problem is not the rule when doing an outer join to a view. The optimizer takes a lot into consideration when making the access path determination and you happened to run into one of the few places where it does not make the correct assumptions about the SQL. You could try writing your view a little differently and may get the results you want. You could, also, send the two queries to the list and recieve suggestions on how the SQL could be rewritten to perhaps avoidd the materialization. Either way, good luck.

Dave Nance
First Health Services, Corp.
(804)527-6841


>>> [login to unmask email] 12/10/03 12:35:28 PM >>>

it's just the opposite, which is why I don't understand it. If we wanted everything from the view and then left joining it to the other data that would make sense to me.
However, we're left outer joining our data to the view data. (data left outer join view)

I really appreciate Mark and anyone else who is willing to try and explain why.

Thanks,
Mike

This message, including any attachments, is intended solely for the use
of the named recipient(s) and may contain confidential and/or
privileged information. Any unauthorized review, use, disclosure or
distribution of this communication(s) is expressly prohibited.
If you are not the intended recipient, please contact the sender by
reply e-mail and destroy any and all copies of the original message.
Thank you.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm