Prev: Spatial Analysis: eSite chooses Yellowfin
Next: How to verify data move from big endian to little endian architecture?
From: Ember on 22 Mar 2010 12:01
I'm working on tuning a very expensive delete (cascades to hundreds of
tables). I do not have the ability to change the RI within the
database - that structure is vendor supplied(WebSphere Commerce).
Running the index advisor on this delete comes up with 800 recommended
indexes. I'm not willing to create all 800. So I'm trying to find
those that would have the most impact. This is db2 version 8(supported
through the commerce license), and I don't expect to upgrade in the
near future. Linux OS. I have to run thousands of deletes per day.

Yes, my runstats are up to date - I do those and reorgs in the proper
order weekly.

My main question would be whether it is worthwhile to create single-
column indexes on the Foreign key columns when those columns are
already members of composite indexes - usually the first column in the
composite indexes?

Thanks,
Ember
From: Lennart on 22 Mar 2010 16:48
On 22 mar, 17:01, Ember <ember.cro...(a)gmail.com> wrote:
> I'm working on tuning a very expensive delete (cascades to hundreds of
> tables). I do not have the ability to change the RI within the
> database - that structure is vendor supplied(WebSphere Commerce).
> Running the index advisor on this delete comes up with 800 recommended
> indexes. I'm not willing to create all 800. So I'm trying to find
> those that would have the most impact. This is db2 version 8(supported
> through the commerce license), and I don't expect to upgrade in the
> near future. Linux OS. I have to run thousands of deletes per day.
>
> Yes, my runstats are up to date - I do those and reorgs in the proper
> order weekly.
>
> My main question would be whether it is worthwhile to create single-
> column indexes on the Foreign key columns when those columns are
> already members of composite indexes - usually the first column in the
> composite indexes?

In general no, I would start with the others. In addition to db2advis,
you could explain the delete, and the use db2exfmt to examine the
plan. If the plan involves 800 tables it will be quite messy, but
perhaps you are able to identify a number of table scans and an
estimated cost for those. Another idea is to examine the schema for
foreign keys that are not supported by an index (syscat.keycoluse and
syscat.indexes).


/Lennart
From: Mark A on 23 Mar 2010 04:58
"Ember" <ember.crooks(a)gmail.com> wrote in message
news:28cfaddc-a0e2-4809-ba98-95780ee4d375(a)t9g2000prh.googlegroups.com...
> I'm working on tuning a very expensive delete (cascades to hundreds of
> tables). I do not have the ability to change the RI within the
> database - that structure is vendor supplied(WebSphere Commerce).
> Running the index advisor on this delete comes up with 800 recommended
> indexes. I'm not willing to create all 800. So I'm trying to find
> those that would have the most impact. This is db2 version 8(supported
> through the commerce license), and I don't expect to upgrade in the
> near future. Linux OS. I have to run thousands of deletes per day.
>
> Yes, my runstats are up to date - I do those and reorgs in the proper
> order weekly.
>
> My main question would be whether it is worthwhile to create single-
> column indexes on the Foreign key columns when those columns are
> already members of composite indexes - usually the first column in the
> composite indexes?
>
> Thanks,
> Ember

If there is already another composite index with the first column equal to
the FK, then don't create an index on the FK since it will be redundant as
far as DB2 performance is concerned.


From: Ember on 23 Mar 2010 10:28
On Mar 23, 2:58 am, "Mark A" <no...(a)nowhere.com> wrote:
> "Ember" <ember.cro...(a)gmail.com> wrote in message
>
> news:28cfaddc-a0e2-4809-ba98-95780ee4d375(a)t9g2000prh.googlegroups.com...
>
>
>
>
>
> > I'm working on tuning a very expensive delete (cascades to hundreds of
> > tables). I do not have the ability to change the RI within the
> > database - that structure is vendor supplied(WebSphere Commerce).
> > Running the index advisor on this delete comes up with 800 recommended
> > indexes. I'm not willing to create all 800. So I'm trying to find
> > those that would have the most impact. This is db2 version 8(supported
> > through the commerce license), and I don't expect to upgrade in the
> > near future. Linux OS. I have to run thousands of deletes per day.
>
> > Yes, my runstats are up to date - I do those and reorgs in the proper
> > order weekly.
>
> > My main question would be whether it is worthwhile to create single-
> > column indexes on the Foreign key columns when those columns are
> > already members of composite indexes - usually the first column in the
> > composite indexes?
>
> > Thanks,
> > Ember
>
> If there is already another composite index with the first column equal to
> the FK, then don't create an index on the FK since it will be redundant as
> far as DB2 performance is concerned.- Hide quoted text -
>
> - Show quoted text -

Thanks, that's exactly what I needed to know.
 | 
Pages: 1
Prev: Spatial Analysis: eSite chooses Yellowfin
Next: How to verify data move from big endian to little endian architecture?