By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,665 Members | 1,952 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,665 IT Pros & Developers. It's quick & easy.

view with where condition

P: n/a
hi, we are using db2 udb v8.1 on windows, i have a table contains over
one million records, it has seperate own tablespace than others, with
bufferpool size 250, i have created multiple views on this table , the
problem is some of the views have omit infomation like this one :
where ( not (OECD09='A')) and ( not ( OECD09='K')) and ( not (
OECD09='C')) and ( not ( OECD09='G')) and ( not ( OECD09='S')) and
( not ( OEQY02=+0)) and ( not ( OECD47='C')) and ( not (
OECD47='V')) and ( not ( OECD04='N')) and ( not ( OECD01='D')), so
even though the view with the where condition has zero records, it
takes me a 2,3 minutes to come up when i do sample data, so my
question is is there anyway come up the query faster? is the db2
determine the data based on the where condition on the run time?
thanks
Nov 12 '05 #1
Share this Question
Share on Google+
8 Replies


P: n/a
Ian
xixi wrote:
hi, we are using db2 udb v8.1 on windows, i have a table contains over
one million records, it has seperate own tablespace than others, with
bufferpool size 250, i have created multiple views on this table , the
problem is some of the views have omit infomation like this one :
where ( not (OECD09='A')) and ( not ( OECD09='K')) and ( not (
OECD09='C')) and ( not ( OECD09='G')) and ( not ( OECD09='S')) and
( not ( OEQY02=+0)) and ( not ( OECD47='C')) and ( not (
OECD47='V')) and ( not ( OECD04='N')) and ( not ( OECD01='D')), so
even though the view with the where condition has zero records, it
takes me a 2,3 minutes to come up when i do sample data, so my
question is is there anyway come up the query faster? is the db2
determine the data based on the where condition on the run time?
thanks

Views are not materialized, so each query to the view gets materialized
at runtime. You can pre-compute the results by building a materialized
query table (MQT).

A couple of other notes:

You can rewrite your predicate to simplify:

where OECD09 not in ('A','K','C','G','S')
and OECD47 not in ('C','V')
and OECD40 <> 'N'
and OECD01 <> 'D'
and OEQY02 <> 0

If this is is performing poorly, consider your index design, statistics,
etc.


-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----== Over 100,000 Newsgroups - 19 Different Servers! =-----
Nov 12 '05 #2

P: n/a
Ian
xixi wrote:
hi, we are using db2 udb v8.1 on windows, i have a table contains over
one million records, it has seperate own tablespace than others, with
bufferpool size 250, i have created multiple views on this table , the
problem is some of the views have omit infomation like this one :
where ( not (OECD09='A')) and ( not ( OECD09='K')) and ( not (
OECD09='C')) and ( not ( OECD09='G')) and ( not ( OECD09='S')) and
( not ( OEQY02=+0)) and ( not ( OECD47='C')) and ( not (
OECD47='V')) and ( not ( OECD04='N')) and ( not ( OECD01='D')), so
even though the view with the where condition has zero records, it
takes me a 2,3 minutes to come up when i do sample data, so my
question is is there anyway come up the query faster? is the db2
determine the data based on the where condition on the run time?
thanks

Views are not materialized, so each query to the view gets materialized
at runtime. You can pre-compute the results by building a materialized
query table (MQT).

A couple of other notes:

You can rewrite your predicate to simplify:

where OECD09 not in ('A','K','C','G','S')
and OECD47 not in ('C','V')
and OECD40 <> 'N'
and OECD01 <> 'D'
and OEQY02 <> 0

If this is is performing poorly, consider your index design, statistics,
etc.


-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----== Over 100,000 Newsgroups - 19 Different Servers! =-----
Nov 12 '05 #3

