|
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
| |
Share this Question
|
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
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! =----- | |
|
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
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! =----- | |
|
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 | |
|
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 | |
|
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.
| |
|
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.
| |
|
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! =-----
| |
|
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. | | This discussion thread is closed Replies have been disabled for this discussion. | |  Question stats - viewed: 3106
- replies: 8
- date asked: Nov 12 '05
|
