Prev: Install question
Next: Discover Location Intelligence
From: jafastinger on 8 Mar 2010 14:49
I have a Primary Key Index that has 11 columns in it. 80 million row
table.
The index is 100% clustered and has nlevels = 4.

We have an Update statement that should use this index but chooses not
to. Which causes the update to scan about 10000 rows before the row
to be updated is found.

If I lie about the statistics and change it to 3. I did this using
db2look and editing the file setting the index to nlevels 3, the
optimizer then chooses this index for update.

I would like to know if there is a way to reduce the number of nlevels
by changing something and not just updating the statistics in the
table.

Can you change the page size of the index and it will change the
number of nlevels?
Is there another way you can change it besides taking out columns of
the Primary Key?
From: Ian on 8 Mar 2010 15:10
On 3/8/10 12:49 PM, jafastinger wrote:
> I have a Primary Key Index that has 11 columns in it. 80 million row
> table.
> The index is 100% clustered and has nlevels = 4.
>
> We have an Update statement that should use this index but chooses not
> to. Which causes the update to scan about 10000 rows before the row
> to be updated is found.
>
> If I lie about the statistics and change it to 3. I did this using
> db2look and editing the file setting the index to nlevels 3, the
> optimizer then chooses this index for update.
>
> I would like to know if there is a way to reduce the number of nlevels
> by changing something and not just updating the statistics in the
> table.
>
> Can you change the page size of the index and it will change the
> number of nlevels?
> Is there another way you can change it besides taking out columns of
> the Primary Key?


Have you tried doing a reorg on the index? What kind of activity do you
do on the table -- are you frequently inserting rows that would lead to
a deeper index? If so, have you tried changing PCTFREE to help reduce
this?


From: jafastinger on 8 Mar 2010 15:21
On Mar 8, 3:10 pm, Ian <ianb...(a)mobileaudio.com> wrote:
> On 3/8/10 12:49 PM, jafastinger wrote:
>
>
>
>
>
> > I have a Primary Key Index that has 11 columns in it.  80 million row
> > table.
> > The index is 100% clustered and has nlevels = 4.
>
> > We have an Update statement that should use this index but chooses not
> > to.  Which causes the update to scan about 10000 rows before the row
> > to be updated is found.
>
> > If I lie about the statistics and change it to 3.  I did this using
> > db2look and editing the file setting the index to nlevels 3,  the
> > optimizer then chooses this index for update.
>
> > I would like to know if there is a way to reduce the number of nlevels
> > by changing something and not just updating the statistics in the
> > table.
>
> > Can you change the page size of the index and it will change the
> > number of nlevels?
> > Is there another way you can change it besides taking out columns of
> > the Primary Key?
>
> Have you tried doing a reorg on the index?  What kind of activity do you
> do on the table -- are you frequently inserting rows that would lead to
> a deeper index?  If so, have you tried changing PCTFREE to help reduce
> this?- Hide quoted text -
>
> - Show quoted text -

We are developing this table now. Just loaded it with data it is
reorganized.
I did not look at PCTFREE to see if it would reduce the nlevels.
I will do that and let you know if it works.
From: jafastinger on 8 Mar 2010 15:41
On Mar 8, 3:21 pm, jafastinger <jafastin...(a)gmail.com> wrote:
> On Mar 8, 3:10 pm, Ian <ianb...(a)mobileaudio.com> wrote:
>
>
>
>
>
> > On 3/8/10 12:49 PM, jafastinger wrote:
>
> > > I have a Primary Key Index that has 11 columns in it.  80 million row
> > > table.
> > > The index is 100% clustered and has nlevels = 4.
>
> > > We have an Update statement that should use this index but chooses not
> > > to.  Which causes the update to scan about 10000 rows before the row
> > > to be updated is found.
>
> > > If I lie about the statistics and change it to 3.  I did this using
> > > db2look and editing the file setting the index to nlevels 3,  the
> > > optimizer then chooses this index for update.
>
> > > I would like to know if there is a way to reduce the number of nlevels
> > > by changing something and not just updating the statistics in the
> > > table.
>
> > > Can you change the page size of the index and it will change the
> > > number of nlevels?
> > > Is there another way you can change it besides taking out columns of
> > > the Primary Key?
>
> > Have you tried doing a reorg on the index?  What kind of activity do you
> > do on the table -- are you frequently inserting rows that would lead to
> > a deeper index?  If so, have you tried changing PCTFREE to help reduce
> > this?- Hide quoted text -
>
> > - Show quoted text -
>
> We are developing this table now.  Just loaded it with data it is
> reorganized.
> I did not look at PCTFREE to see if it would reduce the nlevels.
> I will do that and let you know if it works.- Hide quoted text -
>
> - Show quoted text -


I found an APAR out there.
IY46865
for vs8. but I added DB2_REDUCED_OPTIMIZATION=YES.
This changed the path to use the Correct PK index.
I was checking production and this was turned on in production without
my knowledge. It is now turned on in all non production areas for
this database.

I will guess problem is solved for now.

Still wonder what I can do to reduce the levels. but that is for a
dreary day when I have time.

 | 
Pages: 1
Prev: Install question
Next: Discover Location Intelligence