P: n/a
xixi wrote:
hi, we are using db2 udb v8.1 on windows, i have a table contains over
one million records, it has seperate own tablespace than others, with
bufferpool size 250, i have created multiple views on this table , the
problem is some of the views have omit infomation like this one :
where ( not (OECD09='A')) and ( not ( OECD09='K')) and ( not (
OECD09='C')) and ( not ( OECD09='G')) and ( not ( OECD09='S')) and
( not ( OEQY02=+0)) and ( not ( OECD47='C')) and ( not (
OECD47='V')) and ( not ( OECD04='N')) and ( not ( OECD01='D')), so
even though the view with the where condition has zero records, it
takes me a 2,3 minutes to come up when i do sample data, so my
question is is there anyway come up the query faster? is the db2
determine the data based on the where condition on the run time?
thanks


Do you have indexes on the OECDxx colums?

You could also try to rephrase the WHERE clause like this:

WHERE oecd09 NOT IN ( 'A', 'K', 'C', 'G', 'S' ) AND
oeqy <> 0 AND
oecd47 NOT IN ( 'C', 'V' ) AND
oecd04 <> 'N' AND
oecd01 <> 'D'

--
Knut Stolze
Information Integration
IBM Germany / University of Jena
Nov 12 '05 #4

P: n/a
xixi wrote:
hi, we are using db2 udb v8.1 on windows, i have a table contains over
one million records, it has seperate own tablespace than others, with
bufferpool size 250, i have created multiple views on this table , the
problem is some of the views have omit infomation like this one :
where ( not (OECD09='A')) and ( not ( OECD09='K')) and ( not (
OECD09='C')) and ( not ( OECD09='G')) and ( not ( OECD09='S')) and
( not ( OEQY02=+0)) and ( not ( OECD47='C')) and ( not (
OECD47='V')) and ( not ( OECD04='N')) and ( not ( OECD01='D')), so
even though the view with the where condition has zero records, it
takes me a 2,3 minutes to come up when i do sample data, so my
question is is there anyway come up the query faster? is the db2
determine the data based on the where condition on the run time?
thanks


Do you have indexes on the OECDxx colums?

You could also try to rephrase the WHERE clause like this:

WHERE oecd09 NOT IN ( 'A', 'K', 'C', 'G', 'S' ) AND
oeqy <> 0 AND
oecd47 NOT IN ( 'C', 'V' ) AND
oecd04 <> 'N' AND
oecd01 <> 'D'

--
Knut Stolze
Information Integration
IBM Germany / University of Jena
Nov 12 '05 #5

P: n/a
the where clause is automatically generated by program, i can't change
that to you described, unless manually, i have done indexes suggested
by db2 already, and do run statistics too.
Nov 12 '05 #6

P: n/a
the where clause is automatically generated by program, i can't change
that to you described, unless manually, i have done indexes suggested
by db2 already, and do run statistics too.
Nov 12 '05 #7

P: n/a
i have tried to change the where clause as you described, run stat,
and create the index as db2 design advisor recommend, but still not
much improvment on the query from the view, anything else could help?
thanks


Views are not materialized, so each query to the view gets materialized
at runtime. You can pre-compute the results by building a materialized
query table (MQT).

A couple of other notes:

You can rewrite your predicate to simplify:

where OECD09 not in ('A','K','C','G','S')
and OECD47 not in ('C','V')
and OECD40 <> 'N'
and OECD01 <> 'D'
and OEQY02 <> 0

If this is is performing poorly, consider your index design, statistics,
etc.


-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----== Over 100,000 Newsgroups - 19 Different Servers! =-----

Nov 12 '05 #8

P: n/a
"xixi" <da****@yahoo.com> wrote in message
news:c0**************************@posting.google.c om...
i have tried to change the where clause as you described, run stat,
and create the index as db2 design advisor recommend, but still not
much improvment on the query from the view, anything else could help?
thanks


Views are not materialized, so each query to the view gets materialized
at runtime. You can pre-compute the results by building a materialized
query table (MQT).

A couple of other notes:

You can rewrite your predicate to simplify:

where OECD09 not in ('A','K','C','G','S')
and OECD47 not in ('C','V')
and OECD40 <> 'N'
and OECD01 <> 'D'
and OEQY02 <> 0

If this is is performing poorly, consider your index design, statistics,
etc.

Please show the index DDL for all the indexes on the table(s) in the query.
Nov 12 '05 #9

This discussion thread is closed

Replies have been disabled for this discussion.